Hibernate Query Language (HQL)

Hibernate Query Language is known as HQL. It is a object oriented query language as similar to SQL [Structural Query Language]. HQL works on persistent object and their properties rather than tables and columns. HQL queries is converted into SQL queries to perform task in database.

FROM Clause

Use FROM clause when you want to load complete objects into memory. Follow below given syntax.
Query query = session.createQuery("FROM Student");
List results = query.list();

SELECT Clause

If you want to load few properties of objects instead of complete object use SELECT clause. Follow below given syntax.
Query query = session.createQuery("SELECT S.course FROM Student S");
List results = query.list();

Where Clause

If you want to load few properties of objects use Where clause. Follow below given syntax.
Query query = session.createQuery("FROM Student S WHERE S.id = 1001");
List results = query.list();

AS Clause

If you want to assign a alias name to the class in HQL use AS clause. Follow below given syntax.
Query query = session.createQuery("FROM Student AS S");
List results = query.list();

ORDER BY Clause

To set result coming from database in either ascending (ASC) or descending (DESC) order use ORDER BY Clause. Follow below given syntax.
Query query = session.createQuery("FROM Student.S WHERE S.id > 1001 ORDER BY S.firstName DESC, S.rollno DESC ");
List results = query.list();

GROUP BY Clause

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. Follow below given syntax.
Query query = session.createQuery("SELECT SUM(S.fees), S.firtName FROM Student S GROUP BY S.firstName ");
List results = query.list();

UPDATE Clause

Update clause is use to update one or more properties of one or more objects. The Query interface has a method executeUpdate() for executing HQL UPDATE or DELETE statements. Follow below given syntax.
Query query = session.createQuery("UPDATE Student set rollno = :rollno WHERE id = :student_id");
query.setParameter("rollno", 2002);
query.setParameter("student_id", 1001);
int count = query.executeUpdate();
System.out.println("Rows updated = " + count);

DELETE Clause

The DELETE clause is use to delete one or more objects. Follow below given syntax.
Query query = session.createQuery("DELETE FROM Student WHERE id = :student_id");
query.setParameter("student_id", 1001);
int count = query.executeUpdate();
System.out.println("Rows deleted: " + count);

INSERT Clause

HQL INSERT clause is same as SQL which is use to insert record into database, but HQL insert query only allows to insert from another table. Follow below given syntax.
Query query = session.createQuery("INSERT INTO Student(firstName, lastName, rollno) SELECT firstName, lastName, rollno FROM old_student");
int count = query.executeUpdate();
System.out.println("Rows added: " + count);

Pagination using HQL Query

There are two methods for pagination in HQL.
  • Query setFirstResult(int start) method take a integer parameter that is the first first row in result set, start with row 0.
  • Query setMaxResults(int maxResult) This method retrieve a fixed number of objects.

Aggregate Methods

There are several functions that is supported by HQL.
  • avg(property name)
  • count(property name or *)
  • max(property name)
  • min(property name)
  • sum(property name)