Querying in Hibernate, Part 1

There are three ways to query objects in Hibernate:

1. Using Hibernate Query Language (HQL).

2. Using the Criteria API and doing Query By Criteria and Query by Example.

3. Direct SQL with or without automatic mapping of resultsets to objects.

Querying involves the following steps:

1. Create the query with needed filters and projections.

2. Bind runtime arguments to query parameters.

3. Execute the query and retrieve the results.

Now Hibernate is very rich and has many options so be patient and read through all the available options, it will really help you make the right design choice in future.

How to prepare a query?

There are two interfaces to control query execution in Hibernate: org.hibernate.Query and org.hibernate.Criteria. You obtain an instance of these objects using the Session object. You can use createQuery method to create a Query object that deals with HQL queries. You can use createSQLQuery method to create Query object that deals with native SQL queries. You call the createCriteria method to get a Criteria object. You pass the class of the object you want the query to return for this method. The following are the examples of each of the methods.

Query hqlQuery = session.createQuery(“from Employee”);

Query sqlQuery = session.createSQLQuery(“Select {employee.*} from Employee {employee}”).addEntity(“employee”,Employee.class);

Criteria crit = session.createCriteria(Employee.class);

Note that for the sqlQuery you define placeholders {employee} for the objects to be created and populated with the data that comes off the query.

How to paginate the results of a query?

You can use two methods, setFirstResult and setMaxResults to set the starting record number and the number of records you want the query to return. Hibernate automatically creates the query that will work of a particular SQL dialect. These methods are available for both Query and Criteria objects.

How to bind parameters?

You can use positional or named parameters in a HQL query. Here is an example of named parameter in HQL:

String queryString = “from Account account where account.name like :data”;

Query q = session.createQuery(queryString).setString(“data”,givenData);

Here is an example of positional parameter in HQL:

String queryString = “from Account account where account.name like ? and account.type = ?”;

Query q = session.createQuery(queryString);

q.setString(0,givenData);

q.setString(1,givenType);

How to execute a Query?

You call the list method of the Query or Criteria object to execute the query.

List result = myQuery.list();

List result = myCriteria.list();

There is also another method called iterate to execute the query. In calling the iterate method you are asking Hibernate to take advantage of the cache in the current persistent context or the cache in the second level cache. When you call the iterator method, only the primary key of a given table will be queried and Hibernate will retrieve the actual object from the cache. If the object is not found it the cache, Hibernate will issue another query per row. So it is an optimization mechanism, but use it with caution.

You can also create ScrollableResults by calling the scroll method. This is similar to how you use Scrollable resultses in JDBC. Here is an example of how you do that:

ScrollableResults accountCursor = session.createQuery(“from Account”).scroll();

accountCursor.first();

accountCursor.get();

accountCursor.scroll(3);

accountCursor.previous();

…

accountCursor.close();

How to put my queries in the Configuration file?

You first put the query in the hbm.xml file as follows:

<query name=”findAccountsByType”>

<![CDATA[

from Account account where accout.type = :type

]]>

</query>

Then you obtain a Query as follows:

Query query = session.getNamedQuery(“findAccountsbyType”).setString(“type”,givenType);

This will end my discussion for today. I will write more on Hibernate queries in the part 2 of this series.