1
2
3
4
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
5
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
7
8
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
10
11
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
17
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â&#x20AC;&#x2122;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.getVehiclesByFu elType"); 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
21
22
23
24
25
Itâ&#x20AC;&#x2122;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â&#x20AC;&#x2122;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
30
The setProjection() method on a Criteria accepts a list of several properties that are to be included in the result.
31
32
Note: SQLâ&#x20AC;&#x2122;s are not portable.
33
34
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â&#x20AC;&#x2122;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
37
38
39
40
41
42
43
44
45