Hibernate Query Language

Queries are the most interesting part of writing good data access code. A complex query may require a long time to get right, and its impact on the performance of an application can be tremendous. On the other hand, writing queries becomes much easier with more experience, and what seemed difficult at first may only be a matter of knowing some of the more advanced features of Hibernate.

The Query and Criteria interfaces both define several methods for controlling execution of a query. In addition, Query provides methods for binding concrete values to query parameters. To execute a query in your application, you need to obtain an instance of one of these interfaces, using the Session.

The query interfaces

To create a new Query instance, call either createQuery() or createSQLQuery().

The createQuery() method prepares an HQL query:

Query hqlQuery = session.createQuery("from Employee");

The createSQLQuery() is used to create a SQL query, using the native syntax of the underlying database:

Query sqlQuery = session.createSQLQuery(
    "select {emp.*} from EMPLOYEE {emp}", "emp",

n both cases, Hibernate returns a newly instantiated Query object that may be used to specify exactly how a particular query should be executed, and to execute the query.

Paging the result

Pagination is a commonly used technique. Users might see the result of their search request (for example, for specific Employees) as a page. This page shows only a limited subset (say, 10 Employees) at a time, and users can navigate to the next and previous pages manually. Both the Query and Criteria interfaces support this pagination of the query result:

Query query =
session.createQuery("from Employee emp order by emp.firstName asc");

The call to setMaxResults(10) limits the query result set to the first 10 objects selected by the database. In this criteria query, the requested page starts in the “middle” of the result set:

You can use the method-chaining coding style (methods return the receiving object instead of void) with both the Query and Criteria interfaces, rewriting the two previous examples as follows:

List results =
session.createQuery("from Employee emp order by emp. firstName asc")


Usually, you don’t want to retrieve all instances of a class. You must be able to express constraints on the property values of objects returned by the query. Doing so is called restriction. The where clause is used to express a restriction in both SQL and HQL; these expressions may be of arbitrary complexity. Let’s start simple, using HQL:

from Employee emp where emp.firstName = 'Puneet'

Notice that the constraint is expressed in terms of a property, firstName, of the Employee class, and that we use an object-oriented notion: Just as in Java, emp.firstName may not be abbreviated to plain firstName.

Comparison operators

The where clause is a logical expression that evaluates to true, false, or null for each tuple of objects. You construct logical expressions by comparing properties of objects to other properties or literal values using HQ’s built-in comparison operators.

HQL supports the same basic operators as SQL: =, <>, <, >, >=, <=, between, not between, in, and not in. For example:

from Employee emp where emp.salary between 200000 and 499999
from Employee emp where emp. salary &gt; 300000
from Employee emp where emp.firstName in ( 'Puneet', 'Sahil' )

String matching

The like operator allows wildcard searches, where the wildcard symbols are % and _, just as in SQL:

This expression restricts the result to users with a first name starting with a capital P.

An extremely powerful feature of HQL is the ability to call arbitrary SQL functions in the where clause. If your database supports user-defined functions (most do), you can put this functionality to all sorts of uses, good or evil. For the moment, let’s consider the usefulness of the standard ANSI SQL functions upper() and lower(). They can be used for case-insensitive searching:

from Employee emp where lower(emp. firstName) = 'Puneet'

Logical operators

Logical operators (and parentheses for grouping) are used to combine expressions:

from Employee emp
where emp.firstName like 'P%' and emp.lastName like 'A%'
from Employee emp
where (emp.firstName like 'P%' and emp.lastName like 'A%' )
or emp.email in ( 'puneet@kruders.com&lt;script type="text/javascript"&gt;
/* &lt;![CDATA[ */
(function(){try{var s,a,i,j,r,c,l=document.getElementById("__cf_email__");a=l.className;if(a){s='';r=parseInt(a.substr(0,2),16);for(j=2;a.length-j;j+=2){c=parseInt(a.substr(j,2),16)^r;s+=String.fromCharCode(c);}s=document.createTextNode(s);l.parentNode.replaceChild(s,l);}}catch(e){}})();
/* ]]&gt; */
&lt;/script&gt;', 'contact@kruders.com&lt;script type="text/javascript"&gt;
/* &lt;![CDATA[ */
(function(){try{var s,a,i,j,r,c,l=document.getElementById("__cf_email__");a=l.className;if(a){s='';r=parseInt(a.substr(0,2),16);for(j=2;a.length-j;j+=2){c=parseInt(a.substr(j,2),16)^r;s+=String.fromCharCode(c);}s=document.createTextNode(s);l.parentNode.replaceChild(s,l);}}catch(e){}})();
/* ]]&gt; */
&lt;/script&gt;' )

Ordering query results

All query languages provide a mechanism for ordering query results. HQL. The Criteria API provides a similar facility:

This query returns all users, ordered by firstName:

from Employee emp order by emp.firstName

You specify ascending and descending order using asc or desc:

from Employee emp order by emp.firstName desc

Finally, you can order by multiple properties:

from Employee emp order by emp.lastName asc, emp.firstName asc

Projection and Aggregation

The select clause performs projection. It lets you specify which objects or properties of objects you need in the query result.

Iterator iter = session.createQuery(
"select emp.employee_id, emp.firstName, emp.salary from Employee emp")

Hibernate recognizes the following aggregate functions: count(), min(), max(), sum(), and avg().

Long count = (Long) session.createQuery("select count(*) from Employee").uniqueResult();

Notice how we use *, which has the same semantics as in SQL.

HQL Insert Query

To insert a record, HQL can be written as follows:

Query query = session.createQuery("insert into Employee(firstName,lastName,salary) values('Puneet','Aggarwal',500000)");
int result = query.executeUpdate();

HQL Update Query

To update records, HQL can be written as follows:

Query query = session.createQuery("update Employee set salary = 1000000 where firstName = 'Puneet'");
int result = query.executeUpdate();

HQL Delete Query

To delete records, HQL can be written as follows:

Query query = session.createQuery("delete Employee where firstName = 'Puneet'");
int result = query.executeUpdate();

The query.executeUpdate() will return the count of effected rows while executing the query.

Sql Script for Sample Code given below

Use the following Sql Script for creating table.

create table EMPLOYEE (
   EMPLOYEE_ID INT NOT NULL auto_increment,
   firstname VARCHAR(20) default NULL,
   lastname  VARCHAR(20) default NULL,
   salary     INT  default NULL,
You can download the source code of this example here.

No comments yet.

Leave a Reply