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 !! :-)
Thursday, October 05, 2006
Wednesday, October 04, 2006
Hibernate Framework for JAVA
Now a days Object Oriented approach is the most popular. No doubt! But no doubt we need to deal with the database which is mostly based on Relational model. So definitely while building an application that requires interacting with database, the interaction becomes complex when your application is Object Oriented and the database is relational. The complexity in this case arises due to the knots and knaves of cross approach communication.
So when such a need arises where need to make you object orinented application communicate with the relational database, we need a mediator which can make the objects of two different orientations (object oriented and relational) talk to each other. One such good example of this is the Java Database Connectivity (JDBC) API. JDBC allows you to access different databases without much difficulty. But as JDBC is relatively a low level API and provides a low level of abstraction, it can be relied on for the medium or low level projects but for the enterprise level projects. For enterprise level big application, what is more suited is a framework which acts a medisator facilitating the interaction of the objects of Object oriented approach and releational appraoach. Such frameworks ar ecalled Object Relational Mapping (ORM).
Object Relational Mapping:
The process of transformation from a relational approach to an object oriented approach is known as Object Relational Mapping. The Hibernate framework is an ORM : The best example of an ORM framework for Java is Hibernate. But before going further, I would assume that my readers are familier with the object oriented and releational modles :-)
Introduction to Hibernate:
As of now I have made it probabaly clear that what is the need of ORM. ORM reduces our (developer's) task by takig care of how the releational oriented objects will work while working with classes. Although there are various differnet frameworks available in this category, but Hibernate is comparitively simpler and flexible than all others.
Prerequisites to Make Your Application Hibernate Enabled: Following are the three prerequisites to make your web application hibernate enables:
1. Persistence Class: A class is persistent when it is based on the Plain Old Java Object or POJO model. A persistent class is one which has instance variables and getter and setter methods to get and set the variable values. The main function of a persistent class in Hiberation is to to act as a blueprint of the tables in the database with which your web application interacts. So each tables of the database dores have a corresponding class in you application. The instance variables of the class coresponds to the various cloumns of the table. The data-type of the instance variables depend on the data-type of the columns. For example, if there is a column in a particular table in your database whose datatype is varchar(20), the intance variable corresponding to this variable in the blueprint class of the corresponding class will be Stirng. Now guess what each object of this persistence class will represent. Yes you guessed it right, each object of this class will represent a touple (row) in the corresponding table.
Now as we said each tables in the database, will have a corresponding persistence class in your web application, how will the application come to know which class corresponds to which table ? This mapping is mentioned in an XML file called Mapping file.
2. Mapping file: The mapping file contains detailed mapping between the persistence class and the table it represents. The following are the various required attributes of the XML file:
a. Root Element: The root element enclosing all other elements should be hibernate-mapping.
b. Child Elements class: The child elemets of root should be class. The class element, as the name itself indicates is used to map the various persistent classes to the corresponding table names. This element has two attributes. The first attribute is "name", the value of which is the name of a class to be mapped. The name of the class is the fuly qualified name of the class. By fully qualified I mean that the class name should contain the package name also. The second attribute is "table", the value of which specifies the name of the table which the class represents or is a blueprint of. For example, lets assume that u have a persistent class having fully qualified name as "com.myproject.persistent.Entity" which needs to be mapped to the a table named ENTITY, then the class element will be as follows:
<class name="com.myproject.persistent.Entity" table="ORDERS"></class>
Now a table has various columns and the corresponding persistent class has same number of instance variables. So how does the appilication knows which instance variable represents which column ? This mapping is defined by the element property.
c. property: The property element is used to map the varuious columns in a tables to the corresponding instance variables. This element has an attribute named "name" the value of which species the name of the instance variable in the corresponding persistent class which needs to be mapped to a particular column of the table. The name of the column is defined by a child element of this elment named "column". The child element column has an attribute named "name" which specifies the name of the column to which the instance variable is mapped. There are other two attributes of the column element named "length" and and "sql-type" which specify th length and sql-type of the column which is being mapped tothe corresponding instance variable. For example look at the following configuration of the mapping file:
<property name="entityname">
<column name="entityname" sql-type="char(255)" not-null="true"/>
</property>
The above configuration maps an instance variable named "entityname" to a column named "entityname" whose type is char(255).
Now as we know that the primary key always has a special place in a table, so the primary key of the table is mapped using a special element called id.
c. Child Element id: This element is also defined same as the property element. The only differnce lies in the fact that it also contains an extra child element "generator". This element is used to specify if the primary key value can be automatically generated or not. This is specified using an attribute named "class". If the value of the class attribute is "assigned" it tells Hibernate that the application would be used to assign the primarykey vales instaead of auto generating. For example look at the following configuration of the mapping file:
<id name="entityid" type="int" unsaved-value="0">
<column name="entityid" sql-type="integer(2)" not-null="true"/>
<generator class="assigned"/>
</id>
The above configuration tells Hibernate that the name of the primary key is "entityid" and that the type of the column is integer(2) which is mapped to the instance variable "entityid" of the persistence class which is mapped to the instance variable "entityid" of datatype "int". The value "assigned" of the class attribute of the child element generator specifies that the application itself will provide the value of the primary key and therefore it is not auto generated.
So finally if you look at the Mapping File for a persistent class called Entity for a table called entity having two column entityid and date, it would look something like this:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC"-//Hibernate/Hibernate Configuration DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd;
<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">
<class name=" com.myproject.persistent.Entity" table="entity" mutable="true" select- before-update="false" optimistic-lock="version">
<id name="entityid" type="int" unsaved-value="null">
<column name="entityid" sql-type="integer(2)" not-null="true" />
<generator class="assigned" />
</id>
<property name="date" not-null="false" >
<column name="date" sql-type="datetime" not-null="true" />
</property>
</class>
</hibernate-mapping>
The name of the above mapping file is Entity.hbm.xml. All the configuration files are named as
3. Hibernate Configuration file: This file contains the configuration that is required for communication with the underlying database. This file also declares all the mapping files being used by your web application. The root element of this configuration XML file is name as "hibernate-configuration". Following are the two importent elements that are present in this configuration file:
a. property: This element is used to configure various parameters called property parameters, such as the database server type (MySQL, Oracle, PGSQL etc), database URL, username/password to be used to connect to the database, dialect class, etc. All these parameters are defined using the name attribute of the property element. For example, look at the following configuration of the Hibernate Configuration file:
<!-- Database connection settings -->
<property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
<property name="connection.url">jdbc:hsqldb:data/tutorial</property>
<property name="connection.username">sanjeev</property>
<property name="connection.password">qwedsa</property>
</session-factory>
b. mapping: The mapping element of the Hibernate Configuration file is used to declare all the the mapping (hbm ) files. This element has an attribute named "resource" the value of which specifies the path of the hbm file. For example, look at the following configuration of the Hibernate Configuration file:
So the Hibernate Configuration file for a web application where you are using Oracle database, with username scott and password tiger with one connection at a time, the file would look something like this:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC"-//Hibernate/Hibernate Configuration DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd;
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="connection.driver_class"> oracle.jdbc.driver.OracleDriver </property>
<property name="connection.url">jdbc:oracle:thin:@localhost:1521:orcl </property>
<property name="connection.username">scott</property>
<property name="connection.password">tiger</property>
<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">1</property>
<!-- SQL dialect -->
<property name="dialect"> org.hibernate.dialect.OracleDialect </property>
<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>
<mapping resource=" com/someorg/persist/Event.hbm.xml"/></session-factory>
</hibernate-configuration>
So this is it !! To get started with hibernate. I personally feel that the concept of Hibernate is not that tough but using it is a bit tricky as you should design your application in sucha a way that you can use Hibernate easily. In the next post releating to hibernate, I will try to give you an idea about how to use Hibernate in real world.
Till then Happy Reading and Happy Programming :-)
Subscribe to:
Posts (Atom)
