Hibernate Query Language is a Hibernate framework object-oriented query language. It is in general similar to SQL but operates not on tables and columns, but with persistent objects and properties of persistent objects. HQL queries are translated under the hood into conventional SQL queries and that performs actions on the database.
You can use SQL statements in the Hibernate framework, but you will need to use Native SQL. So, it can cause database portability hassles. Therefore, it is recommended to use HQL whenever possible and to take advantage of Hibernate SQL generation and caching strategies.
In HQL keywords (SELECT, FROM, WHERE, etc.) are not case sensitive. But, table and column names are case sensitive in HQL. For example, that FrOm can be written as fRoM or just FROM. But, edu.hibernate.test.FOO cannot be replaced with edu.hibernate.test.Foo.
The FROM clause
The FROM clause is used to load a complete persistent object into memory. Let’s look at code snippet to understand how to call the HQL query and how the simplest query with the FROM clause looks like. We will use the User class. It is valuable to mention that we do not usually need to qualify the class name with its package name because auto-import is the default. We don’t need to write “FROM edu.test.User”. Simple “FROM User” is enough.
import java.util.List;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
public class HibernateExample {
public static void main(String[] args)
{
Configuration configuration = new Configuration();
configuration.addAnnotatedClass(edu.hibernate.example.User.class);
Session session = configuration.configure().buildSessionFactory().openSession();
String hql = "FROM User";
List results = session.createQuery(hql).getResultList();
}
}
It is possible to use aliases. For example, “from User as userEntity”. You could also write: “from User userEntity”. In the FROM clause you can use multiple tables. In the case of multiple classes, the result will be in a cartesian product or “cross” join:
FROM User, Car
FROM User as user, Car as car
The query “FROM User as user” returns instances not only of User but also of subclasses (e.g.: FacebookUser, InstaUser). HQL queries use any Java class or interface in the FROM clause. In this case, it will return the list of instances of all classes that extend that class or implement the interface.
SELECT Clause
The SELECT clause gives the developer more control over the result. With this clause, you can obtain just several properties of objects instead of the entire object. The syntax is simple. Let’s get just the first_name field from the User object. The User.firstName is a property of User object rather than a field of the User table.
import java.util.List;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
public class HibernateExample {
public static void main(String[] args)
{
Configuration configuration = new Configuration();
configuration.addAnnotatedClass(edu.hibernate.example.User.class);
Session session = configuration.configure().buildSessionFactory().openSession();
String hql = "SELECT U.firstName FROM User U";
List results = session.createQuery(hql).getResultList();
}
}
WHERE Clause
WHERE clause is used to narrow the specific objects that are returned from the database. It works the same as the SQL WHERE clause but operates with class properties rather than fields of a table. Let’s create a simple example.
import java.util.List;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
public class HibernateExample {
public static void main(String[] args)
{
Configuration configuration = new Configuration();
configuration.addAnnotatedClass(edu.hibernate.example.User.class);
Session session = configuration.configure().buildSessionFactory().openSession();
String hql = "FROM User U WHERE U.id = 4";
List results = session.createQuery(hql).getResultList();
}
}
Expressions
HQL supports the list of expressions that are used in the where clause:
- math operators: +, -, *, /
- comparison operators: =, >=, <=, <>, !=, like
- logical operations: and, or, not
- is empty, is not empty, in, not in, between, is null, is not null, member of and not member of
- string concatenation concat(…,…)
- current_date(), current_time(), and current_timestamp()
- second(…), minute(…), hour(…), day(…), month(…), and year(…)
- substring(), trim(), lower(), upper(), length(), abs(), sqrt(), mod()
- str() for converting values to a readable string
- for collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex()
- scalar function like sign(), trunc(), rtrim(), and sin()
- named parameters :name, :start_date, and :x1
- SQL literals ‘foo’, 69, 6.66E+2, ‘1970-01-01 10:00:01.0’
- Java public static final constants eg.Color.TABBY
ORDER BY Clause
ORDER BY Clause is used to sort your results after calling the HQL query. It is possible to order the result set in an ascending (ASC) or descending (DESC) way. You can sort your result by any property on the objects. For example:
String hql = "FROM User U WHERE U.id < 10 ORDER BY U.id DESC";
        List results = session.createQuery(hql).getResultList();Â
It can turn out that we need to sort a result set by more than one property. It is possible in HQL. We just need to add the additional properties separated by commas to the end of the ORDER BY clause.
String hql = "FROM User U WHERE U.id < 10 ORDER BY U.id DESC, U.firstname DESC"; Â Â Â Â Â Â Â Â List results = session.createQuery(hql).getResultList();
GROUP BY Clause
GROUP BY Clause allows you to pull information from the database and group it based on an attribute value. It uses the result to include an aggregate value. Let’s create a simple example of the use of the GROUP BY Clause.
String hql = "SELECT MAX(U.age), U.firtName FROM User U GROUP BY U.firstName"; Â Â Â Â Â Â Â Â List results = session.createQuery(hql).getResultList(); Â
HQL has a list of aggregate methods similar to the SQL. They have the same name and work the same way as corresponding methods in SQL. But HQL is adapting to all database types. Here is the list of the available aggregate methods supported by HQL:
avg(property name) – this function finds an average of property values. For example:
count(property name or *) – function counts the number of times a property occurs in the results.
For example:
String hql = "SELECT COUNT(*) FROM User U"; List results = session.createQuery(hql).getResultList(); max(property name) - function finds the maximum value of the property values.
For example:
String hql = "SELECT MAX(U.age), U.firtName FROM User U GROUP BY U.firstName"; List results = session.createQuery(hql).getResultList(); min(property name) - the function finds the minimum value of the property values
Foe example:
String hql = "SELECT MIN(U.age) FROM User U"; List results = session.createQuery(hql).getResultList(); sum(property name) - the function that sums the total of the property values. String hql = "SELECT SUM(U.salary) FROM User"; List results = session.createQuery(hql).getResultList();
In HSL there is also a keyword distinct that counts just the unique values in the row set. Here is an example that where the query will return a unique firstnames count:
String hql = "SELECT count(distinct U.firstName) FROM User U"; List results = session.createQuery(hql).getResultList();
Using Named Parameters
It is possible in Hibernate to use named parameters in HQL queries. This is good because we can write HQL queries that accept user input. And we do not need to defend against SQL injection attacks. Here is an example:
String hql = "FROM User U WHERE U.id = :user_id";
List results = session.createQuery(hql).setParameter("user_id",4).getResultList();
UPDATE Clause
HQL supports bulk updates. Therefore, the UPDATE clause can be used to update one or more properties of one or more objects. Let’s look at the example:
String hql = "UPDATE User set age = :age " + "WHERE id = :user_id";
Query query = session.createQuery(hql);
query.setParameter("age", 30);
query.setParameter("user_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);
DELETE Clause
The DELETE clause is used to delete one or multiple objects.Here is an example:
String hql = "DELETE from User WHERE id = :user_id";
Query query = session.createQuery(hql);
query.setParameter("user_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);
INSERT Clause
INSERT INTO clause can only insert values from one object to another object. For example:
String hql = "INSERT INTO User(firstName, lastName, age)"
+ "SELECT firstName, lastName, aage FROM old_user";
int result = session.createQuery(hql).executeUpdate();
System.out.println("Rows affected: " + result);
Pagination using Query
There are two methods for pagination:
Query setFirstResult(int startPosition) – method takes an integer that represents the first row in your result set, starting with row 0. For example:
String hql = "FROM User"; Query query = session.createQuery(hql); query.setFirstResult(1); List results = query.list();
Query setMaxResults(int maxResult)- method tells Hibernate to retrieve a fixed number maxResults of objects. Here is an example:
String hql = "FROM User"; Query query = session.createQuery(hql); query.setMaxResults(5); List results = query.list();
We can also combine both methods:
String hql = "FROM User"; Query query = session.createQuery(hql); query.setFirstResult(1); query.setMaxResults(5); List results = query.list();
Associations and joins
HQL supports the same join types as ANSI SQL: inner join, left outer join, right outer join, full join (not usually useful). For example:
String hql = "from Cat as cat " + " join cat.mate as mate " + " left join cat.kittens as kitten"; Query query = session.createQuery(hql); List results = query.list();
HQL supports implicit and explicit association joining.
The queries from the previous example use the explicit form, where the join keyword is explicitly used in the from clause. This form is recommended.
In the implicit form, the join keyword is not used. The association uses dot-notation. Implicit joins can appear in any HQL Clause. For example:
from Cat as cat where cat.mate.name like ‘%s%’
