Hibernate Query Language (HQL) - Java Training School

Hibernate Query Language (HQL) is an object-oriented query language. It is quite similar to SQL but instead of working on tables and columns, HQL works on persistent objects and their attributes. Hibernate translates these HQL queries into conventional SQL queries which works on database objects.

The biggest advantage of HQL is that it is database independent. Meaning even if you change the existing database, you would not need to change HQL queries. Translation to native queries will still be taken care by hibernate framework.

Although, SQL statements can be used directly with Hibernate using Native SQL, but it is recommend to use HQL whenever possible to avoid database portability issues. This will also help in taking advantage of Hibernate SQL generation and caching strategies.

Keywords like SELECT, FROM, and WHERE etc. are not case sensitive, but properties like table and column names are case sensitive in HQL.

FROM Clause

If you want to load complete persistent object into memory, then ‘From’ clause can be used.

String hql = "FROM Student";
Query query = session.createQuery(hql);
List results = query.list();

AS Clause

As clause can be used to assign alliases to the entity classes in HQL. As clause is optional. Meaning alias can directly be assigned also.

String hql = "FROM Student AS S";
Query query = session.createQuery(hql);
List results = query.list();


Or 

String hql = "FROM Student S";
Query query = session.createQuery(hql);
List results = query.list();

SELECT Clause

The SELECT clause provides more control over the result set. It is used when we want to select few properties and not all of them. Refer the below example

String hql = "SELECT S.firstName FROM Student S";
Query query = session.createQuery(hql);
List results = query.list();

Note that S.firstName is a property of Student object rather than a field of the Student table.

WHERE Clause

If you want to narrow down the results based on some condition, then use WHERE clause. Following is the syntax

String hql = "FROM Student S WHERE S.id = 101";
Query query = session.createQuery(hql);
List results = query.list();

String hql = "FROM Student S WHERE S.id = 101";
Query query = session.createQuery(hql);
List results = query.list();

ORDER BY Clause

ORDER BY is used to sort the results of the query. You can order the results by any property on the objects in the result set either ascending (ASC) or descending (DESC) fashion.

String hql = "FROM Student S WHERE S.id > 101 ORDER BY S.age DESC";
Query query = session.createQuery(hql);
List results = query.list();

In order to sort by more than one property, you would just add the additional properties to the end of the order by clause, separated by commas as follows −

String hql = "FROM Student S WHERE S.id > 101 " +
             "ORDER BY S.firstName DESC, S.age DESC";
Query query = session.createQuery(hql);
List results = query.list();

GROUP BY Clause

This clause allows Hibernate to fetch information from the database and group it based on a value of an attribute.

String hql = "SELECT SUM(S.marks), S.firtName FROM Student S " +
             "GROUP BY S.firstName";
Query query = session.createQuery(hql);
List results = query.list();

UPDATE Clause

Bulk updates are new to HQL with Hibernate version 3. Delete also works differently in Hibernate version 3. The Query interface now contains a method called executeUpdate() for executing HQL UPDATE or DELETE statements.

The UPDATE clause can be used to update one or more properties of an one or more objects. Following is an example

String hql = "UPDATE Student set marks = :marks "  + 
             "WHERE id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("marks", 388);
query.setParameter("student_id", 101);
int result = query.executeUpdate();

Using Named Parameters

Hibernate supports named parameters in its HQL queries. This makes writing HQL queries that accept input from the user.

String hql = "FROM Student S WHERE S.id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("student_id",101);
List results = query.list();

DELETE Clause

The DELETE clause can be used to delete one or more objects.

String hql = "DELETE FROM Student "  + 
             "WHERE id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("student_id", 10);
int result = query.executeUpdate();
System.out.println("Rows deleted: " + result);

INSERT Clause

HQL supports INSERT INTO clause only where records can be inserted from one object to another object.

String hql = "INSERT INTO Student(firstName, lastName, marks)"  + 
             "SELECT firstName, lastName, marks FROM student_backup";
Query query = session.createQuery(hql);
int result = query.executeUpdate();
System.out.println("Rows inserted: " + result);

Aggregate Methods

Various aggregate methods are supported in HQL. Following are the examples:

avg(property name), count(property name or *), max(property name), min(property name), sum(property name)

String hql = "SELECT count(distinct S.firstName) FROM Student S";
Query query = session.createQuery(hql);
List results = query.list();

Pagination using Query

There are two methods of the Query interface for pagination.

Method
Query setFirstResult(int startPosition)This method takes an integer that represents the first row in your result set, starting with row 0.
Query setMaxResults(int maxResult)This method tells Hibernate to retrieve a fixed number maxResults of objects.

In the following example, we can fetch 10 rows at a time

String hql = "FROM Student";
Query query = session.createQuery(hql);
query.setFirstResult(1);
query.setMaxResults(10);
List results = query.list();