ulti-criteria search functionalities, which allow combinations of many optional criteria fields, are common in modern Web-enabled business applications (see Sidebar 1. When to Use Multi-Criteria Searches). An example of this functionality is an online accommodations database (see Figure 1) that allows users to search accommodations by country, availability, type (hotel, bed and breakfast, etc.), and capacity. All the criteria are optional, and the user may use any combination of criteria.

Figure 1: A Multi-Criteria Search Screen

The traditional approach for a Hibernate developer to enable this sort of functionality generally involves building an HQL (Hibernate Query Language) query on the fly, based on the search criteria the user entered. Next, he or she uses this query string to create a Hibernate Query object and then sets the non-null parameter values. The code looks something like this:

...
if (startDate != null) {
    queryBuf.append(firstClause ? " where ": " and ");
    queryBuf.append("a.availabiliyDate >=:startDate");
    parameters.put("startDate",startDate);
    firstClause = false;
}  
...  

(See Listing 1 for a full example. Note that the model has been simplified for the sake of readability. Accommodation availability data would normally be stored in a separate table, of course.)

Listing 1. A Traditional Approach for Building Multi-Criteria HQL Queries

public List searchAccommodation(Date startDate,
                               Date endDate,
                               Country country,
                               AccommodationType type,
                               Integer capacity)
    Map parameters = new HashMap();
    boolean firstClause = true;
    StringBuffer queryBuf 
           = new StringBuffer("from Accommodation a ");

    if (startDate != null) {
        queryBuf.append(firstClause ? " where " : " and ");
        queryBuf.append("a.availabiliyDate >= :startDate");
        parameters.put("startDate",startDate);
        firstClause = false;
    }    
    if (endDate != null) {
        queryBuf.append(firstClause ? " where " : " and ");
        queryBuf.append("a.availabiliyDate <= :endDate");
        parameters.put("endDate",endDate);
        firstClause = false;
    }
    if (country != null) {
        queryBuf.append(firstClause ? " where " : " and ");
        queryBuf.append("a.country = :country");
    }
    if (type != null) {
        queryBuf.append(firstClause ? " where " : " and ");
        queryBuf.append("a.type = :type");
    }
    if (maxCapacity != null) {
        queryBuf.append(firstClause ? " where " : " and ");
        queryBuf.append("a.capacity >= :capacity");
    }

    String hqlQuery = queryBuf.toString();
    Query query = session.createQuery(hqlQuery);
   
    //
    // Set query parameter values
    //
    Iterator iter = parameters.keySet().iterator();
    while (iter.hasNext()) {
        String name = (String) iter.next();
        Object value = map.get(name);
        query.setParameter(name,value);
    }
    //
    // Execute the query
    //
    return query.list();
}

This approach is cumbersome and error-prone. It is also risky in a team-development context, as inexperienced developers will often take dangerous short-cuts using this approach. During code review, I've often come across multi-criteria search functions using error-prone String concatenation, direct use of query parameters in the query string, and so on:

...
if (startDate != null) {
    if (firstClause) {
        query = query + " where ";
    } else {
        query = query + " and ";
    }
    query += " a.availabilityDate >= '" 
             + startDate + "'";
} 
// And so on...               

So, is there better way? Yes, there is: the Hibernate criteria API, a powerful and elegant technique for complex, dynamic search functionalities. Using the online accommodations database cited previously, this article examines the criteria API solution and its benefits.

Introducing the Hibernate Query API

The Hibernate Query API lets you write the previous 36-line example in Listing 1 using just 17 lines of quite readable code (see Listing 2 for the full example):

...
Criteria criteria 
            = session.createCriteria(Accommodation.class);
if (startDate != null) {
    criteria.add(Expression.ge("availabilityDate",
                               startDate);
}                
if (endDate != null) {
    criteria.add(Expression.le("availabilityDate",
                               endDate);
}                
// and so on

Listing 2. Using the Hibernate Criteria API for Multi-Criteria Queries

public List searchAccommodation(Date startDate,
                               Date endDate,
                               Country country,
                               AccommodationType type,
                               Integer capacity)
    Criteria criteria 
            = session.createCriteria(Accommodation.class);
    if (startDate != null) {
        criteria.add(Expression.ge("availabilityDate",
                                   startDate);
    }                
    if (endDate != null) {
        criteria.add(Expression.le("availabilityDate",
                               endDate);
    }                
    if (country != null) {
          criteria.add(Expression.eq("country",country);
    }                
    if (capacity != null) {
          criteria.add(Expression.ge("capacity",capacity);
    }                
    if (type != null) {
          criteria.add(Expression.eq("type",type);
    }                
    List results = criteria.list();
    //
    // Execute the query
    //
    return query.list();
}

Let's take a closer look at how you create and use the Criteria object. The principal class in the API is the Criteria class. You create a Criteria object using the createCriteria() method in the Hibernate session object:

Criteria criteria 
            = session.createCriteria(Accommodation.class);

Once created, you build the query by adding Criterion objects, obtained from the Expression class:

criteria.add(Expression.ge("availabilityDate",
                           startDate));
criteria.add(Expression.le("availabilityDate",
                           endDate));

You can also add 'order by' clauses:

criteria.addOrder( Order.asc("availabilityDate") )

You may use methods such as setFirstResult(), setMaxResults(), and setCacheable() to customize the query behavior in the same way as in the Query interface. So, to get the first 10 results, use the following commands:

criteria.setFirstResult(0)
criteria.setMaxResults(10)

Finally, to execute the query, invoke the list() method (or, if appropriate, the uniqueResult() method). Here is the full example:

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

Expressions

The Hibernate Query API supports a rich set of comparison operators. The standard SQL operators (=, <, ≤, >, ≥ ) are supported by the following methods in the Expression class, respectively: eq(), lt(), le(), gt(), ge(). The following example uses ge() and eq():

session.createCriteria(Accommodation.class)
              .add(Expression.ge("availabilityDate",
                                 startDate)
              .list();
     
session.createCriteria(Accommodation.class)
              .add(Expression.eq("country",country))
              .list();

The API also provides additional comparison operators: like, between, in, isNull, isNotNull, etc. The following example uses between, in, and like:

session.createCriteria(Accommodation.class)
       .add(Expression.between("availabilityDate", 
                               startDate, 
                               endDate))
       .list();
 
session.createCriteria(Country.class)
       .add(Expression.like("A%"))
       .list();
   
favoriteOwners = new ArrayList();
...
session.createCriteria(Accommodation.class)
      .add(Expression.in("owner",
                         favoriteOwners))
      .list();

Finally, if you (really) need to, you can also build nested expressions using 'and' and 'or', as in the following example:

session.createCriteria(Accommodation.class)
       .add(Expression.or(
              Expression.eq("owner",peter),
              Expression.eq("owner",paul)
           )
       .list();

In practice, I have found little use for 'and' and 'or' clauses in true dynamic queries, but who knows? You might think of one!

Ordering Results

To order your query results, you use the addOrder() method and the Order class:

session.createCriteria(Accommodation.class)
       .add(Expression.between("availabilityDate", 
                               startDate, 
                               endDate))
       .addOrder( Order.desc("availabilityDate") )
       .addOrder( Order.asc("country.name") )
       .list();

The generated HQL query would be something like:

from Accommodation a 
order by a.availabilityDate desc, country.name asc

Joining Tables

In HQL, additional associated objects can be loaded in the same query using a "left join fetch" clause (see Sidebar 2. Hibernate Joins), as in the following example:

from Accommodation a
where a.availabilityDate >=:startDate
left join fetch a.country

When using the criteria API, you need to do the same thing using the setFetchMode() function:

session.createCriteria(Accommodation.class)
       .setFetchMode("country",FetchMode.EAGER)
       .list();

Sometimes you don't need to actually load the associated object (or set of objects). You just need to use one of its columns as a query criterion. Say you now need to add a language criterion in the search screen. For example, the user may want to find all accommodations where French is one of the languages spoken. To model this relation, you attach a set of languages to the Accommodation object. In HQL, the query might be the following:

from Accommodation a
join a.languages language
where language.code = 'FR'

Using the Criteria API, you use the createCriteria() method to create an inner join between the two tables, as in the following example:

session.createCriteria(Accommodation.class)
       .createCriteria("languages", "language")
       .add(Expression.eq("language.code", "FR"))
       .list();

Another way of doing this is to use the createAlias() method, which does not involve creating a new instance of the Criteria class:

session.createCriteria(Accommodation.class)
       .createAlias("languages", "language")
       .add(Expression.eq("language.code", "FR"))
       .list();

Note that in both these cases, the languages collection in each Accommodation object will not be initialized—it is simply used as part of the search criteria.

QBE: Query by Example

Another interesting approach is QBE. Basically, you instantiate one of your business objects with the corresponding values coming from the search screen. Hibernate then builds the corresponding query using the non-null field values:

    Accommodation accommodationEx = new Accommodation();
    accommodationEx.setCountry(country);
    accommodationEx.setCapacity(capacity);
	 // and so on

    Example example 
                = Example.create(accommodationEx)
                         .ignoreCase()
                         .excludeZeroes()
                         .excludeProperty("doNotUse") 
                         .enableLike(MatchMode.ANYWHERE);

    return getSession()
              .createCriteria(Accommodation.class)
              .add(example)
              .add(Expression.between("availabilityDate", 
                                      startDate, 
                                      endDate))
                                      .list();

You can also fine-tune the way the QBE query is built in many ways, for example:

• Queries may be case sensitive or insensitive (ignoreCase()).
• Zero-valued fields may be ignored (excludeZeroes()).
• Certain properties may be ignored (excludeProperty()).
• And so on...

The interesting thing here is that, if you have a lot of text fields in your multi-criteria search screen, you can use QBE to initialize the text values, and then use Expression-based criterion objects to add further constraints. In fact, the Example object is just another Criterion. So you can mix and match QBE and Criteria-based querying as you need.

Powerful, Elegant and Definitely Worth a Try

The Hibernate criteria API is a powerful and elegant library, which is well adapted for implementing multi-criteria search functionalities where queries must be built on the fly. Using it in appropriate circumstances will result in cleaner, clearer, more reliable, and more maintainable code. Try it out!