Drawbacks of HQL

Currently HQL (Hibernate Query Language) is used widely to query data using Hibernate. However, there are many drawbacks such as:

1. SQL-like syntax and Non Extensible
2. Relational methodology instead of OO methodology
3. Problem in creating search queries on the fly
4. Complexity increases with number of variable conditions
5. Error-prone String concatenation
6. Direct use of query parameters in the query string

A sample usage for HQL Query:

String query = "select o from Order as o join o.products as p where o.priceTotal > :priceTotalLower and o.priceTotal < :priceTotalUpper";           
Query q = sess.createQuery(query);
q.setDouble("priceTotalLower", Double.parseDouble(lower));
q.setDouble("priceTotalUpper",Double.parseDouble(upper));
List list = q.list();

In comparison to the Criteria API, the above query gets reduced to simple two lines with more comprehension and understanding of the functionality.

List list = sess.createCriteria(Order.class)
.add(Restrictions.between(lower,upper).list();

In addition to the conciseness and readability, following are some of the best parts of using the Criteria API

1. Aspects of the relational approach
2. Reduces the complexity
3. Multi-criteria search functionalities
4. Building Hibernate Queries on the fly
5. Knowledge of SQL not necessary
6. Extensible since it uses OO methodology
7. Interoperable since it has provision for including native SQL clauses also
8. Rich set of comparison operators
9. Business Objects as query parameters, without having to use primary and foreign key references
10. Optimizing queries by providing various JOIN Strategies
11. Provides cleaner, clearer, more reliable and more maintainable code.

What is Criteria API?

There are five core components of the Criteria API.

1. Criteria
2. Criterion
3. Restrictions
4. Projection
5. Order

Criteria class provides the gateway to working with criteria APIs. Criterion class is the object-oriented representation of the relational criterion. Restriction API provides the built-in types for Criterion. Essentially, the Restriction class is a factory to the Criterion class. All of its methods are static.

In Hibernate 2.x, the Expression class provided the services that are now provided by the Restriction class. The Restriction class provides almost all the required restrictions such as equals (eq()), logical and (and()), like (like()) Aggregation and Grouping are provided by the Projection class. Order class represents the “order by” clause of SQL.

Order Interface

In HQL (and SQL), the order by clause allows you to order your query results. This is done using the addOrder() method and the Order class The SQL will have the order clause in the sequence the Order objects were added to the Critieria.

Criteria crit = session.createCriteria(Sale.class) ;
crit.addOrder( Order.desc("date") );
crit.addOrder( Order.asc("product.number") );

Simple Criteria Query

The following code shows how a simple Criteria query is built.

1. It selects the Insurance Object
2. Includes a Where clause insurance name like ‘%a%’
3. Includes another Where clause investmentAmount value between 1000 and 2500 inclusive
4. Sets the number of maximum results as 5

session = sessionFactory.openSession();
Criteria crit = session.createCriteria(Insurance.class);
crit.add(Restrictions.like("insuranceName", "%a%")); 
crit.add(Expression.between("investAmount", new Integer(1000),new Integer(2500))); 
crit.setMaxResults(5); 
List insurances = crit.list();
for(Iterator it =insurances.iterator();it.hasNext();){
    Insurance insurance = (Insurance) it.next();
  System.out.println("ID: " + insurance.getLngInsuranceId());
  System.out.println("Name: " + insurance.getInsuranceName());
  System.out.println("Amount: " + insurance.getInvestAmount());
}

Criterion Chaining

This is a popular method of adding Restrictions, Expressions, Projections and Order object without the need to create additional Objects. This is particularly useful when the Criteria Objects which are extensible are needed to be passed between methods.

List sales = session.createCriteria(Sale.class)
    .add(Expression.ge("date",startDate)
    .add(Expression.le("date",endDate)
    .addOrder( Order.asc("date") )
    .setFirstResult(0)
    .setMaxResults(10)
    .list();

Restriction for WHERE property

The WHERE clause or Restrictions can be easily applied via Restriction.eqProperty(), Restriction.neProperty(), Restriction.leProperty() and Restriction.geProperty()

// Adds a WHERE Clause for comparing two columns,
session.createCriteria(Sale.class)
    .eqProperty("saleDate","releaseDate")
    .list();

Restriction still allows custom restrictions added using Restrictions.sqlRestriction

// Adds a native SQL Restriction in the WHERE Clause
sess.createCriteria(Cat.class)
.add(Restrictions.sqlRestriction("lower({alias}.name) like lower(?)", "Fritz%",Hibernate.STRING) ) .list();

All the static methods available in Restrictions are also available in the Expression API. Also it contains some additional methods such as ilike(“columnname”) which does a lower(columnname) in the query.

Disjunction and Conjunction:

Disjunction and Conjunction are excellent APIs which help perform complex search criteria simple to develop and maintain.

Disjunction indicates a group of Criterion to be ORed

Disjunction disList = Restrictions.disjunction();
disList.add(Restrictions.eq("id",new Integer(1));
disList.add(Restrictions.eq("id",new Integer(2));
sess.createCriteria(Cat.class) .add(disList);
// This gives the WHERE Clause WHERE (id=1 OR id=2)

Conjunction indicates a group of Critierion to be ANDed

Conjunction conList = Restrictions.conjunction();
conList.add(Restrictions.eq("id",new Integer(1));
conList.add(Restrictions.eq("id",new Integer(2));
sess.createCriteria(Cat.class) .add(conList);
// This gives the WHERE Clause WHERE (id=1 AND id=2)

_The Disjunctions and Conjunctions can be nested as well and also along with group of Restrictions.

Conjunction conList = Restrictions.conjunction();
conList.add(Restrictions.disjunction()
       .add(Restrictions.eq("id",new Integer(1))));

How to join Tables using Criteria API

Using HQLs, joins resemble SQL closely. [source,sql]

// use LEFT JOIN FETCH for optimizing queries
from Sale sale where sale.date > :startDate left join fetch sale.product

The same can be achieved using Criteria API with the help of setFetchMode()

session.createCriteria(Sale.class)
      .setFetchMode("product",FetchMode.EAGER)
      .setFetchMode("category",FetchMode.EAGER)
      .list();

Using Criteria API, even Restriction can be applied on the joined tables.Criteria API uses the createCriteria() or createAlias() (no new instance) to create an inner join between the two tables.

// to find all the shirt models with sizes over 40.
// HQL: from Shirt shirt join shirt.availableSizes size where size.number > 40

Session.createCriteria(Shirt.class)
      .createCriteria("availableSizes")
      .add(Expression.gt("number", new Integer(40)))
      .list();

Projections - Aggregation and Grouping

The Projections API is used for aggregation and grouping functionality. A simple example which returns the count of number of cats with age greater than 10.

// Simple Projection
session.createCriteria(Cat.class)
      .add(Restrictions.gt("age", new Integer(10))
      .setProjection(Projections.rowCount())
      .list();

A number of aggregations can be done in a single Criteria and can be done along with a group by clause.

Criteria crit = session.createCriteria(Cat.class)
ProjectionList projList = Projections.projectionList();
projList.add(Projections.rowCount() );
projList.add(Projections.avg("weight") ) ;
projList.add(Projections.max("weight") ) ;
projList.add(Projections.groupProperty("color") ) ) ;
List result = crit.setProjection(projList).list();

To allow the users to query only the required columns Hibernate 3 introduced the Projections.property()

Criteria crit = session.createCriteria(Employee.class);
crit.add(Restrictions.eq("zipCode", zipCode));
crit.add(Restrictions.gt("salary", new Integer(10000));
ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("name"));
projList.add(Projections.property("age"));
projList.add(Projections.property("county"));
projList.add(Projections.property("job"));
crit.setProjection(projList);

This really helps when the table being queried contains 50 to 60 columns and we need only 4 to 5 columns.

Query By Example API

The Example API helps to optimize the query by initializing the text values and by providing extensibility of the Criterion Object.

// Initializing the text values to be used
Accommodation accommodationEx = new Accommodation();
accommodationEx.setCountry(country); 
accommodationEx.setCapacity(capacity);

// Creating and fine tuning the example object
Example example = Example.create(accommodationEx)
    .ignoreCase() //Queries are case insensitive
    .excludeZeroes()  //zero-valued fields are ignored
    .excludeProperty("doNotUse") // this property is excluded
    .enableLike(MatchMode.ANYWHERE); //query string matching uses ‘%X%’

// Using the Example Object and adding further restrictions
List list = session.createCriteria(Accommodation.class)
    .add(example)
    .add(Expression.between("availabilityDate", startDate, endDate))
    .list();