10 - Hibernate Query Language (HQL)

10.1 Overview

As Hibernate is all about persisting java objects, Hibernate Query Language is a similar to SQL but in terms of Object. We may want queries with where clauses, conditions , aggregate functions etc in our application and HQL does support all these features. One of the important different between SQL and HQL is , class names are used in place of tables and object properties names are used in place of table column names. In this chapter we will discuss HQL in detail. Hibernate also supports native SQL which we will discuss later.

10.2 Query Object

There are several methods available in Query API ( refer Chapter 6 [ section 6.4] ) for more details on Query API.

We can create a query object, using session object. Session Object does provide a createQuery() method which takes a query as an argument and returns a Query object.

Refer below code snippet for reference

Query query = session.createQuery(“ from Object ”);

10.3 from Clause

From is the only mandatory part of HQL.. It tells hibernate from where to pull the data. For example if we have a BOOK-INFO table corresponding to Book object and we want to fire a query on Book object .

Query query = session.createQuery(“ from Book ”);

10.4 Aliases

We can assign an aliases to the objects in HQL by just adding an alias next to object OR using “as” keyword.

To assign an alias for Book object we can use any one of below syntax

Query query = session.createQuery(“ from Book as bookAlias ”);

Query query = session.createQuery(“ from Book bookAlias ”);

10.5 select Clause

To get all the columns of table we can simple use “from Object” . There is no need of select clause but if we want to get selected properties only we need to use select clause providing the list of comma separated name of the object properties .

If select statement has multiple properties to fetch then result will be an List of Object array (List<Object[] >. If select has one property then result will be list of property data type

Assuming name and author are the two properties of Book object then we can use below queries.

10.6 where Clause

Assuming isbn , name and author are the properties of Book object then we can use below queries.

Query query = session.createQuery(“select name from Book where isbn=’123’ ”);

10.7 Supported Operators

a)Logical Operators - AND , OR , NOT

Query query = session.createQuery(“ select name from Book where isbn=’123’ and author=’xyz’ ”);

b) Equality Operators- =, !=

Query query = session.createQuery(“ select name from Book where isbn=’123’ and author !=’xyz’ ”);

c) Comparison Operators - < , > ,<= ,>=, LIKE, NOT LIKE , BETWEEN , NOT BETWEEN , IS NULL , IS NOT NULL , IN

Query query = session.createQuery(“ select name from Book where isbn=’123’ and author like ’%xyz’ ”);

Query query = session.createQuery(“ select name from Book where author in ( ‘xyz’ , ‘abc’ ”);

10.8 Pagination

HQL supports pagination where we can specify the starting record number and number of records to fetch. To do so we can use setFirstResult and setMaxResults methods.

Below query will fetch 15 records starting from 5th record.

Query query = session.createQuery(“select name from Book ”);

query.setFirstResult(5);

query.setMaxResults(15);

10.9 Parameters Binding

Instead of hard coding the variables values , we can bind the values at run time. We can bind the parameters using

Name – also known as named parameter binding

Position - also known as positional parameter binding.

10.9.1 Named Parameter Binding

- Using named parameter binding , query becomes more readable and same parameter can be reused multiple times. To use named parameter we need to use “ :name ” syntax

Query query = session.createQuery(“select name from Book where name = : bookName ”);

query.setString(“bookName”, “XYZ”);

10.9.2 Positional Parameter Binding.

– Similar to traditional JDBC Prepared Statement style , we can use ? as a place holder ( 0 based)

Query query = session.createQuery(“select name from Book where name = ? ”);

query.setString(0, “XYZ”);

10.10 Functions

We can use several functions of SQL in HQL as well. Functions like avg() , sum(), count() , max() , min() , distinct are supported.

String hql = “ select sum(book.price) from Book as book”;

Query query = session.createQuery(hql);

10.11 CRUD Operations

HQL supports INSERT, DELETE, UPDATE ,SELECT clauses. For INSERT we cannot provide arbitrary values , instead we can only use select which means select the record from database and insert it. To insert a new record , we can simply use save() method

10.12 Sorting

We can use ORDER BY clause to sort the results

Query query = session.createQuery(“select name from Book order by name desc ”);