HQL : Hibernate Query Language

Hibernate Query Language (HQL) is a query language used by hibernate enabled applications. If you are not very familiar with Hibernate then please read my earlier blog Hibernate For Java first. I hope after reading Hibernate For Java article you would be a little familiar with the hibernate concept. HQL is similar to SQL with the difference that it is case sensitive (except for the java class names and property names that it uses). The main use of HQL is to enable Hibernate framework generate sql queries and execute it against underlying database. HQL is different from SQL also in the fact that it uses Classes (which maps to tables in the databases) and properties (which corresponds to the name of the columns in the table) instead of tables and columns. HQL also supports Polymorphism, Associations, and is much less verbose than SQL. Before going further, first let us discus what are the benefits of using HQL or Why to Use HQL? So here we go!!

Why Use HQL?

1. Representing SQL queries in form of Objects: HQL allows you to write and run the SQL queries using objects. Thus your object oriented code more readable and homogeneous.
2. Ready made resultset Objects: When hibernate enabled applications run the HQL, the result that u get is a ready-made object ready to be used and thus eleminating the need to first create an object and then populate it using the query result set.
3. Support for Advance Features: HQL contains many advance features such as pagination, fetch join with dynamic profiling, Inner/outer/full joins, Cartesian products. It also supports Projection, Aggregation (max, Avis) and grouping, Ordering, Sub queries and SQL function calls.
4. Database Independency: Queries written in HQL are database independent (If database supports the underlying feature).
5. SubQuery Support: HQL also supports sub queries in case the underlying database supports it.

Note: While using HQL, you always need to keep in mind that the feature that you are going to use is supported by the underlying database. It will be better if u use such features that are supported dby all the databases to keep your application database independent.

Understanding HQL Syntax

Any Hibernate Query Language like SQL queries consists of following three elements:

1. Clauses : Clauses in the HQL are: (a) from (b) select (c) where (d) order by (e) group by
2. Aggregate functions : (a) avg, sum, min, max (b) count(*) (c) count(...), count(distinct ...), count(all...)
3. Subqueries : Subqueries are nothing but its a query within another query. Hibernate supports Subqueries if the underlying database supports it.

Let us explore each clause with an example. For this let us go step by step as follows:

Step 1 : Create Table order

CREATE TABLE "order" ( "order_id" int(11) NOT NULL default '0', "order_name" varchar(50) default NULL, "order_price" int(11) default NULL, "order_date" datetime default NULL, "order_period" int(2) default 1, PRIMARY KEY ("orderid")) TYPE=MyISAM;

Step 2 : Populate order Table

Now let us put some dummy data in this table to be used in examples later as follows:

insert into "order" values (1,'Domain Registration',10,'2006-01-05 00:00:00',1);
insert into "order" values (2,'Managed DNS',100,'2006-10-01 00:00:00',1);
insert into "order" values (3,'Domain Fwd',500,'2006-10-15 00:00:00',1);
insert into "order" values (4,'Email Hosting',2500,'2006-01-01 00:00:00',1);
insert into "order" values (5,'Windows Hosting',500,'2005-01-01 00:00:00',1);
insert into "order" values (6,'Domain Registration',20,'2006-01-05 00:00:00',2);
insert into "order" values (7,'Windows Hosting',1000,'2006-01-05 00:00:00',2);
insert into "order" values (8,'Email Hosting',5000,'2006-01-05 00:00:00',2);

Step 3 : Create Plain Old Java Object (POJO) persistent class Order.java mapping to the order table

Following is the java code for the Order.java class:

package myproject.HQL.tutorial;import java.util.Date;
/** * This Java Class maps to the order table */
public class Order
{
private long orderid;
private String ordername;
private int orderprice;
private Date orderdate;
private int orderperiod;
// Now write the getter setter methods for each of the instance variables. This class is a standard JavaBean.
public long getOrderId()
{
return orderid;
}
public void setOrderId(long orderid)
{
this.orderid = orderid;
}
public String getOrderName()
{
return ordername;
}
public void setOrderName(String ordername)
{
this.ordername = ordername;
}
public Date getOrderDate()
{
return orderdate;
}
public void setOrderDate(Date orderdate)
{
this.orderdate = orderdate;
}
public int getOrderPrice()
{
return orderprice;
}
public void setOrderPrice(int orderprice)
{
this.orderprice = orderprice;
public int getOrderPeriod()
{
return orderperiod;
}
public void setOrderPrice(int orderperiod)
{
this.orderperiod = orderperiod;
}
}

Step 4: Adding mappings into order.hbm.xml file

Now we need to map the class Order.java class to the order table, so write the following code in the order.hbm.xml file:

<class name="myproject.HQL.tutorial" table="order">
<id name="orderid" type="long" column="order_id" >
<generator class="increment"/>
</id>
<property name="ordername">
<column name="order_name" />
</property>
<property name="orderprice">
<column name="order_price" />
</property> <property name="orderdate">
<column name="order_date" />
</property>
<property name="orderperiod">
<column name="order_period" />
</property>
</class>

Step 5: Using HQL Clauses

Now we have done with a table in our database and a POJO class that maps to the table and the mapping file also. Time to go and fire some HQL query on the database and get some useful information from it, so here we go exploring each clause one by one:

The HQL From Clause

The From clause is the simplest one. Let us say we want to select all the order present in the order table. for that we just need to write the following code in the java file:

from Order as order

or

from Order order

(the as keyword is optional)

The first word "Order" indicates the name of the class which maps to the table from which you want to fetch the rows (here the orders table). The second word "order" gives a alias to the resultset. This is the handle using which you can iterate through the records and perform whatever operation you want to perform on them. For example let us assume that we want to fetch all the orders present in the order table and then print them one by one. For this in your java file you can write the following code:

try
{
// The line below reads the hibernate.cfg.xml and prepare hibernate for use.
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session =sessionFactory.openSession();
String query ="from Order as order";
Query hqlquery = session.createQuery(query);
System.out.println("=============================================\n");
System.out.println("Printing All the Orders\n\n");
for(Iterator it=query.iterate();it.hasNext();)
{
Order order = (Order)it.next(); // as each row fetched is an object of the corresponding POJO class.
System.out.println("ORDER ID: " + order.getOrderId());
System.out.println(" ORDER NAME: " + order.getOrderName()+"\n"); // The resultset contains every other column value also which we can print, but I am just mentioning first two here for the sake of space and time. You can print other column values also in the similar manner if you want.
}
System.out.println("=============================================\n"); session.close();
}
catch(Exception e)
{
System.out.println(e.getMessage());
}

Note: You would need to import the following lasses in you java file:

org.hibernate.Session
org.hibernate.*
org.hibernate.cfg.*

Output of the above code will be as follows:

=============================================
Printing All the Orders
ORDER ID: 1 ORDER NAME: Domain Registration
ORDER ID: 2 ORDER NAME: Managed DNS
ORDER ID: 3 ORDER NAME: Domain Fwd
ORDER ID: 4 ORDER NAME: Email Hosting
ORDER ID: 5 ORDER NAME: Windows Hosting
ORDER ID: 6 ORDER NAME: Domain Registration
ORDER ID: 7 ORDER NAME: Windows Hosting
ORDER ID: 8 ORDER NAME: Email Hosting
=============================================

Note: You can also perform cross join of two tables in the same clasuse just by mentioning the multiple class names which map to the multiple tables that you want to cross join. For example if you want to cross join two tables Order and Owners then you would write the HQL as follows:

from Order, Owner or from Order as order, Owner as owner

The HQL Select Clause

The select clause is like the from clause only with the difference that you can specify the columns that you want to be selected unlike the from clause which returns all the columns of the table (this is well understood by you I hope if you are familiar with the SQL queries already). For example if we want exactly the same output as of the from clause using the select clause we would replace the string in the above code by the following query:

Select order.orderid,order.ordername from Order as order

The HQL Where Clause

Now you may limit the number of rows selected by the select clause by specifying the condition using the where. For example lwt us say that we want to select only Domain Registration orders, then you wouuld replace the query in the from clause code by the following query:

from Order as order where order.ordername = 'Domain Registration'

The HQL Order By Clause

If you want to order the list returned by your query by any property (instance variable of the POJO class or the column of the table) then the order by clause can be used. For example if you want to sort the orders returned by the from clause according to the order name, order date in ascending order, then you would replace the from query by the following query:

from Order order order by order.ordername asc, order.orderdate asc

The HQL Group By Clause

You can use the group by clause to aggregate values returned in the resultset of the query. For example if you want to see how much reenue you have generated till date on different orders, then you would replace the from query with the following query:

select sum(order.orderprice) from Order order group by order.ordername

So this is it. Simple and powerful No!! Yes no doubt!! Stayed tuened for more stuff on Hibernate.

Till then Happy Reading and Happy Programming !! :-)