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 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 !! :-)

25 comments:

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

Anonymous said...

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

-Rams

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...

software to join how to join 001 how to join 001

Anonymous said...

software to join join 001 join 001

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 ...

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...

Hな人妻たちの社交場、割り切った付き合いも当然OK!欲求不満のエロ人妻たちを好みに合わせてご紹介します。即会い、幼な妻、セレブ、熟女、SM妻、秘密、以上6つのジャンルから遊んでみたい女性を選んでください

サイドビジネス said...

1日5万円~が手に入るサイドビジネスのご案内です。男狂いのセレブ女性はネットで知り合った男を次々に金の力で食い散らかしています。そんな女性を手玉にとって大金を稼いでみませんか

Hチェッカー said...

みんなで楽しめるHチェッカー!簡単な設問に答えるだけであなたの隠されたH度数がわかっちゃいます!あの人のムッツリ度もバレちゃう診断を今すぐ試してみよう

家出 said...

最近流行の家出掲示板では、各地のネットカフェ等を泊り歩いている家出少女のメッセージが多数書き込みされています。彼女たちはお金がないので掲示板で知り合った男性の家にでもすぐに泊まりに行くようです。あなたも書き込みに返事を返してみませんか

高級チェリー said...

童貞を奪ってみたい女性たちは、男性にとって「初体験」という一生に一度だけの、特別なイベントを共に心に刻み込むことを至上の喜びにしているのです。そんな童貞好きな女性たちと高級チェリーで最高のSEXをしてみませんか

困っています。 said...

最近寂しくて困っています。夜一人で寝るのが凄く寂しいです…隣で添い寝してくれる男性いませんか?見た目とか特に気にしません。優しくて一緒にいてくれる方大歓迎☆一緒に布団で温まりましょう♪shart.enamorado.de-me@docomo.ne.jp

副収入 said...

一晩の割り切ったお付き合いで副収入が得られるサイトのご案内です。アルバイト感覚での挑戦もできる、安心の無料登録システムを採用しておりますので、興味のある方は当サイトをぜひご覧ください

スタービーチ said...

復活、スタービーチ!日本最大の友達探しサイトがついに復活、進化を遂げた新生スタビをやってみませんか?理想のパートナー探しの手助け、合コンパーティー等も随時開催しています。楽しかった頃のスタビを体験しよう

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

なかなか彼氏、彼女が出来ない君達の深層心理を徹底解明♪みんなでモテる度チェックをやって結果交換も自由、合コンや休み時間はモテる度チェックで暇つぶし!次にモテ期が訪れる瞬間をズバリ診断しちゃいます

¥倶楽部 said...

出会ぃも今は¥倶楽部!オンナがオトコを買う時代になりました。当サイトでは逆援希望の女性が男性を自由に選べるシステムを採用しています。経済的に成功を収めた女性ほど金銭面は豊かですが愛に飢えているのです。いますぐTOPページからどうぞ

野外露出 said...

今迄は野外露出がマイナスイメージと囚われがちですが、実際は開放的な気分になり有名人のヌーディストが、オープンになる事を推奨してるぐらいです。このサイトをキッカケに知り合った娘達と野外で楽しみませんか

高級 said...

どうしても相手がセレブだと高級感が有り、近付きにくいと思われがちですが、実際はただ欲望のままに快楽を追い求める、セレブとは掛け離れた女性が居るだけです。今こそ自分の欲望を満たすときです

人妻 said...

最近旦那とマンネリで全然Hしてません。正直もうかなり欲求不満です…誰か相手してくれる方いませんか?空いている時間は多いと思うので都合は合わせやすいと思います。お互い楽しめる関係になりたいな。人妻でも平気な人いたら是非相手してください☆一応18歳以上の人限定でお願いします。上はどこまででも大丈夫なんで excellent.lady@docomo.ne.jp

ライブチャット said...

当サイトでは無料でオナ動画を見ることができます。また、ライブチャット機能でリアルタイムオ○ニーを見るチャンスも高く、興奮間違いなしです。また、一人Hのお手伝いを希望される女性もあり、お手伝いいただけた方には謝礼をお支払いしております

スタービーチ said...

日本最大、だれもが知っている出会い系スタービーチがついに復活、進化を遂げた新生スタビをやってみませんか?趣味の合う理想のパートナー探しの手助け、合コンパーティー等も随時開催しています。楽しかった頃のスタビで遊んでみよう

モテる度 said...

あなたの異性からのモテ度数を診断できる、モテる度チェッカー!日頃モテモテで充実しているあなたもそうでないニートの方も隠されたモテスキルを測定して結果を活用し、今以上にモッテモテになること間違いなし

救援部 said...

女性のオナニーを助ける場が救援部です。ここに所属してる娘のオナニーを見て気に入ったら、実際に会ってオナニーを手伝っても良いし、エッチしても良し、これで報酬Get出来るんですから美味しいバイトですよ

スタービーチ said...

一時代を築いたスタービーチは閉鎖になりましたが、もう一度楽しい思いをしたい、もう一度出会いたいと思う有志により再度復活しました。本家以上に簡単に出会えて楽しい思いを約束します