Hibernate Query Language

Hibernate uses a query language- HQL which is similar to SQL. Compared to SQL, HQL is object oriented and works with persistent object. We can use native SQL query also with hibernate, but we should use HQL so that their will not be any issue related to portability.

From Clause:

Example:

String hqs = "FROM Employee"; //Simple Query
String hqs = "FROM Employee as emp"; //Using As a clause
String hqs = "FROM Employee emp"; //As clause is not mandatory 
String hqs = "FROM Employee , Country"; //Multiple tables

Select clause:

Example:

String hqs = "SELECT E.name FROM Employee E";  // selecting name property only

Aggregate functions:

Example:

String hqs = "SELECT SUM(emp.salary), emp.name FROM Employee emp " +
             "GROUP BY E.name";

The supported aggregate functions are:

avg(...), sum(...), min(...), max(...)

count(*)

count(...), count(distinct ...), count(all...)

WHERE Clause:

Example:

String hqs = "FROM Employee WHERE name = "Ankit"";

ORDER BY Clause:

Example:

String hqs = "FROM Employee WHERE id > 10 ORDER BY name DESC";

GROUP BY clause:

Example:

String hqs = "SELECT SUM(emp.salary), emp.name FROM Employee emp " +
             "GROUP BY E.name";

Queries with Named Paramters:

Example:

String hqs = "FROM Employee WHERE id = :emp_id";
Query query = session.createQuery(hqs);
query.setParameter("emp_id",10);
List results = query.list();

Update Clause:

Example:

String hqs = "UPDATE Employee set name = :emp_name "  + 
             "WHERE id = :emp_id";

Delete Clause:

Example:

String hqs = "DELETE FROM Employee "  + 
             "WHERE id = :emp_id";

Insert Clause:

Example:

String hqs = "INSERT INTO HeadEmployee(name, salary)"  + 
             "SELECT name, salary FROM Employee";

Subqueries:

Example:

String hqs = "FROM Employee as emp where emp.id in (
    select empid from HeadEmployee hemp
)

Pagination:

We can bound the result set for maximum number of rows to be fetched or the first row we wan to retrieve.

Example:

Query q = sess.createQuery("from Employee emp");
q.setFirstResult(5);  // 5th row from DB will be first row in your resultset.
q.setMaxResults(10);  //to retrieve a fixed number maxResults of objects.