HQL-Hibernate Query Language is used for building queries to find or filter data from the database. It looks like SQL’s SELECT Query and case-insensitive. Queries written in HQL are database independent.
Differences from SQL
It’s only used for searching, not updating.
It understands inheritance polymorphism, and object-oriented ownership of associations.
Some pieces of the query are optional like the SELECT clause.
HQL – Basics
Simplest possible HQL query:
FROM (required, except with Session.filter)
Other: ORDER BY, GROUP BY, HAVING,etc.
‘ SELECT ‘ Clause
Select specifies Object and properties to be returned and used in conjunction with the ‘from’ clause.
e.g: select emp.first_name from Employees as emp
Will return all values of firstName in all instances of Employees
‘ FROM ‘ Clause
From clause is commonly used with SELECT.
From object [as alias]
alias means another name given to an object for the convenience.
‘from Employees as emp’
Will return all the instances of Object Employees
‘ WHERE ‘ Clause
Example : from Employees as emp where emp.salary>10000
Listing and Iterating Results
List() method executes the query and returns the results as a list:
List result = session.createQuery(‘from Employees”).list();
Iterate() method also can be used to iterate through the results:
Iterator I = session.createQuery(“from Employees”).iterate();
Using Named Parameter
We can set parameter dynamically in HQL
Query q = session.createQuery(“from Employees emp where emp.firstName=:empName”);
Iterator I = q.iterate();
Using positional parameters
String queryString = “from Employees emp where emp.location like ? and emp.joiningDate > ?”;
List result =session.createQuery(queryString)
Aggregate Functions as supported by HQL
1 count(*): Gives total number of objects persisted in the database.
2. count(query): Gives total number of objects based on the supplied query
3. count(distinct attribute): Gives the count of objects having distinct attribute values
4. count(attribute): Gives count of all the objects in a specified class
5. avg(attribute):Gives average values for the field provided
6. sum(sttribute): Gives sum of all values supplied in the attribute
7. min(attribute): Gives minimum of all the supplied attributes
8. max(attribute): Gives maximum of all the supplied attributes
Comparison operators Used in HQL
HQL supports the same basic operators as SQL: =, <>, <, >, >=, <=,between, not between, in, and not in.
from Employees emp where emp.salary between 5000 and 10000
from Employees emp where emp.salary > 10000
from Employees emp where emp.firstName in ( “George”,”shaan” )
Ordering query results
HQL provides an order by clause which is similar to SQL.
This query returns all employees , ordered by their firstName:
from Employees emp order by emp.firstName
You specify ascending and descending order using asc or desc:
from Employees emp order by emp.firstName desc
You can order by multiple properties:
from Employees emp order by emp.firstName asc, emp.lastName asc
Joins in Hibernate
Left outer join.
Right outer join.
Paging the result
Query query =session.createQuery(“from Employees emp order by emp.firstName asc”);