Hibernate Query Language is an object-oriented query language that works on persistence objects and their properties instead of operating on tables and columns. Hibernate will translate HQL queries into conventional SQL queries during the interaction with a database.
Even though you can use SQL queries using native SQL directly with Hibernate, it is recommended that you use HQL to get the benefits of Hibernate's SQL generation and caching strategies.
In HQL, keywords such as SELECT
, FROM
, WHERE
, GROUP BY
, and so on are not case sensitive but properties such as table and column names are case sensitive. So org.packt.spring.chapter6.hibernate.model.Employee
is not same as org.packt.spring.chapter6.hibernate.model.EMPLOYEE
, whereas SELECT
is similar to Select
.
To use HQL, we need to use Query
object. The Query
interface is an object-oriented representation of HQL. The
Query
object can be obtained by calling the createQuery()
method of the Session
interface. The Query
interface provides a number of methods such as executeUpdate()
, list()
, setFirstResult()
, setMaxResult()
, and so on. The following code snippet uses HQL to get all records:
@Transactional public List<Employee> getAllEmployees() { Session session = sessionFactory.openSession(); String hql = "FROM Employee"; Query query = session.createQuery(hql); <Employee> emList = query.list(); return emList; }
HQL supports clauses to perform database operation. Let's have a look at a few clauses.
The FROM
clause is used to load complete persistence objects into memory. The FROM
clause is the same as the SELECT
clause in SQL, as shown in the following table:
HQL |
SQL |
---|---|
|
|
The syntax to use the FROM
clause is as follows:
String hql = "FROM Employee"; Query query = session.createQuery(hql); List results = query.list();
We can specify the package and class name if needed to fully qualify the class name, as follows:
String hql = "FROM org.packt.spring.chapter6.hibernate.model.Employee"; Query query = session.createQuery(hql); List results = query.list();
The expected output to the console is:
Hibernate: select employee0_.ID as ID0_, employee0_.DEPARTMENT as DEPARTMENT0_, employee0_.FIRST_NAME as FIRST3_0_, employee0_.JOB_TITLE as JOB4_0_, employee0_.LAST_NAME as LAST5_0_, employee0_.SALARY as SALARY0_ from EMPLOYEE_INFO employee0_ Employee [id=1, name=RAVI SONI, jobTitle=AUTHOR department=TECHNOLOGY salary=5000] Employee [id=2, name=Shree Kant, jobTitle=Software Engineer department=Technology salary=3000]
In HQL, the AS
clause is used to assign aliases to the classes when you have long queries. The syntax to use the AS
clause is:
String hql = "FROM Employee AS E"; Query query = session.createQuery(hql); List results = query.list();
The AS
clause is optional, so you can also specify the alias directly after the class name as follows:
String hql = "FROM Employee E"; Query query = session.createQuery(hql); List results = query.list();
The SELECT
clause gives more control over the result set than the FROM
clause. In order to get some specific properties of the object instead of the complete objects, go for the SELECT
clause.
The syntax of the SELECT
clause is as shown here, where it is just trying to get the name field of the Employee
object:
String hql = "SELECT E.firstName FROM Employee E"; Query query = session.createQuery(hql); return query.list();
In this code snippet, E.firstName
is the property of the Employee
object rather than a field of the Employee table.
The WHERE
clause is used to narrow the specific objects that are returned from the storage. The syntax of the WHERE
clause is:
String hql = "FROM Employee E WHERE E.firstName='RAVI'"; Query query = session.createQuery(hql); List results = query.list();
The expected output will be as follows:
Hibernate: select employee0_.ID as ID0_, employee0_.DEPARTMENT as DEPARTMENT0_, employee0_.FIRST_NAME as FIRST3_0_, employee0_.JOB_TITLE as JOB4_0_, employee0_.LAST_NAME as LAST5_0_, employee0_.SALARY as SALARY0_ from EMPLOYEE_INFO employee0_ where employee0_.FIRST_NAME='RAVI' Employee [id=1, name=RAVI SONI, jobTitle=AUTHOR department=TECHNOLOGY salary=5000]
The ORDER BY
clause can be used to sort the results from a HQL query by any property of the objects in the result set, either in the ascending (ASC) or the descending (DESC) order.
The syntax of the ORDER BY
clause is as follows:
String hql = "FROM Employee E ORDER BY E.firstName DESC"; Query query = session.createQuery(hql); List results = query.list();
The expected output will be as follows:
Hibernate: select employee0_.ID as ID0_, employee0_.DEPARTMENT as DEPARTMENT0_, employee0_.FIRST_NAME as FIRST3_0_, employee0_.JOB_TITLE as JOB4_0_, employee0_.LAST_NAME as LAST5_0_, employee0_.SALARY as SALARY0_ from EMPLOYEE_INFO employee0_ order by employee0_.FIRST_NAME DESC Employee [id=2, name=Shree Kant, jobTitle=Software Engineer department=Technology salary=3000] Employee [id=1, name=RAVI SONI, jobTitle=AUTHOR department=TECHNOLOGY salary=5000]
Whenever we need to sort by more than one property in the result set, just add those additional properties to the end of the ORDER BY
clause, separated by commas, as follows:
String hql = "FROM Employee E ORDER BY E.firstName DESC, E.id DESC"; Query query = session.createQuery(hql); List results = query.list();
The expected output will be as follows:
Hibernate: select employee0_.ID as ID0_, employee0_.DEPARTMENT as DEPARTMENT0_, employee0_.FIRST_NAME as FIRST3_0_, employee0_.JOB_TITLE as JOB4_0_, employee0_.LAST_NAME as LAST5_0_, employee0_.SALARY as SALARY0_ from EMPLOYEE_INFO employee0_ order by employee0_.FIRST_NAME DESC, employee0_.ID DESC Employee [id=2, name=Shree Kant, jobTitle=Software Engineer department=Technology salary=3000] Employee [id=1, name=RAVI SONI, jobTitle=AUTHOR department=TECHNOLOGY salary=5000]
Hibernate uses the
GROUP BY
clause to pull information from the database and group them based on the value of the attribute and use the result to include an aggregate value.
HQL supports aggregate functions such as count(*)
, count(distinct x)
, max()
, min()
, avg()
, and sum()
. A few are listed here with descriptions:
Function |
Description |
---|---|
|
This function calculates the average of a property's value |
|
This function counts the number of times a given property occurs in the results |
|
This function returns the maximum value from the group |
|
This function returns the minimum value from the group |
|
This function returns the sum total of the property value |
The syntax of the GROUP BY
clause is as follows:
Session session = sessionFactory.openSession(); String hql = "SELECT SUM(E.salary) FROM Employee E GROUP BY E.firstName"; Query query = session.createQuery(hql); List<Long> groupList = query.list();
The expected output will be as follows:
Hibernate: select sum(employee0_.SALARY) as col_0_0_ from EMPLOYEE_INFO employee0_ group by employee0_.FIRST_NAME Salary: 3000 Salary: 5000
Hibernate supports named parameters in HQL queries to accept input from users and you don't have to defend against SQL injection attacks.
The syntax to use named parameters is as shown here:
Session session = sessionFactory.openSession(); String hql = "FROM Employee E WHERE E.firstName = :employee_firstName"; Query query = session.createQuery(hql); query.setParameter("employee_firstName", "Shree"); return query.list();
The expected output will be as follows:
Hibernate: select employee0_.ID as ID0_, employee0_.DEPARTMENT as DEPARTMENT0_, employee0_.FIRST_NAME as FIRST3_0_, employee0_.JOB_TITLE as JOB4_0_, employee0_.LAST_NAME as LAST5_0_, employee0_.SALARY as SALARY0_ from EMPLOYEE_INFO employee0_ where employee0_.FIRST_NAME=? Employee [id=2, name=Shree Kant, jobTitle=Software Engineer department=Technology salary=3000]
Hibernate supports bulk updates. The Query
interface contains a method named executeUpdate()
to execute the HQL UPDATE
or DELETE
statement. The UPDATE
clause can be used to update one or more object's properties.
The syntax of the UPDATE
clause is as shown here:
String hql = "UPDATE Employee E set E.firstName = :name WHERE id = :employee_id"; Query query = session.createQuery(hql); query.setParameter("name", "Shashi"); query.setParameter("employee_id", 2); int result = query.executeUpdate(); System.out.println("Row affected: " + result);
The expected output will be as follows:
Hibernate: update EMPLOYEE_INFO set FIRST_NAME=? where ID=? Row affected: 1
To delete one or more objects, you can use the DELETE
clause. The syntax of the DELETE
clause is as shown here:
String hql = "DELETE from Employee E WHERE E.id = :employee_id"; Query query = session.createQuery(hql); query.setParameter("employee_id", 2); int result = query.executeUpdate(); System.out.println("Row affected: " + result);
The expected output will be as follows:
Hibernate: delete from EMPLOYEE_INFO where ID=? Row affected: 1
HQL supports pagination, where we can construct a paging component in our application. The Query
interface supports two methods for pagination:
Method |
Description |
---|---|
|
This method takes an argument of type |
|
This method takes an argument of type |
The following code snippet will fetch one row at a time:
String hql = "FROM Employee"; Query query = session.createQuery(hql); query.setFirstResult(0); query.setMaxResults(1); return query.list();
The expected output will be as follows:
Hibernate: select employee0_.ID as ID0_, employee0_.DEPARTMENT as DEPARTMENT0_, employee0_.FIRST_NAME as FIRST3_0_, employee0_.JOB_TITLE as JOB4_0_, employee0_.LAST_NAME as LAST5_0_, employee0_.SALARY as SALARY0_ from EMPLOYEE_INFO employee0_ limit ? Employee [id=1, name=RAVI SONI, jobTitle=AUTHOR department=TECHNOLOGY salary=5000]