Hibernate Query Language | HQL examples

Hibernate Query Language a.k.a HQL is a query language which deals directly with the persistent entity object instead of the database table directly.

In this section we will see what is hibernate query language, syntax to write various kinds of HQL like Select, updates, insert, delete.

Syntax

Query query=session.createQuery("HQL query comes here....Example.... 
FROM Employee set age=:age where name=:name");

1. SELECT Query

You can write it as “Select * from employee” or as “From Employee”.

Example:

Query query = session.createQuery("From Employee");
List<Employee> list = query.getResultList();

2. Update Query

Example:

Query query=session.createQuery("update Employee 
                                set age=:age where name=:name");
query.setInteger("age", 30);
query.setString("name", "John Doe");
int result=query.executeUpdate();
System.out.println("Rows affected: " + result);

3. Insert Query

Example:

String hql = "INSERT INTO Employee(firstName, lastName, address)"
           + "SELECT firstName, lastName, address FROM old_employee"
           + "where id=:id";
Query query = session.createQuery(hql);
query.setString("id", 101);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

4. Delete Query

Example:

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

5. Pagination in HQL Query

Two methods to set the start and end positions.

Query setFirstResult(int startPosition) : start index position
Query setMaxResults(int maxResult) : number of results from the index set above

Example:

String hql = "FROM Employee";
Query query = session.createQuery(hql);
query.setFirstResult(5);
query.setMaxResults(10);
List results = query.list();

6. Aggregate functions in HQL Query

The aggregate functions available in HQL includes the following:

1. avg(property name): The average of a property’s value.
2. count(property name or *): The number of times a property occurs in the results.
3. min(property name): The minimum value of the property values.
4. max(property name): The maximum value of the property values.
5. sum(property name): The sum total of the property values.

Example:

String hql = "SELECT count(distinct Emp.firstName) FROM Employee Emp";
Query query = session.createQuery(hql);
List results = query.list();

Summary

In this tutorial we learnt how to write HQL(hibernate query language) which is simple, easy for java developers. We went through CRUD operations syntax and examples in HQL, then went through the pagination and aggregate functions with examples.
I hope you liked it !

---