Home arrow Java arrow Page 2 - Hibernate: HQL in Depth
JAVA

Hibernate: HQL in Depth


SQL can be combined with an object-oriented approach. What we end up with is Hibernate Query Language, or HQL. This article discusses how HQL works, and shows how to use it in the real world.

Author Info:
By: A.P.Rajshekhar
Rating: 4 stars4 stars4 stars4 stars4 stars / 86
January 10, 2006
TABLE OF CONTENTS:
  1. · Hibernate: HQL in Depth
  2. · HQL: Understanding Queries
  3. · HQL in the Real World
  4. · Points to Remember

print this article
SEARCH DEVARTICLES

Hibernate: HQL in Depth - HQL: Understanding Queries
(Page 2 of 4 )

The foundation of an ORM framework is its object orientation. The best example of this is how Hibernate provides SQL-like queries. Though the dialect is similar to that of SQL, it acts on objects. Also, HQL has all the attributes of an object-oriented language including polymorphism, inheritance and association. Since it is an object-oriented equivalent of SQL, all of the functions of retrieval are provided out-of-the-box in HQL. These functions can be classified into four categories:

1. Projection

2. Restriction

3. Aggregation

4. Grouping

In HQL, both complex and simple queries can be constructed. All of the above functions participate in both types of queries. In this tutorial, I won’t be discussing the complex queries having sub-queries and in joins. This discussion will consider only joins between two tables. Lets look into the details next.

Projection

Projection lets one specify which objects or properties of objects must be retrieved. In HQL, this can be done with the help of the from clause and the select clause.

The from clause returns all of the instances of the given class. For example:     

from Order

returns all of the instances of the Order class. In other words, the above query is equivalent to the following SQL:

SELECT * FROM ORDER

from is the simplest clause. A query starting with from can contain aliases or multiple class names. To get all the instances of Order and Product the query would be:

from Order, Products

Similarly, an alias can be assigned to a class to be used at a later position in the query. To assign an alias as is used thus:

from Order as o, Products p

When associations and joins are used, aliases come heavily into the picture. But even in simple HQL they can come into play when the class has to be used later in the query. The segment about Restriction will discuss this further. Coming back to associations and joins, consider the following SQL query:

SELECT O.*, P.* FROM ORDERS O, PRODUCT P WHERE O.ORDER_ID=P.ORDER_ID;

As I have already discussed in the previous article, in one-to-many relationships, the class participating in the  ‘one’ end of the association contains a set of objects of classes participating in the ‘many’ end of the relationship. Hence the above SQL query would become:

from Order as o inner join  o.products as product

Now if the Order.products has to be referenced anywhere in the query, using just ‘product’ will work fine. Also by aliasing Order as o, I could use o in place of Order.

Let's consider another scenario where particular attributes of a table have to be retrieved. That’s where the select clause comes handy. It works in the same way as its SQL brethren. In a situation where only particular properties or attributes have to be retrieved, the select clause is recommended. To rephrase the above SQL join query in HQL with the select clause :

select product from Order as o inner join o.products as
product

The above HQL would return all instances of Products from other Orders. If a specific attribute is used, such as the name of the product, then the above HQL would become:

select product.name from Order as o inner join o.products
as product

Similarly any attribute of Orders can also be retrieved in this fashion. For example, to retrieve the id of the order along with the name of the product, the HQL query would be:

select o.id, product.name from Order as o inner join
o.products as product

This brings us to the next aspect of queries. Suppose that the data to be retrieved depends on a condition. The above described approaches won’t work in that case. To work out such a condition, what we need is restrictions.

Restriction

Projection retrieves all the values. But we don't need to do this all the time. This is where restriction comes into play. The where clause performs the restriction operation on the rows. The syntax is similar to that of the SQL where clause. For example, consider this SQL statement:

SELECT * FROM ORDERS WHERE ID=’1092’;

It retrieves all the attributes (columns) of the row that has the ID column value  1092. The next query is the HQL equivalent of SQL:

select o from Order o where o.id=’1092’

As you can see, the use of the where clause is the same in both cases. The only difference is that SQL operates on relations, whereas HQL operates on objects. The having clause also works in the same way. In the section about Grouping, I will be discussing it in detail. Projection and restriction are basic operations; when they are combined with the aggregation functions, a powerful reporting environment can be created. So the aggregation functions are coming up.

 Aggregation

There are situations where the retrieved value is based on a group of values of a column. Average, sum, count of rows, etc. all come under the aggregation. HQL supports the following aggregate functions:

a. avg(…), sum(…)

b. min(…), max(…)

b. count(*), count(…), count(distinct …), count(all …)

All of these functions return scalar values. All the arithmetic operations can be applied to these functions in the select clause, thus:

select max(o.priceTotal)+max(p.price) from Order o join
o.products p group by o.id

The above statement retrieves the sum of two values: the maximum of the total price value in the Orders table and the maximum of price value in the Product table joined on the orders’ id column. Here too restrictions can be applied. Instead of using the where clause, the having clause has to be used. For example, the line of code below is used to retrieve the count of all the orders having a total price greater than 2000/-.

select count(o) from Order o having o.priceTotal>2000
group by o.id

The having clause also works with predicates with results of aggregate functions themselves. To cite an example, if the number of  products whose price is greater than the maximum of a particular amount have to be found, then the query would be:

select count(p) from Product p having o.price>max(amount)
group by p.id

In short, all the tasks that could be performed by using SQL can be done using HQL.

Grouping

In the section regarding aggregation, grouping has been used.  Grouping operates on a set of rows. It groups the dataset according to a specified column/attribute. To group the resultset/dataset/list according to a column, the group by clause is used. To reuse an earlier example, the following statement not only counts all the orders that have a total price above 2000, but also groups them by the id field of the orders table.

select count(o) from Order o having o.priceTotal>2000
group by o.id

The behavior of group by in HQL is similar to that of group by in SQL. In short, almost all of the functions provided by SQL are present in HQL, barring a few that are proprietary extensions to ANSI SQL. That brings us to our next section.


blog comments powered by Disqus
JAVA ARTICLES

- Java Too Insecure, Says Microsoft Researcher
- Google Beats Oracle in Java Ruling
- Deploying Multiple Java Applets as One
- Deploying Java Applets
- Understanding Deployment Frameworks
- Database Programming in Java Using JDBC
- Extension Interfaces and SAX
- Entities, Handlers and SAX
- Advanced SAX
- Conversions and Java Print Streams
- Formatters and Java Print Streams
- Java Print Streams
- Wildcards, Arrays, and Generics in Java
- Wildcards and Generic Methods in Java
- Finishing the Project: Java Web Development ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials