Hibernate HQL, Native SQL, and Criteria Search

Being a query language, you will always find it similar to SQL, but there is a fundamental difference between the SQL and HQL, Hibernate Query Language. SQL is or has to be database entity centric. As hibernate is acting as a middleware for objects and database, HQL is mix of both SQL and object oriented concept. Also I feel, it is object centric than database. How any SQL can change to become HQL can be observed in example below.


Considering above rules, If I want to write a simple hql to select customers from database then this is how the query will look like.

 from Customer

If we add where condition and a join then –

 from Customer
   inner join customer.address

You can go on applying the rules here and generate more complex hibernate query.

Hibernate Criteria API:
If you don’t want to write these queries on your own, and want hibernate to generate the query for you then, hibernate criteria API or hibernate search framework is available. This also operates on objects and not on tables directly. My experience is, if the objects being retrieved are having many children at many levels then see if you can go for lazy loading. Otherwise rethink before using this API as it may hit performance badly. You can still use hibernate with native sql (discussed below).
I have seen some people writing Java wrapper to hide the calls to the criteria api, this also can become an endless job and you will end up covering each and every minute behavior of the API.
Let us take a simple example and convert it into criteria query.

 SELECT * FROM tab_customer WHERE customer_id =1

And the criteria query will be

Criteria criteria = session.createCriteria(Customer.class)
  .add(Restrictions.eq("id", new Integer(0));

Retrieve data like –

 List customers = criteria.list();

This API also provides features required for pagination, like setting starting row id, max record. You can set fetching mode, child object selection etc. using API. This API can be complete replacement of your SQL or HQL query writing.

Native SQL:
This is nothing but hibernate’s support to the SQL language. If you have huge number of already written queries, and don’t want to take pain of rewriting those in HQL or SQL, but want to use other features of hibernate, then use those sqls and generate objects on own or get hibernate’s help for that.
Example to select customer with id = 1 will change like this.

session.createSQLQuery("SELECT * FROM tab_customer WHERE customer_id =1")
  .addEntity("customer", Customer.class);

Externalizing Native SQL:
Above query can be externalized i.e. moved out of Java code into an hbm file as a named query and used. This concept is not restricted to native sql but hqls can also be externalized.

SELECT customer.CUSTOMER_NAME as {customer.customerName}
FROM Customer customer
WHERE customer.CUSTOMER_ID =:customerId

And the Java call will be –

List customers = sess.getNamedQuery("customers")
.setString("customerId ", customerId)

Calling Externalized Stored Procedure using Hibernate:
Hibernate also supports stored proc calling. It is similar to the queries with few minor changes.
If I write a stored procedure to insert data in customer table as insert_customer(?,?) then the declaration in hbm file will look like this.

  { call insert_customer(?, ?)}


Leave a Reply

Your email address will not be published.