1. HIBERNATE QUERY LANGUAGE
Hibernate Query Language (HQL) is a query language similar to SQL but operates on persistent objects instead of tables. Hibernate translates HQL queries into SQL queries which in turn perform action on database. The advantage of HQL over SQL is that, we can avoid database portability issues and take benefits of Hibernate’s SQL generation and caching strategies.
An HQL query is represented as a Query object which is created using createQuery() method of Session passing the query string to the method. Here is an example:
String str = “from Book”;
Query query = session.createQuery(str);
Here “FROM Book” is an HQL query and query is a Query object that represents the query. The Query interface has a list of useful methods that are used to work with queries such as executing them, specifying parameters etc. Here is an example that prints the information of all persistent objects:
List<Book> books = query.list();
for (Book b : books)
System.out.println(b.getId() +” ”+b.getTitle()+” ”+ b.getPrice());
The following sections describe how to use different HQL clauses.
1.1. From
This clause is used to obtain entire persistent objects.
Query query = session.createQuery(”from Book”);
List<Book> books = query.list();
for (Book b : books)
System.out.println(b.getId() +” ”+b.getTitle()+” ”+ b.getPrice());
This prints the information of all persistent Book objects. The method iist() executes the query and returns all of Book objects as a list.
1.2. Select
Unlike from clause which always returns entire objects, select clause can be used to obtain individual properties of the objects.
Query query = session.createQuery(”select title, price from Book”);
List<Object[]> list = (ArrayList)query.list();
The query only selects title and price properties of Book objects. The list() methods returns a list of Object arrays each of which has two elements—one for title and the other for the price. It can be extracted as follows:
for(Object[] item : list) {
String title = (String)item[0];
int price = ((Integer)item[1]).intValue();
System.out.println(title+” ”+price);
}
1.3. Where
It is used to filter result and can be used with from or select clause.
Query query = session.createQuery(”from Book where price < 400”);
List<Book> books = query.list();
The following uses where with select:
Query query = session.createQuery(”select * from Book where price < 400”);
List<Object[]> list = (ArrayList)query.list();
1.4. Filtering
It enables us to filter data obtained from the database using some query according to a custom condition. Filters have a unique name for identification and can accept parameters and can also be used with XML mapping and annotation. There are two steps to work with filters: define a filter and attach it to a mapping element. A filter in mapping file is defined using <fiiter-def> tag as follows:
<filter-def name=”priceFilter”>
<filter-param name=”price” type=”java.lang.Integer” />
</filter-def>
This filter priceFiiter is then attached to a class or collection:
<class>
…
<filter name=”priceFilter” condition=”pr < :price”/>
</class>
Using the filter is as simple as follows:
Filter filter = session.enableFilter(“priceFilter”);
filter.setParameter(npricen, 400);
Query query = session.createQuery(”from Book”);
List<Book> books = query.list();
This list contains only those books whose price is less than 400.
1.5. Order by
This clause is used to sort the result of an HQL query either in ascending (asc) or descending (desc) order. The following query sorts the result with respect to id in ascending order:
Query query = session.createQuery(”from Book order by id”);
The following query sorts the result with respect to price in descending order:
Query query = session.createQuery(”from Book order by price desc”);
1.6. Group by
This is similar to SQL group by clause. Here is a simple example:
Query query = session.createQuery(”select count(price), price from Book group by price”);
List<Object[]> list = (ArrayList)query.list();
This finds number of books for each price category.
1.7. Parameter Binding
A parameter may be passed to an HQL query by concatenating it as follows:
String val = ”What is Hibernate?”;
Query query = session.createQuery(”FROM Book where title=’”+val+”’”);
List<Book> books = query.list();
This works fine as long as name does not contain any special character such as “’”. For example if the name is What’s Hibernate?, the resultant query FROM Book where title=’What is Hibernate? becomes invalid due to the character “‘” in the word What’s. This situation can be handled in two ways: using named parameters or positional parameters.
Named Parameter
It first defines a named parameter using a colon followed by a parameter name. For example, the following defines a named parameter :title.
Query query = session.createQuery(”FROM Book where title=:title”);
The value of this named parameter may be set using setParameter() method as follows:
query.setParameter(”title”, val);
The rest of the code now works fine. Alternatively, setString() method may be used:
query.setString(”title”, val);
The Query interface has many set methods, one for each data type. The setProperties() method may also be used in general:
Book bk = new Book();
bk.setTitle(val);
query.setProperties(bk);
Positional Parameters
This is similar to prepared statements in SQL. In this case a parameter is defined using question mark (?).
Query query = session.createQuery(”FROM Book where title=?”);
We specify their values using setParameter () or setString () method. Since, parameters defined in this fashion are unnamed, we specify their positions (hence its name) instead of their names:
query.setParameter(0, val);
or
query.setString(0, val);
The basic disadvantage of this method is that if positions of parameters change, we must also change their indexes in set method.
1.8. Update
This is used to update one or more properties of one or more objects. The following is a simple example:
Query query = session.createQuery(“update Book set price=400 where id=1”);
int result = query.executeUpdate();
System.out.println(”Rows affected: ” + result);
1.9. Delete
This clause deletes one or more persistent objects. The following query when executed, deletes all Book objects having price property greater than 400.
Query query = session.createQuery(”delete from Book where price > 400”);
1.10. Insert
HQL currently supports record insertion from one object to another. Here is a simple example:
Query query = session.createQuery(”insert into Book(title, price) select title, price from Book where id=1”);
1.11. Aggregate Methods
HQL supports a set of aggregate methods, similar to SQL and works the same way. Here is a simple example:
Query query = session.createQuery(”select count(*) from Book”);
2. USING NATIVE SQL QUERY
Since, every HQL query will finally be translated into SQL query, applications using HQL queries takes more time to run. Fortunately, Hibernate allows us to use SQL queries in our applications as well. It is sometimes useful to use database-specific features such as query hints or the CONNECT keyword in Oracle. Hibernate also allows us to query, store procedures etc. in SQL. An SQL query is created using createSQLQuery() method as follows:
SQLQuery query = session.createSQLQuery(”select no, name, pr from tbl_book”);
Note that the result of this query is an Object array. The types of array elements are determined from ResultSetMetadata. It is the responsibility of the programmer to convert array elements in proper type.
List<Object[]> list = (ArrayList)query.list();
for(Object[] item : list) {
Integer id = (Integer)item[0];
String title = (String)item[1];
Integer price = (Integer)item[2];
System.out.println(id+” ”+title+” ”+price);
}
However, we can (and should) explicitly indicate the data type of fields being retrieved. Below is a description of how this can be achieved.
query.addScalar(”no”, org.hibernate.type.IntegerType.INSTANCE);
query.addScalar(”name”, org.hibernate.type.StringType.INSTANCE);
query.addScalar(”pr”, org.hibernate.type.IntegerType.INSTANCE);
However, it requires us to manually parse the results. This can be avoided by indicating what data type is being retrieved using addEntity() method:
query.addEntity(Book.class);
The rest of the code works as before:
List<Book> books = query.list();
for (Book b : books)
System.out.println(b.getId() +” ”+b.getTitle()+” ”+ b.getPrice());
3. NAMED QUERIES
Using too many queries makes Java code ugly. Fortunately, Hibernate provides a technique called named queries that lets us separate queries from the coding section of the application. Developers place queries in mapping xml file (.hbm files) or annotated class and assign unique names to them. A Java application can refer to those queries by their name. The following are some advantages of named queries:
- Since, queries are no longer scattered in Java code, it greatly helps in code cleanup.
- It is possible to use the same query multiple times without writing the same query multiple times.
- Their syntax is checked when the session factory is created, making the application fail safe in case of an error.
- If queries are placed in the mapping file, no recompilation is required in case of query modification. However, since most of the cases queries are cached, reloading of session factory is required which may result in server restart up.
However, named queries sometimes make debugging difficult as we have to locate the actual query definition being executed and understand that as well. In terms of performance, named queries do not make much difference nor put any extra cost. Hibernate supports both HQL as well as native SQL named queries.
3.1. Defining Named Queries
The named queries may be defined in the mapping file or in an annotated class.
Using Mapping File
We define an HQL named query using <query> tag in the mapping file as follows:
<hibernate-mapping default-access=”property”>
…
<query name=”find_All_Books_HQL”>
from Book
</query>
…
</hibernate-mapping>
If there is a <ciass> element, put it before <query> element. The <query> tag has a mandatory attribute name which specifies the name assigned to the query and referred by the application. The query itself is written within the <query> tag. Since a query may contain some special characters (such as <, > etc), it is usually placed within CDATA section as follows:
<query name=”find_Books_price_less_than_300_HQL”>
<![CDATA[from Book where price < 300]]>
</query>
An SQL named query is defined using <sqi-query> tag as follows:
<sql-query name=”find_All_Books_SQL”>
<return class=”Book”/> select * from tbl_book
</sql-query>
The <return> tag specifies entity object to be returned by the SQL query. The CDATA section is usually used to embed query as it may contain special characters such as <, > etc. as follows:
<sql-query name=”find_Books_price_less_than_300_SQL”>
<return class=”Book”/>
<![CDATA[select * from tbl_book where pr < 300]]>
</sql-query>
Using Annotation
Named HQL queries may be defined in an annotated class using @NamedQueries annotation as follows:
@NamedQueries({
@NamedQuery(name=”find_All_Books_HQL”, query=”from Book”)
})
The @NamedQueries annotation contains a comma-separated list of queries, each of which is specified by @NamedQuery annotation. @NamedQuery annotation has two important attributes: name and query. The name specifies the name of the query by which it will be referred and the query specifies the actual HQL query string to be executed in database. Multiple queries are defined as follows:
@NamedQueries({
@NamedQuery(name=”find_All_Books_HQL”, query=”from Book”),
@NamedQuery(name=”find_Books_price_less_than_300_HQL”,
query=”from Book where price < 300”)
})
SQL named queries are defined using @NamedNativeQueries and @NamedNativeQuery annotations.
@NamedNativeQueries({
@NamedNativeQuery(name=”find_All_Books_SQL”, query = “select * from tbl_book”, resultClass=Book.class
),
})
The resuitciass attribute specifies entity to be returned by the SQL query. Multiple queries are defined as follows:
@NamedNativeQueries({
@NamedNativeQuery(name=”find_All_Books_SQL”, query = “select * from tbl_book”, resultClass=Book.class),
@NamedNativeQuery(name=”find_Books_price_less_than_300_SQL”,
query = “select * from tbl_book where pr < 300”, resultClass=Book.class)
})
The names of queries must be unique in XML mapping files or annotations. In practice, it’s always a good idea to place all named queries in a separate file and include it in the Java code or in the configuration file. Here is an example of named query file (NQ.hbm.xml):
<?xml version=”1.0″ encoding=”utf-8″?>
<!DOCTYPE hibernate-mapping SYSTEM “http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd”>
<hibernate-mapping>
<query name=”find_All_Books_HQL”><![CDATA[from Book]]</query>
<sql-query name=”find_All_Books_SQL”>
<return class=”Booku/>
<![CDATA[select * from tbl_book]]
</sql-query>
</hibernate-mapping>
To include this file in Java code, use addResource() of configuration object as follows: Configuration cfg = new Configuration();
//add configuration file
cfg.addResource(“NQ.hbm.xml”);
Alternatively, include the mapping resource in the configuration file (say config.xml) using <mapping resource=”NQ.hbm.xml”/> and add the configuration file as follows:
cfg.configure(“config.xml”);
3.2. Calling Named Queries
Once a query is defined, it’s name may be used in getNamedQuery() method to create a Query object as follows:
Query query = session.getNamedQuery(“find_All_Books_HQL”);
Execute the query calling its list() method:
List<Book> books = query.list();
This list can then be iterated to obtain information about all Book objects as follows:
for (Book b : books)
System.out.println(b.getId() + ” “+b.getTitle()+” ” + b.getPrice());
Here is the complete source code (NamedQueryTest.java) of Java program that uses named query stored in NQ.hbm.xml file:
//NamedQueryTest.java import org.hibernate.*;
import org.hibernate.cfg.*;
import java.util.*;
public class NamedQueryTest {
public static void main(String[] args) {
Configuration cfg = new Configuration();
cfg.configure(“config.xml”);
cfg.addResource(“Book.hbm.xml”);
cf g. addResource (“NQ .hbm .xml”);
SessionFactory factory = cfg.buildSessionFactory();
Session session = factory.openSession();
Transaction tx=session.beginTransaction();
try {
Query query = session.getNamedQuery(“find_All_Books_HQLn);
Lis t<Book> books! = query.list() ;
for (Book b : books)
System.out.println(b.getId() +” “+b.getTitle()+” ”+ b.getPrice());
tx.commit();
}catch (Exception e) {
e.printStackTrace();
if (tx!=null) tx.rollback();
}
finally { session.close();factory.close();}
}
Source: Uttam Kumar Roy (2015), Advanced Java programming, Oxford University Press.