Hibernate uses a powerful query language (HQL) that is similar in appearance to SQL. Compared with SQL however, HQL is fully object-oriented and understands notions like inheritance, polymorphism and association.

Although we can use SQL statements directly with Hibernate using Native SQL, I would recommend using HQL whenever possible to avoid database portability hassles, and to take advantage of Hibernate's SQL generation and caching strategies.

Keywords like SELECT, FROM, WHERE etc. are not case sensitive but properties like table and column names are case sensitive in HQL.

Case-sensitive

With the exception of names of Java classes and properties, queries are case-insensitive. So FrOm is the same as froM is the same as FROM, but org.hibernate.eg.Foo is not org.hibernate.eg.FoO, and foo.bar is not foo.BAR.

In this article, we will use lowercase query language keywords. Some users find queries with uppercase keywords more readable, but this convention is unsuitable for queries embedded in Java code.

Clauses

WHERE Clause

To apply a condition on a column, we can use a WHERE clause. Here is the simple syntax of using WHERE clause:

String hql = "FROM Student S WHERE S.id = 10";
Query query = session.createQuery(hql);
List results = query.list();

ORDER BY Clause

To sort the HQL query results, we will need to use the ORDER BY clause. We can order the results by any property on the objects in the result set either ascending (ASC) or descending (DESC). Here is the simple syntax of using the ORDER BY clause:

String hql = "FROM Student S WHERE S.id > 10 ORDER BY S.grade DESC";
Query query = session.createQuery(hql);
List results = query.list();

 If we wanted to sort by more than one property, we would just add the additional properties to the end of the order by clause, separated by commas as follows:

String hql = "FROM Student S WHERE S.id > 10 ORDER BY S.grade ASC, S.lastName DESC ";
Query query = session.createQuery(hql);
List results = query.list();

SELECT Clause

The SELECT clause provides more control over the result set than the from clause. If we want to obtain a few properties of objects instead of the complete object, use the SELECT clause.

Let’s look at the simple syntax of using the SELECT clause to get just the first_name field of the Student object:

String hql = "SELECT S.firstName FROM Student S";
Query query = session.createQuery(hql);
List results = query.list();

It is important to note here that Student.firstName is a property of the Student object rather than a field of the Student table.

GROUP BY Clause

This clause lets Hibernate pull information from the database and group it based on a value of an attribute and, typically, use the result to include an aggregate value. The following is the simple syntax of using the GROUP BY clause:

String hql = "SELECT SUM(S.grade), S.firtName FROM Student E " +
            "GROUP BY S.firstName";
Query query = session.createQuery(hql);
List results = query.list();

Using Named Parameters

Hibernate supports named parameters in its HQL queries. This makes writing HQL queries that accept input from the user easy and you do not have to defend against SQL injection attacks. Here is the simple syntax of using named parameters:

String hql = "FROM Student S WHERE S.id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("student_id",10);
List results = query.list();

UPDATE Clause

Bulk updates are new to HQL with Hibernate 3, and deletes work differently in Hibernate 3 than they did in Hibernate 2. The Query interface now contains a method called executeUpdate() for executing HQL UPDATE or DELETE statements.

The UPDATE clause can be used to update one or more properties of one or more objects. Here is the simple syntax of using the UPDATE clause:

String hql = "UPDATE Student set grade = :grade "  + 
            "WHERE id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("grade", 8);
query.setParameter("student_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

DELETE Clause

The DELETE clause can be used to delete one or more objects. The following is the simple syntax of using the DELETE clause:

String hql = "DELETE FROM Student "  + 
            "WHERE id = :student_id";
Query query = session.createQuery(hql);
query.setParameter("student_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

INSERT Clause

HQL supports INSERT INTO clause only where records can be inserted from one object to another object. Following is the simple syntax of using INSERT INTO clause:

String hql = "INSERT INTO Student(firstName, lastName, grade)"  + 
            "SELECT firstName, lastName, grade FROM old_student";
Query query = session.createQuery(hql);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

Restrictions with Criteria

We can use the add() method available for the Criteria object to add restriction for a criteria query. Let’s look at an example to add a restriction to return the records with a grade equal to 7:

Criteria cr = session.createCriteria(Student.class);
cr.add(Restrictions.eq("grade", 7));
List results = cr.list();

Following are the few more examples covering different scenarios and can be used as per requirement:

Criteria cr = session.createCriteria(Student.class);

// To get records having grade more than 7
cr.add(Restrictions.gt("grade", 7));

// To get records having grade less than 7
cr.add(Restrictions.lt("grade", 7));

// To get records having fistName starting with zara
cr.add(Restrictions.like("firstName", "liran%"));

// Case sensitive form of the above restriction.
cr.add(Restrictions.ilike("firstName", "liran%"));

// To get records having grade in between 5 and 7
cr.add(Restrictions.between("grade", 5, 7));

// To check if the given property is null
cr.add(Restrictions.isNull("grade"));

// To check if the given property is not null
cr.add(Restrictions.isNotNull("grade"));

// To check if the given property is empty
cr.add(Restrictions.isEmpty("grade"));

// To check if the given property is not empty
cr.add(Restrictions.isNotEmpty("grade"));

 Creating AND or OR conditions using LogicalExpression restrictions as follows:

Criteria cr = session.createCriteria(Student.class);

Criterion grade = Restrictions.gt("grade", 7);
Criterion name = Restrictions.ilike("firstNname","liran%");

// To get records matching with OR conditions
LogicalExpression orExp = Restrictions.or(grade, name);
cr.add( orExp );


// To get records matching with AND conditions
LogicalExpression andExp = Restrictions.and(grade, name);
cr.add( andExp );

List results = cr.list();

All the above conditions can be used directly with HQL as explained in previous tutorial.

Projections and Aggregations

The Criteria API provides the org.hibernate.criterion.Projections class which can be used to get the average, maximum or minimum of the property values. The Projections class is similar to the Restrictions class in that it provides several static factory methods for obtaining Projection instances.

Following are the few examples covering different scenarios and can be used as needed:

Criteria cr = session.createCriteria(Student.class);

// To get total row count.
cr.setProjection(Projections.rowCount());

// To get average of a property.
cr.setProjection(Projections.avg("grade"));

// To get distinct count of a property.
cr.setProjection(Projections.countDistinct("firstName"));

// To get maximum of a property.
cr.setProjection(Projections.max("grade"));

// To get minimum of a property.
cr.setProjection(Projections.min("grade"));

// To get sum of a property.
cr.setProjection(Projections.sum("grade"));

Conclusion

In this article, we studied Hibernate Query Language and understood its difference and similarities with SQL.