HQL overview in Hibernate

We have understood how to perform various operations like Insert,Update,delete and Select on single row of a table in Hibernate.

We have different methods in Hibernate like save,get,delete and update methods to perform the same

But what if we want to Select or Update or Insert all the records of a table based on some condition ?

Consider the below scenarios

What if we want to select all the records of a table ?

What if we want to join 2 tables and get the result ?

What if we want to perform aggregations on columns like avg(salary) of Employees.

Answer to all the above queries is “HQL”.

Yes Hibernate Query Language (HQL) is an object oriented query language provided by Hibernate.

HQL is very similar to SQL except that we use Class names instead of table names and attributes of a class instead of Columns of a table that makes it more close to object oriented programming.
Same thing is shown in the below figure

HQL_Overview

Invisible characters to adjust spaceHQL Invisible characters to adjust spaceSQL

HQL is case-insensitive except for java class names and attribute names.

So SeLeCT is the same as sELEct which is same as SELECT, but ‘com.kb.model.Applicant‘ is not same as ‘com.kb.model.APPLICANT‘.

HQL examples

1.Select Query examples

Select all the records of Applicant table.

  1. Query query = session.createQuery("from Applicant ");
  2.  
  3. List list = query.list();
Query query = session.createQuery("from Applicant ");

List list = query.list();

Where Applicant is the class name.

Equivalent SQL query

Select  *  from APPLICANT

Where APPLICANT is the table name

Select all the records of Applicant table whose age >30

  1. Query query = session.createQuery("from Applicant  where  age >:age ");
  2.  
  3. query.setParameter("age", 30);
  4.  
  5. List list = query.list();.
Query query = session.createQuery("from Applicant  where  age >:age ");

query.setParameter("age", 30);

List list = query.list();.

Where Applicant is the class name and age is the attribute name inside Applicant class.

Equivalent SQL query

Select * from APPLICANT where AGE >30

Where APPLICANT is the table name and AGE is the column name.

Select specific columns of Applicant table

  1. Query query = session.createQuery("select a.name,a.age from Applicant a");
  2. List<Object[]> applicants= (List<Object[]>)query.list();
Query query = session.createQuery("select a.name,a.age from Applicant a");
List<Object[]> applicants= (List<Object[]>)query.list();

Where Applicant is the class name , age and name are the attribute names inside Applicant class

Equivalent SQL query

select a.NAME,a.AGE from APPLICANT a

Where APPLICANT is the table name ,AGE and NAME are the column names in APPLICANT table.

2. Update Query example

  1. Query query = session.createQuery("update Applicant  set  age =:age  where  id=:id");
  2.  
  3. query.setParameter("age", 30);
  4.  
  5. query.setParameter("id", 1);
  6.  
  7. int result = query.executeUpdate();
Query query = session.createQuery("update Applicant  set  age =:age  where  id=:id");

query.setParameter("age", 30);

query.setParameter("id", 1);

int result = query.executeUpdate();

Where Applicant is the class name ,id and age are the attribute names inside Applicant class.

Equivalent SQL query

update APPLICANT  set  AGE =30  where  ID=1

Where APPLICANT is the table name and AGE and ID are the column names

3. Delete Query example

  1. Query query = session.createQuery("delete from  Applicant  where  id=:id");
  2.  
  3. query.setParameter("id", 1);
  4.  
  5. int result = query.executeUpdate();
Query query = session.createQuery("delete from  Applicant  where  id=:id");

query.setParameter("id", 1);

int result = query.executeUpdate();

Where Applicant is the class name ,id is the attribute name inside Applicant class.

Equivalent SQL query

Delete from  APPLICANT  where  ID=1

Where APPLICANT is the table name and ID is the column name

4.Insert Query example


HQL INSERT can not be used to insert the records directly but it can be used to insert the records retrieved from another entity using Select statement.

So “Insert into … values(…..)” is not supported in HQL as it supports in SQL.

We need to use Insert but with Select.

  1. Query query = session.createQuery("insert into  ApplicantBackup(id,name,age) select id,name,age from  Applicant");
  2.  
  3. int result = query.executeUpdate();
Query query = session.createQuery("insert into  ApplicantBackup(id,name,age) select id,name,age from  Applicant");

int result = query.executeUpdate();

Where Applicant and ApplicantBackup are the class names

id,name and age are the attribute names inside Applicant and ApplicantBackup classes.

Equivalent SQL query

Insert into  APPLICANT_BACKUP(ID,NAME,AGE) values(1,”john”,30);

In SQL we can directly do insert records with values without using Select, Where APPLICANT_BACKUP is the table name and ID,NAME and AGE are the column names

Note: The query.executeUpdate() method returns number of records inserted, updated or deleted.

5.Aggregate Query examples


select count(*) from Applicant

select avg(age) from Applicant

select max(age) from Applicant

select min(age) from Applicant

Where Applicant is the class name and age is the attribute name inside Applicant class

Advantages of HQL


We know the benefit of Java being platform independent , similary HQL is database independent.

It means queries written using HQL syntax will be able to execute in all the Databases without any modification in it.

HQL supports object oriented features like Inheritance, polymorphism, Associations(Relation ships) using which we can perform complex queries using Join

HQL also supports DDL operations like Insert,update along with select operation.

HQL syntax is very similar to SQL and hence learning HQL does not require huge effort if you know SQL already.

HQL can be used to perform bulk operations like selecting all the records of a Table or updating all records of a table etc.

HQL also supports query parameters to set the values dynamically.

Note:
Apart from HQL, Hibernate also supports native SQL queries using org.hibernate.SQLQuery interface we just need to call createSQLQuery() method of Session interface to use it.

About the Author

Founder of javainsimpleway.com
I love Java and open source technologies and very much passionate about software development.
I like to share my knowledge with others especially on technology 🙂
I have given all the examples as simple as possible to understand for the beginners.
All the code posted on my blog is developed,compiled and tested in my development environment.
If you find any mistakes or bugs, Please drop an email to kb.knowledge.sharing@gmail.com

Connect with me on Facebook for more updates

Share this article on