Hibernate Query Language - HQL

Session api's are used when an entity needs to be fetched from the database. To fetch list of records which might involve joins we have to use Hibernate Query Language (HQL). HQL queries are similar to SQL. They are written in OO way. Hibernate supports criteria queries also which are type safe way of writing queries. Hibernate supports the JPA QL also. Hibernate also supports issuing native SQL queries. The Query interface is used to build and execute queries.

Fetching List

---

To fetch a list
In HQL:

Query hqlQuery = session.createQuery("Select s from Student s");
List<Student> listStudent = hqlQuery.list();

//Iterating the list 
for(Student s: listStudent){
      System.out.println(s.getName());
   }

Using Criteria:

Criteria crit = session.createCriteria(PhoneEntity.class);
List<PhoneEntity> phones = crit.list();

for(PhoneEntity p: phones){
   System.out.println(p.getNumber());
}

To restrict the list based on parameter
HQL way:

hqlQuery = session.createQuery ("from Student s where s.name like :name");

//The wild card can use used as similar to SQL
hqlQuery.setString("name", "ab%");

List<Student> listStudent = hqlQuery.list();

Using Criteria:

Criteria crit = session.createCriteria(Student.class);
crit.add(Restrictions.ilike(“name", “ab”);
List<Student> students = crit.list();

for(Student p: students ){
	System.out.println(p.getName());
}

Pagination

---

Pagination is used when there are huge number of records and we want to fetch a subset of it. For example we want to display the list of students on front end so we might just want to fetch the 25 records which we want to display in third page. So we want the record which starts at 51th row and ends at 75th row

HQL way:

Query hqlQuery =
session.createQuery("from Student s order by s.name asc");

hqlQuery.setFirstResult(51);
hqlQuery.setMaxResults(25);

List<Student> listStudent = hqlQuery.list();

Criteria way:

Criteria crit = session.createCriteria(PhoneEntity.class);
crit.addOrder(Order.asc("number"));

crit.setFirstResult(51);
crit.setMaxResults(25);

List<PhoneEntity> phones = crit.list();

Binding Parameters

---

It's always a good practice to bind the parameters rather than building the queries by String manipulation. It will protect from SQL injection also. For example do not do like
Select s from Student s where s.name like "James%".
Rather than use parameter binding. The parameters can be bound either using named parameter or positional parameter. Names parameter is better as it is more maintainable and readable.

Named parameter binding:

hqlQuery = session.createQuery("from Student s where s.name like :name");

//The parameter is given a name
hqlQuery.setString("name", "James%");

List<Student> listStudent = hqlQuery.list();

Positional parameter binding:

hqlQuery = session.createQuery("from Student s where s.name like ?");

//The parameter is referred by position. For more than 1 parameter use number 0,1,2  
//as thesequence used in query
hqlQuery.setString0, "James%");

listStudent = hqlQuery.list();

Scrolling with database cursor

---

Hibernate provides support for JDBC feature called scrollable result where the cursor is held on database. To open the curosr

ScrollableResults itemCursor = session.createQuery(“from Student s).scroll();

The different functions to iterate using cursor

//Go to first record
itemCursor.first();

//Go to last record
itemCursor.last();

//Go to next record
itemCursor.next();

//Go to previous record
itemCursor.previous();

Named Query

---

Named query are queries where we give a name to it. These queries can bring performance as they can be cached.

@NamedQuery{name=“findAllStudents”,
	   query=“SELECT s from Student s where
	   s.firstName LIKE :firstName”)

Calling the query:

Query query = em.createNamedQuery(“findAllStudents”);

Polymorphic Queries

---

Hibernate supports polymorphic query. For example suppose we have an inheritance structure on the OO side as mentioned in Hibernate Mapping Inheritance.It does not matter whichever strategy you use, Hibernate will ensure to fetch the record as per polymorphism.

If the query is "Select u from User u", hibernate will fetch all the records form User, Customer and Employee table.

Expressions

---

Hibernate supports the expression which can narrow down results as per some criteria.
Some example of expression are

//amount between 1 and 10
from Student s where s.pendingAmount between 1 and 10

//amount > 100
from Student s where s.pendingAmount > 100

//Only those students whose email is as mentioned
from Student s where s.email in (‘a@a.com’,’b@b.com’)

//students whose email are not set
where s.email is null

//students whose mails are set
where s.email is not null

//student refers to a phone collection and the collection is not empty
from Student i where i.phones is not empty

Hibernate supports function also

//lowering the name and than comparing
from Student s where lower(s.name) like…
//Concatenating the name and than comparing as the database is having only one name field
//which contains both first and last name together
from Student s where concat(s.firstName,s.lastName) like..

//Student has a collection of phone and has more than 2 phones.
From Student s where size(s.phones) > 2

//current_date will calculate the date and return
Select upper(s.name), current_date()  from Student s

Fetching Multiple Objects

---

Multiple objects can be fetched as an object array.

//It will return an Object   with Student at index 0 and User at index 1
Select s , u from Student s, User u

Scalar queries or Projection

---

This is very useful in reporting. Fetching a lot of objects when we are looking for only some column data or want data from different tables leads to performance bottlenecks. In these cases it's better to use scalar queries as it fetched only required data

Select s.name , s.pendingAmount from Student s

This query will return an Object[].It’s a scalar queries and the data fetched is not associated with persistence context. The fetched data is not managed for dirty states.

Query Hints

---

The flush mode can be disabled while executing the query. This can be useful when you do not want Hibernate to issue a flush before executing the query. For example if before executing the query you know that the results of the query is not going to be affected by the dirty states of entities in the session, for performance reason it's better to disable the flush.

//Query way
Query q = session.createQuery(queryString).setFlushMode(FlushMode.COMMIT);

//Criteria way
Criteria c = session.createCriteria(Student.class).setFlushMode(FlushMode.COMMIT);

//JPA way
Query q = em.createQuery(queryString).setFlushMode(FlushModeType.COMMIT);

The cache mode can also be disabled while executing query.It tells hibernate to not to put any entities in the second level cache

//Query way
Query q = session.createQuery(queryString).setCacheMode(CacheMode.COMMIT);

//Critera way
Criteria c = session.createCriteria(Student.class).setCacheMode(CacheMode.COMMIT);

//In JPA this is not supported as standard.
If hibernate is the JPA provider than it can be used
//using hibernate feature.
~~#FF0000">//Query way


Query q = session.createQuery(queryString).setReadOnly(true);

//Criteria way
Criteria c = session.createCriteria(Student.class).setReadOnly(true);

//In JPA this is not supported as standard.
//If hibernate is the JPA provider than it can be used using hibernate feature.
Query q = em.createQuery(queryString).setHint(“org.hibernate.readOnly”,	true);

A timeout can also be passed as query hint which tells how long a long running query can be allowed

//Query way
Query q = session.createQuery(queryString).setTimeout(60);

//Criteria way
Criteria c = session.createCriteria(Student.class).setTimeout(60);

//In JPA this is not supported as standard.
//If hibernate is the JPA provider than it can be used using hibernate feature.
Query q = em.createQuery(queryString).setHint(“org.hibernate.timeout”,60);

---

Back to Hibernate Index
Back To Java Home
Back To Home

---