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]