Extend the power of Hibernate with HQL. This query language, designed as 'minimal object-oriented extension to SQL', facilitates writing database-type independent queries that are converted to local SQL dialect of underlying database at runtime.
In the previous article, we explored Hibernate, a popular Open Source O/R Mapping framework for J2EE-based enterprise applications. Hibernate automates to a large extent the creation of an efficient persistence layer for the enterprise application. Hibernate makes mapping objects to be persisted to underlying database easier. In other words, Hibernate allows representing an underlying database by using simple Java objects and vice versa.
By facilitating direct retrieval of persistent objects from the database, Hibernate automates/hides the process of creating objects and populating them with data retrieved from the database (common in JDBC-based applications), saving the developer from such tedious routine tasks.
Hibernate uses the following ways to retrieve objects from the database:
- Hibernate Query Language (HQL)
- Query By Criteria (QBC) and Query BY Example (QBE) using Criteria API
- Native SQL queries
The most preferred way is using the Hibernate Query Language (HQL), which is an easy-to-learn and powerful query language, designed as a minimal object-oriented extension to SQL. HQL has syntax and keywords/clauses very similar to SQL. It also supports many other SQL-like features, such as aggregate functions (for example: sum(), max()) and clauses such as group by and order by clause.
Note: The Criteria API also uses HQL behind the scenes.
Although it is possible to use native SQL queries directly with a Hibernate-based persistence layer, it is more efficient to use HQL instead. The reasons of choosing HQL over the other two methods are given below:
- HQL allows representing SQL queries in object-oriented terms—by using objects and properties of objects.
- Instead of returning plain data, HQL queries return the query result(s) in the form of object(s)/tuples of object(s) that are ready to be accessed, operated upon, and manipulated programmatically. This approach does away with the routine task of creating and populating objects from scratch with the "resultset" retrieved from database queried.
- HQL fully supports polymorphic queries. That is, along with the object to be returned as a query result, all child objects (objects of subclasses) of the given object shall be returned.
- HQL is easy to learn and implement, as its syntax and features are very similar to SQL.
- HQL contains many advance features such as pagination, fetch join with dynamic profiling, and so forth, as compared to SQL.
- HQL facilitates writing database-type independent queries that are converted to the native SQL dialect of the underlying database at runtime. This approach helps tap the extra features the native SQL query provides, without using a non-standard native SQL query.
As described earlier, most of HQL's syntax and features are very similar to SQL. An HQL query may consist of following elements:
- Aggregate functions
Clauses are HQL keywords that make up queries. The following table contains some important and commonly/often used clauses and their syntax along with their examples.
Table A: HQL Clauses with their description, syntax, and examples.
||The simplest form of an HQL query. Specifies the object whose instances are to be returned as the query result. Commonly used with the select clause.
||from object [as object_alias]* object_alias simply means another name given to refer to an object for convenience.
||from comp.Dept as dept
Will return all instances of object dept.
||Specifies objects and properties to be returned in the query result set. Used in conjunction with the from clause.
||select dept.mgr from comp.Dept as dept
Will return all values of mgr in all instances of dept.
||Specifies the conditions that should be satisfied by the instances returned as the query result. Used with select and/or from clause.
Here, condition is a combination of logical, relational operators i.e. =, >, AND, NOT etc.
|select dept.mgr from comp.Dept as dept where dept.emp_no > 10
Will return all instances of mgr in dept whose corresponding emp_no values are greater than 10.
||Specifies the order (ascending/descending) in which the properties of objects returned as query results should be listed. Used with the select and from clauses.
||order by object0.property0 [asc|desc][, object1.property0]...
By default, order is ascending unless specified otherwise.
|select dept.mgr from comp.Dept as dept order by dept.emp_no asc
Will return a list of all instances of mgr in dept in ascending order of corresponding emp_no values.
||Specifies the grouping criteria using objects properties, by which the list of objects returned as a query result should be grouped together. Used with the select and/or from clause.
||group by object0.property0[, object1.property0]...
||select dept.emp_no from comp.Dept as dept group by dept.mgr
Will return list of all emp_no instances from dept grouped by corresponding values of mgr.
||a | b: either a or b
Aggregate functions, when used in HQL queries, return an aggregate value (such as sum, average, and count) calculated from property values of all objects satisfying other query criteria. These functions can be used along with the distinct and all options, to return aggregate values calculated from only distinct values and all values (except null values), respectively. Following is a list of aggregate functions with their respective syntax; all of them are self-explanatory.
- count( [ distinct | all ] object | object.property )
- count(*) (equivalent to count(all ...), counts null values also)
- sum ( [ distinct | all ] object.property)
- avg( [ distinct | all ] object.property)
- max( [ distinct | all ] object.property)
- min( [ distinct | all ] object.property)
Example: select sum(emp.sal) from comp.Emp as emp
Subqueries are queries within queries, surrounded by parentheses (). Subqueries get executed before the main query and are used to provide criteria for grouping/ordering/aggregating or narrowing query result with where clause. It should be noted that HQL queries might contain subqueries only when subqueries are supported by the underlying database.
Example: select dept.emp_name from comp.Dept as dept where dept.emp_sal > (select avg(dept.emp_sal) from dept)
For more information on HQL elements, refer to Hibernate's Web site.
Note: HQL queries, except classnames and variables used in them, are case-insensitive. In other words, select is same as SELECT or even select; although it is a general practice to write HQL queries in lowercase for the sake of better readability.
HQL at Work
HQL can be used in any one of the following ways in the Hibernate-based persistence layer code of application:
- Using the createQuery() method of a Session object that returns a Query object.
First, instantiate the Session object using the openSession() method of SessionFactory.
Then, invoke the createQuery() method on the resulting object. Example:
Query q1 = session.createQuery("select emp.sal
from comp.Emp as emp");
List result = q1.list();
Here, the Query object q1 uses the list() method to return a List object containing a list of query results.
Alternatively, the same task can be accomplished using the following code:
List result = session.createQuery("select emp.sal
session.createQuery("select emp.sal from comp.Emp
- Using Criteria API (for QBC and QBE). Here, instead of hand-coded HQL, Criteria API allows specifying various query criteria and executes the equivalent HQL query behind the scenes.
First, a Criteria object is obtained by invoking the Session object's createCriteria() method, providing a class of object(s) desired to be returned as a query result, as a method argument. Now, the query criteria can be specified by using various methods of the Criteria object, for instance:
- add( criteria_expression): for specifying general query criteria such as ordering/grouping of query results (as with the order by/group by clauses), or conditional expressions (as in the where clause).
- setMaxResults(no_of_result): for specifying a maximum limit of query results.
- uniqueResult(): to return only non-duplicate query results.
- list(): for returning a List object containing all query results.
Note: The Queryobject also consists of similar query criteria-specifying methods, such as Criteria, that can be used make the query code look clean and less verbose.
List result = session.createCriteria(Emp.class)
/*emp_no < empno*/
These two methods can be used to perform query operations with HQL in the persistence layer code of the application. They also support native SQL queries in place of HQL. This approach helps you to fall back on native SQL in cases where HQL doesn't provide certain features required but are available through native SQL only.
Hibernate Query Language (HQL) is a rich and powerful object-oriented query language available with the Hibernate O/R Mapping Framework. This query language, designed as a "minimal object-oriented extension to SQL," allows you to represent SQL queries in object-oriented terms—by using objects and properties of objects.
HQL provides many advanced features compared to SQL, yet is easier to learn and use as its syntax and basic features are very similar to SQL. It facilitates writing database-type independent queries that are converted to a local SQL dialect of the underlying database at runtime.
About the Author
Mugdha Chauhan (formerly Mugdha Vairagade) is a senior IT consultant and author. An Open Source supporter, she frequently writes articles and tutorials on useful emerging Open Source projects. Major tech portals including developer.com, IBM developerWorks, CNET Networks, Slashdot, and many eZines regularly publish her work. Her expertise and interests include Java, Linux, XML, wireless application development, and Open Source.