Thursday, October 05, 2006

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 mapping to the order table

Following is the java code for the 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 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"/>
<property name="ordername">
<column name="order_name" />
<property name="orderprice">
<column name="order_price" />
</property> <property name="orderdate">
<column name="order_date" />
<property name="orderperiod">
<column name="order_period" />

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


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:

// 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("Printing All the Orders\n\n");
for(Iterator it=query.iterate();it.hasNext();)
Order order = (Order); // 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)

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


Output of the above code will be as follows:

Printing All the Orders
ORDER ID: 1 ORDER NAME: Domain Registration
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 !! :-)


Anonymous said...

Hi there. This is the last info by theme.
Good luck!


Anonymous said...

its here

phentermine search ENGINE
Farm Search Engine

Anonymous said...

Good day! 

Why do dogs eat their poop? One reason dogs eat their poop is because it just plain taste
good. Another reason is it could be a vitamin deficiency. One other reason for the poop
eating is that it is an instinctual behavior in the wild to eat the poop to keep other predators
from tracking their movements.

Some solutions for stopping poop eating are: You can put meat tenderizer on it.

What do you think?

Wow, I've found the same to be true too!  How did you find that?  

See you soon! WonderGirl 

how I make money with paid surveys

sink sink socks said...
This comment has been removed by a blog administrator.
Rams said...

Isn't the EQL case insensitive, except the java types which are case sensitive?


Anonymous said...

Good day! 

How do you change the size of your monitor?

By the way, I love that too!  Where did you get that at?  

Bye, - MyGirl! 

how I make money with paid surveys

Anonymous said...

Search engines try it


Anonymous said...

Latest news. Viagra, cialis


Anonymous said...

software to join how to join 001 how to join 001

Anonymous said...

software to join join 001 join 001

sink sink socks said...

He hadbeen, he remembered, GIDEON, America's muschie pornos Foremost Native Comedian, atitle that was at once boast and challenge.But is it notcarelessness on the part of this guardian to diminish girl drinking piss its vigilanceduring the night and to allow the suppressed emotions of the Unc.Mostdreams seem to operate bikini voyeur with relatively slight psychic intensities, forthey wait for the awakening.There's no dependence on any coachman; and perhaps as he may besure of business enough this rainy night he may never come atall--being already paid for bringing kontaktanzeigen baden wuerttemberg you here.

Anonymous said...

apartment rental los angeles ca

Anonymous said...

apartment for rent san diego

Anonymous said...

[url=]bentley dierks[/url]

Anonymous said...

Cepasa amigos ))
[url=]hotchkiss co real estate[/url]
[url=]humber college[/url]
[url=]estate real speedwell tn[/url]
[url=]jennifer hudson[/url]

Anonymous said...

Cepasa amigos ))
[url=]herrington marmon[/url]
[url=]durant kevin[/url]
[url=]barnes and noble[/url]
[url=]international harvester[/url]
[url=]dc design[/url]

Anonymous said...

Cepasa amigos ))
[url=]group sex[/url]
[url=]asian girl[/url]
[url=]big penis[/url]
[url=]anal sex[/url]
[url=]big pussy[/url]
[url=]ebony porn[/url]
[url=]arab sex[/url]
[url=]gay anal[/url]

Anonymous said...

Hi Sanjeev, that's a good one ... I think there is a mistake,you are trying to iterate through the 'query' object where as it should be 'hqlquery' obejct. Thanks for the useful article ...

Michael said...

Young Fatties

Anonymous said...

generic name of viagra viagra vs cialis free viagra in the uk viagra online stores does viagra work cheapest uk supplier viagra buy viagra in london england buy generic viagra viagra for cheap splitting viagra viagra logo how viagra works viagra equivalent viagra effects on women

人妻 said...


サイドビジネス said...


Hチェッカー said...


家出 said...


高級チェリー said...


困っています。 said...


副収入 said...


スタービーチ said...


モテる度チェッカー said...


¥倶楽部 said...


野外露出 said...


高級 said...


人妻 said...


ライブチャット said...


スタービーチ said...


モテる度 said...


救援部 said...


スタービーチ said...