hibernate_query_operations

Note: a) HQL keywords are case-insensitive(prefer lowercase).

Example : SELECT is same as Select or SeLeCt

b) Class names and properties are case-sensitive

Example: RegistrationNumber is not the same as registrationNumber

The from clause returns instances of a class

The select clause picks which objects and properties to return in the query result set.

Results:

1) HQL: from Vehicle

1,KA-04-Z-1234,1200.99,Diesel,14.0,Toyota,1

2,AP-08-XY-9822,800.88,Diesel,21.0,Tata,1

3,JK-02-AA-120,450.0,Petrol,16.0,Maruthi,1

4,TN-09-EF-3411,3200.0,Diesel,5.0,Ashok Leyland,3

5,KA-08-AE-672,4000.0,Diesel,6.0,Tata,2

6,KA-02-GH-788,900.0,Petrol,15.0,Hyundai,1

2) HQL:from Vehicle v where v.fuelType = ‘Petrol’

3,JK-02-AA-120,450.0,Petrol,16.0,Maruthi,1

6,KA-02-GH-788,900.0,Petrol,15.0,Hyundai,1

3) select registrationNumber, mileagePerLiter from Vehicle

KA-04-Z-1234,14.0

AP-08-XY-9822,21.0

JK-02-AA-120,16.0

TN-09-EF-3411,5.05

Note: The result of the query will be loaded completely into a collection in memory.

Entity instances retrieved by a query are in a persistent state.

The uniqueResult() method offers a shortcut if you know your query will only return a single object.

Object query.uniqueResult();

6

The advantages of named parameters are as follows:

•named parameters are insensitive to the order they occur I the query string

•they can occur multiple times in the same query

•they are self-documenting

Note: The setEntity() method of Query interface lets you bind a persistent entity as listed below:

String HQL = "from Vehicle v where v.category = :cat";

Query query = session.createQuery(HQL);

Category category = new Category();

category.setCategoryId(1);

category.setCategoryName("Car");

query.setEntity("cat", category);

9

Pagination usage: If user needs to view a subset of data that is not easily displayed on one page. Provide pagination control to browse from page to page. Let the user browse to the previous and next pages by providing links to such actions. Also, provide links to the absolute start and end of the dataset (first and last).

Note: There is no standard way to express pagination in SQL. Hibernate knows the tricks to make this work efficiently on your particular database.

12

Note: The HQL is same as the following SQL.

select

vehicle0_.REG_NO as col_0_0_,

vehicle0_.MILEAGE as col_1_0_,

vehicle0_.FUEL_TYPE as col_2_0_,

category1_.CATEGORY_NAME as col_3_0_

from

VEHICLE vehicle0_

inner join

CATEGORY category1_

on vehicle0_.CATEGORY_ID=category1_.CATEGORY_ID

13

Note: Hibernate join queries return tuples of objects.

14

An inner join is the most common join operation used in applications and can be regarded as the default join-type.

Note: cat.vehicles ---> vehicles is a collection property of type Set<Vehicle> declared in Category class.

Set<Vehicle> vehicles;

Complete Code listing:

String hql = "from Category cat left outer join cat.vehicles as vehicle";

Query query = session.createQuery(hql);

List<Object[]> objects = query.list();

for(int i = 0; i < objects.size(); i++) {

Object[] entities = objects.get(i);

if( entities[0] != null) {

Category category = (Category) entities[0];

System.out.print(category.getCategoryId()

+"," + category.getCategoryName());

}

if( entities[1] != null ) {

Vehicle vehicle = (Vehicle) entities[1];

System.out.print(vehicle.getRegistrationNumber()

+"," + vehicle.getManufacturer() );

}15

SQL generated:

select

category0_.CATEGORY_ID as CATEGORY1_0_0_,

vehicles1_.VEHICLE_ID as VEHICLE1_1_1_,

category0_.CATEGORY_NAME as CATEGORY2_0_0_,

vehicles1_.REG_NO as REG2_1_1_,

vehicles1_.DAILY_RENTAL as DAILY3_1_1_,

vehicles1_.FUEL_TYPE as FUEL4_1_1_,

vehicles1_.MILEAGE as MILEAGE1_1_,

vehicles1_.MANUFACTURER as MANUFACT6_1_1_,

vehicles1_.DESCRIPTION as DESCRIPT7_1_1_,

vehicles1_.CATEGORY_ID as CATEGORY8_1_1_

from

CATEGORY category0_

left outer join

VEHICLE vehicles1_

on category0_.CATEGORY_ID=vehicles1_.CATEGORY_ID

16

SQL:

select

vehicle0_.VEHICLE_ID as VEHICLE1_1_,

vehicle0_.REG_NO as REG2_1_,

vehicle0_.DAILY_RENTAL as DAILY3_1_,

vehicle0_.FUEL_TYPE as FUEL4_1_,

vehicle0_.MILEAGE as MILEAGE1_,

vehicle0_.MANUFACTURER as MANUFACT6_1_,

vehicle0_.DESCRIPTION as DESCRIPT7_1_,

vehicle0_.CATEGORY_ID as CATEGORY8_1_

from

VEHICLE vehicle0_

where

vehicle0_.MILEAGE>=(

select

avg(vehicle1_.MILEAGE)

from

VEHICLE vehicle1_

)

18

Wrap the query text into a CDATA instruction so the XML parser doesn’t get confused by any characters in your query string that may accidentally be considered XML (such as the < or > than operator).

Note:

1) if <query> element is inside a <class> element : Prefix it with name of entity class:

session.getNamedQuery("com.mindtree.entity.Vehicle.getVehiclesByFuelType");

2) if <query> element is inside a <class> element, name of the query is globally unique:

session.getNamedQuery("getVehiclesByFuelType");

19

Refer the same query illustrations in Native SQL section.

20

It’s easy to extend the Criteria API and add new methods or new Criterion classes.

Note: 1) Queries expressed as criteria are often much less readable than queries expressed in HQL.

2) Criteria API is native to Hibernate; it isn’t part of the Java Persistence API.

26

If you need Vehicles of Category type = 1 or any vehicle of type ‘petrol’

Criteria criteria = session.createCriteria(Vehicle.class);

Criterion c1 = Restrictions.eq("category.categoryId", 1);

Criterion c2 = Restrictions.ilike("fuelType", "Petrol");

Criterion c3 = Restrictions.or(c1,c2);

criteria.add(c3);

27

Restrictions class contain static factory methods for creating Criterion instances.

Queries comprise of Criterion instances, which say what objects to select.

Queries generated using Criteria be ordered using Order instances.

If you add multiple Criterion instances to the one Criteria instance, they’re applied conjunctively (using and).

You can use Restrictions.disjunction() and Restrictions.conjunction() to add “and” and/or “or” to where clause.

28

SQL equivalent:

select * from VEHICLE where

( (

CATEGORY_ID=?

and lower(REG_NO) like ?

)

or MILEAGE>=?

)

29

The setProjection() method on a Criteria accepts a list of several properties that are to be included in the result.

31

Note: SQL’s are not portable.

33

Hibernate reads the resultset of the SQL query and tries to discover the column names and types as defined in your mapping metadata.

If the column REG_NO is returned, and it’s mapped to the name property of the Vehicle class, Hibernate knows how to populate that property and finally returns fully loaded entity objects.

Note: For addEntity() method entity class and mapping information is required.

35

To achieve the same using HQL or Criteria API will be a complicated task.

TOTAL_RENT_AMOUNT calculated by taking the data difference between booking end date and booking start date multiplied by the daily rental

36

hibernate_query_operations

Documents