Hibernate – Query Language

Hibernate is a Java framework that makes it easier to create database-interactive Java applications. In HQL, instead of a table name, it uses a class name. As a result, it is a query language that is database-independent. Hibernate converts HQL queries into SQL queries, which are then used to perform database actions. Although Native SQL may be used directly with Hibernate, it is encouraged to utilize HQL wherever feasible to prevent database portability issues.

HQL has many benefits. Some benefits are – they are database independent, polymorphic queries supported, and easy to learn for Java programmers.

The Query interface provides object-oriented methods and capabilities for representing and manipulating HQL queries.

Example: HQL FROM Clause

To load a whole persistent object into memory, the FROM clause is used.

String hib = "FROM Student";
Query query = session.createQuery(hib);
List results = query.list();

Example: HQL SELECT Clause

The SELECT clause is used when only a few attributes of an object are required rather than the entire object.

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

Example: HQL WHERE Clause

Filtering records is done with the WHERE clause. It’s used to retrieve only the records that meet a set of criteria.

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

Example: HQL ORDER BY Clause

The ORDER BY clause is used to sort the results of an HQL query.

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

HQL UPDATE Clause

The UPDATE clause is required to update the value of an attribute.

String hib = "UPDATE Student set name=:n WHERE roll=:i";
Query q=session.createQuery(hib);  
q.setParameter("n","John");  
q.setParameter("i",23);  
int status=q.executeUpdate();  
System.out.println(status); 

HQL DELETE Clause

It is required to delete a value of an attribute.

String hib = "DELETE FROM Student where id=10";
Query query=session.createQuery(hib);  
query.executeUpdate();

HQL INSERT Clause

It is required to Insert values into the relation.

String hib = "insert into Student(first_name, last_name)" +
                "select first_name, last_name from backup_student";
Query query = session.createQuery(hib);
int result = query.executeUpdate();

Pagination using Query

Example

String hib = "from Student"
Query query=session.createQuery(hib);  
query.setFirstResult(5);  
query.setMaxResult(10);  
List list=query.list();

The above example returns the record from 5 to 10.

Aggregate Methods

Similar to SQL, HQL has a number of aggregation techniques.

Example 1: average

String hib = "select avg(marks) from Student";
Query q=session.createQuery(hib); 

Example 2: max

String hib = "select max(marks) from Student";
Query q=session.createQuery(hib); 

Example 3:  min

String hib = "select min(marks) from Student";
Query q=session.createQuery(hib); 

Example 4: count

String hib = "select count(id) from Student";
Query q=session.createQuery(hib);

Example of sum

String hib = "select sum(marks) from Student";
Query q=session.createQuery(hib);  
List<Integer> list=q.list();  
System.out.println(list.get(0));