Hibernate Query Language

HQL (Hibernate Query Language)
  • HQL is a powerful query language provided by Hibernate in order to perform manipulations over multiple records.
  • HQL is an object oriented query language, it able to support for the object oriented features like encapsulation, polymorphism, etc. but, SQL is structured query language.
  • HQL is database independent query language, but, SQL is database dependent query language.
  • In case of HQL, we will prepare queries by using POJO class names and their properties, but, in case of SQL , we will prepare queries on the basis of database table names and table columns.
  • HQL queries are prepare by using the syntax’s which are similar to SQL queries syntax’s.
  • HQL is mainly for retrieval operations, but, right from Hibernate3.x version we can use HQL to perform insert, update and delete operations along with select operations, but, SQL is able to allow any type of database operation.
  • In case of JDBC, in case of SQL, if we execute select SQL query then records are retrieved from database table and these records are stored in the form of ResultSet object, which is not implementing java.io.Serializable , so that, it is not possible to transfer in the network, but, in the case of HQL, if we retrieve records then that records will be stored in Collection objects, which are Serializable by default, so that, we are able to carry these objects in the network.
  • HQL is database independent query language, but, SQL is database dependent query language.
  • In case of Hibernate applications, if we process any HQL query then Hibernate Software will convert that HQL Query into database dependent SQL Query and Hibernate software will execute that generated SQL query.

Note

HQL is not suitable where we want to execute Database dependent SQL queries.

E.g

PL/SQL procedures and functions are totally database dependent, where we are unable to use HQl queries.

Procedure to use HQL queries in Hibernate Applications
  1. Represent HQL query by creating Query object.
  2. Apply custom properties on HQL Query or on Query object.
  3. Execute HQL Query
1. Represent HQL query by creating Query object.

Query object is able to store HQL query, to represent Query object Hibernate has provided a predefined interface in the form of “org.hibernate.Query”. To get Query object we have to use the following method from org.hibernate.Session.

public Query createQuery(String hqlQuery)

E.g

Query query = s.createQuery("from Employee");
2. Apply custom properties on HQL Query or on Query object.

In hibernate applications, after getting Query object we have to set the custom properties like providing fetch size , making the results as Cache results and read only results, providing start record position and max no of records, etc. To perform all these custom properties we have to use
the following methods.

public void setFetchSize(int size)
public void setCacheable(boolean b)
public void setMaxResults(int value)
public void setFirstResult(int value)
public void setReadOnly(boolean b)
public void setComment(String comment)
public void setTimeOut(int time)

E.g

q.setFetchSize(10);
q.setCacheable(true);
q.setMaxResults(10);
q.setFirstResult(5);
q.setReadOnly(true);
q.setComment("Employee Details");
q.setTimeOut(10000);
3. Execute HQL Query

To execute HQl queries we will use the following methods.

1. public List list()

It will execute HQL query and generate the results in the form of List.

E.g

List<Employee> list = query.list();

2. public Iterator iterate()

It will execute HQL query and generate the results in the form of Iterator.

E.g

Iterator<Employee> it = query.iterate();

3. public ScrollableResults scoll()

It able to execute HQL query and generate the results in form of ScrollableResults, which is same as ScrollableResultSet object, it allows to read data in both forward and backward directions.

Note

In the case of ScrollableResults we are able to use the following methods in order to retrieve data.

public boolean next()
publci boolean previous()
public void first()
public void last()
public Object get(int position)

4. public Object uniqueResult()

It will execute HQL query and it will return only one result, if more than one result is identified then it will rise an Exception.

E.g

Query query = session.createQuery("from Employee where eno= 111");
Object obj = query.uniqueResult();
Employee e = (Employee)obj;

5. public int executeUpdate()

It can be used to perform the database operations like insert, update, delete, etc. and it will generate rowCount value.

E.g

Query query = session.createQuery("delete from Employee where esal < 10000");
Transaction tx = session.beginTransaction();
int rowCount = query.executeUpdate();
Building Blocks for HQL queries

To prepare HQL queries what are the various elements we have to provide

  1. Clauses
  2. Aggregate Functions
  3. Generic Expressions
  4. Parameters
  5. Subqueries

1. Clauses

These are building blocks to HQL queries, which are able to specify POJO classes property names, conditional expressions, etc.

E.g: from, select, where, order by, group by, having etc.

1. from

It can be used to specify POJO class names and their alias names in HQL queries.

Syntax

From POJO_Class_Name [[AS] var_Name]

E.g

From Employee
FROM Employee
from Employee e
From Employee AS e

2. select

This clause can be used to specify POJO class properties names in order to retrieve individual column values. If we provide select clause with individual POJO class properties in HQL query then results are generated in the form of Object[].

Syntax

[select prop_Names] from POJO_Class_Name [[AS] var_Name]

E.g

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e");
List<Object[]> list = query.list();
for(Object[] obj: list) { 
   for(Object o: obj) {
      System.out.print(o+"\t");
   }
}

3. Where clause

In HQL, where clause can be used to provide a particular conditional expression in order to retrieve the results as per the condition.

Syntax

[select prop_Names] from POJO_Class_Name [[AS] var_Name][where condition]

E.g

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal<=15000");
List<Object[]> list = query.list();
for(Object[] obj: list) { 
   for(Object o: obj) {
      System.out.print(o+"\t");
   }
}

4. Order by

It can be used to retrieve all results either in ascending order or in descending order w.r.t a particular column in HQL, by default, all results are generated in Ascending order only.

Syntax

[select prop_Names] from POJO_Class_Name [[AS] var_Name][where condition][order by prop_Name asc/desc]

E.g

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal<=10000 order by e.ename desc");
List<Object[]> list = query.list();
for(Object[] obj: list) { 
   for(Object o: obj) {
      System.out.print(o+"\t");
   }
}

5. group by

This clause can be used to specify groups over the retrieved results w.r.t a particular column.

Note

In HQL queries, we are able to implement group by clause with the combination of aggregate functions only.

Syntax

[select prop_Names] from POJO_Class_Name [[AS] var_Name][where condition][order by prop_Name asc/desc] [group by column_Name]

E.g

Query query = session.createQuery("select sum(e.esal) FROM Employee AS e group by e.ename");
List<Double> list = query.list();
for(Double val : list) {
   System.out.println(val);
}

6. having

The main intention of having clause is to implement a conditional expression while including elements in groups as per group by clause.

Syntax

[select prop_Names] from POJO_Class_Name [[AS] var_Name][where condition][order by prop_Name asc/desc] [group by column_Name][having Condition]

E.g

Query query = session.createQuery("select count(e.esal) FROM Employee AS e group by e.esal having e.esal<=8000");
List<Long> list = query.list();
for(Long val : list) {
   System.out.println(val);
}

2. Aggregate Functions

The main intention of Aggregate functions is to provide small arithmetic calculations over the results.

E.g

1. count

It able to count the no of results are generated from HQL query and the resultant count value is generated in the form of java.lang.Long type.

E.g

Query query = session.createQuery("select count(e.esal) FROM Employee AS e");
List<Long> list = query.list();
for(Long val : list) {
   System.out.println(val);
}

2. sum

It able to perform addition operation over the results which are generated from HQL query.

Query query = session.createQuery("select sum(e.esal) FROM Employee AS e");
List<Double> list = query.list();
for(Double val : list) {
   System.out.println(val);
}

3. min

It able to get min value over all the results which are generated from HQL query.

E.g

Query query = session.createQuery("select min(e.esal) FROM Employee AS e");
List<Float> list = query.list();
for(Float val : list) {
   System.out.println(val);
}

4. max

It able to get max value over all the results which are generated from HQL query.

Query query = session.createQuery("select max(e.esal) FROM Employee AS e");
List<Float> list = query.list();
for(Float val : list) {
   System.out.println(val);
}

5. avg

It able to generate average value over all the generated results from HQL query.

Query query = session.createQuery("select avg(e.esal) FROM Employee AS e");
List<Double> list = query.list();
for(Double val : list) {
   System.out.println(val);
}

3. Generic Expressions

The main intention of Generic Expressions is to provide expressions in HQL queries in order to perform simple Arithmetic calculations, comparisons, etc.

To provide generic expressions in HQL queries we have to use the following elements.

a. Arithmetic Operators: +, -, *, /, %

E.g

Query query = session.createQuery("select (e.esal-500) FROM Employee AS e");
List<Float> list = query.list();
for(Float val : list) {
   System.out.println(val);
}

b. Comparision Operators: ==, !=, <, >, <=, >=

c. Logical Operators: && or AND, || or OR

d. Scalar Functions

lower(): To get results in lower case letters.
upper(): To get results in Upper case letters.

E.g

Query query = session.createQuery("select e.eno, lower(e.ename), e.esal, upper(e.eaddr) FROM Employee AS e");

e. IN

It will be used along with where clause to specify a list of values in order to retrieve matched results.

E.g

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename IN ('BBB', 'CCC')");

f. BETWEEN

It able to specify min value and max value in order to retrieve the results which are between the specified min value and max value.

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename BETWEEN 'BBB' and 'DDD'");

g. LIKE

It able to provide a particular pattern in HQL query in order to retrieve matched results.

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename LIKE 'B%'");

h. IS NULL

It able to retrieve all the results from database table w.r.t a particular column whose value is null.

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employ ee AS e where e.ename IS NULL");

i. IS NOT NULL

It able to retrieve all the results from database table w.r.t a particular column whose value is not null.

E.g

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.ename IS NOT NULL");

4. Parameters

The main intention of parameters in HQL queries is to take dynamic values in HQL queries. In HQL, there are two types of parameters

  1. Positional parameters
  2. Named Parameters

1. Positional parameters

These parameters are represented in the form of ‘?’ in HQL queries.
After specifying these parameters in HQL queries we must set values to these parameters, to set values to positional parameters we have to use the following method.

public void setParameter(int paramIndex, xxx value)

Where paramIndex may start with 0.
Where xxx may be byte, short, int etc.

Note

In JDBC, positional parameter indexes will start from 1 , but, in HQL parameters indexes will start from 0.

E.g

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal<?");
query.setParameter(0, 10000.0f);
List<Object[]> list = query.list();
List<Object[]> list = query.list();

In HQL queries, we are able to provide more than one positional parameter.

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal>=? and e.esal<=?");
query.setParameter(0, 6000.0f);
query.setParameter(1, 8000.0f);
List<Object[]> list = query.list();

2. Named Parameters

These parameters are represented in the form of ‘:ParamName’ in HQL queries, after providing named parameters in HQL query we have to set values to named parameters, for this, we have to use the following method.

public void setXXX(String param_name, xxx value)

Where xxx may be byte, short, int, String etc.

Note

In HQL queries, we are able to provide more than one named parameters.

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal>=:minSal and e.esal<=:maxSal");
query.setFloat("minSal", 5000.0f);
query.setFloat("maxSal", 25000.0f);
List<Object[]> list = query.list();

In Hibernate applications we are able to provide both positional parameters and named parameters with in a single HQL query, but, first we have to provide all positional parameters after that only we have to provide named parameters, we must not provide any positional parameter after named parameter.

Query query = session.createQuery("select e.eno, e.ename, e.esal, e.eaddr FROM Employee AS e where e.esal>=? and e.esal<=:maxSal");
query.setParameter(0, 6000.0f);
query.setFloat("maxSal", 8000.0f);
List<Object[]> list = query.list();

If we provide named parameter before positional parameter ion HQL query then we are able to get the following error or Exception.

ERROR: Cannot define positional parameter after any named parameters have been defined.

5. Subqueries

Writing a query in another query is called as Sub Query. HQL is supporting sub queries also.

E.g

Query query = session.createQuery("select e1.eno, e1.ename, e1.esal, e1.eaddr FROM Employee AS e1 where e1.esal<(select max(e2.esal) from Employee e2)");
List<Object[]> list = query.list();

6. Pagination

The process of displaying results in more than one page is called as Pagination. Displaying 3 results in a page like three pages out of 9 results is called as “Pagination”. We are able to provide Pagination in Hibernate applications by using setFirstResult() and setMaxResult() methods over Query object.

Hibernate Query Language


Scroll to top