Chapter 16. Criteria Queries

Hibernate features an intuitive, extensible criteria query API.

16.1. Creating a Criteria instance

The interface org.hibernate.Criteria represents a query against a particular persistent class. The Session is a factory for Criteria instances.

Criteria crit = sess.createCriteria(Cat.class);
crit.setMaxResults(50);
List cats = crit.list();

16.2. Narrowing the result set

An individual query criterion is an instance of the interface org.hibernate.criterion.Criterion. The class org.hibernate.criterion.Restrictions defines factory methods for obtaining certain built-in Criterion types.

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "Fritz%") )
    .add( Restrictions.between("weight", minWeight, maxWeight) )
    .list();

Restrictions may be grouped logically.

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "Fritz%") )
    .add( Restrictions.or(
        Restrictions.eq( "age", new Integer(0) ),
        Restrictions.isNull("age")
    ) )
    .list();
List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.in( "name", new String[] { "Fritz", "Izi", "Pk" } ) )
    .add( Restrictions.disjunction()
        .add( Restrictions.isNull("age") )
    	.add( Restrictions.eq("age", new Integer(0) ) )
    	.add( Restrictions.eq("age", new Integer(1) ) )
    	.add( Restrictions.eq("age", new Integer(2) ) )
    ) )
    .list();

There are quite a range of built-in criterion types (Restrictions subclasses), but one that is especially useful lets you specify SQL directly.

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.sql("lower({alias}.name) like lower(?)", "Fritz%", Hibernate.STRING) )
    .list();

The {alias} placeholder with be replaced by the row alias of the queried entity.

An alternative approach to obtaining a criterion is to get it from a Property instance. You can create a Property by calling Property.forName().

Property age = Property.forName("age");
List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.disjunction()
        .add( age.isNull() )
    	.add( age.eq( new Integer(0) ) )
    	.add( age.eq( new Integer(1) ) )
    	.add( age.eq( new Integer(2) ) )
    ) )
    .add( Property.forName("name").in( new String[] { "Fritz", "Izi", "Pk" } ) )
    .list();

16.3. Ordering the results

You may order the results using org.hibernate.criterion.Order.

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "F%")
    .addOrder( Order.asc("name") )
    .addOrder( Order.desc("age") )
    .setMaxResults(50)
    .list();
List cats = sess.createCriteria(Cat.class)
    .add( Property.forName("name").like("F%") )
    .addOrder( Property.forName("name").asc() )
    .addOrder( Property.forName("age").desc() )
    .setMaxResults(50)
    .list();

16.4. Associations

You may easily specify constraints upon related entities by navigating associations using createCriteria().

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "F%")
    .createCriteria("kittens")
        .add( Restrictions.like("name", "F%")
    .list();

note that the second createCriteria() returns a new instance of Criteria, which refers to the elements of the kittens collection.

The following, alternate form is useful in certain circumstances.

List cats = sess.createCriteria(Cat.class)
    .createAlias("kittens", "kt")
    .createAlias("mate", "mt")
    .add( Restrictions.eqProperty("kt.name", "mt.name") )
    .list();

(createAlias() does not create a new instance of Criteria.)

Note that the kittens collections held by the Cat instances returned by the previous two queries are not pre-filtered by the criteria! If you wish to retrieve just the kittens that match the criteria, you must use returnMaps().

List cats = sess.createCriteria(Cat.class)
    .createCriteria("kittens", "kt")
        .add( Restrictions.eq("name", "F%") )
    .returnMaps()
    .list();
Iterator iter = cats.iterator();
while ( iter.hasNext() ) {
    Map map = (Map) iter.next();
    Cat cat = (Cat) map.get(Criteria.ROOT_ALIAS);
    Cat kitten = (Cat) map.get("kt");
}

16.5. Dynamic association fetching

You may specify association fetching semantics at runtime using setFetchMode().

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "Fritz%") )
    .setFetchMode("mate", FetchMode.EAGER)
    .setFetchMode("kittens", FetchMode.EAGER)
    .list();

This query will fetch both mate and kittens by outer join. See Section 20.1, “Fetching strategies” for more information.

16.6. Example queries

The class org.hibernate.criterion.Example allows you to construct a query criterion from a given instance.

Cat cat = new Cat();
cat.setSex('F');
cat.setColor(Color.BLACK);
List results = session.createCriteria(Cat.class)
    .add( Example.create(cat) )
    .list();

Version properties, identifiers and associations are ignored. By default, null valued properties are excluded.

You can adjust how the Example is applied.

Example example = Example.create(cat)
    .excludeZeroes()           //exclude zero valued properties
    .excludeProperty("color")  //exclude the property named "color"
    .ignoreCase()              //perform case insensitive string comparisons
    .enableLike();             //use like for string comparisons
List results = session.createCriteria(Cat.class)
    .add(example)
    .list();

You can even use examples to place criteria upon associated objects.

List results = session.createCriteria(Cat.class)
    .add( Example.create(cat) )
    .createCriteria("mate")
        .add( Example.create( cat.getMate() ) )
    .list();

16.7. Projections, aggregation and grouping

The class org.hibernate.criterion.Projections is a factory for Projection instances. We apply a projection to a query by calling setProjection().

List results = session.createCriteria(Cat.class)
    .setProjection( Projections.rowCount() )
    .add( Restrictions.eq("color", Color.BLACK) )
    .list();
List results = session.createCriteria(Cat.class)
    .setProjection( Projections.projectionList()
        .add( Projections.rowCount() )
        .add( Projections.avg("weight") )
        .add( Projections.max("weight") )
        .add( Projections.groupProperty("color") )
    )
    .list();

There is no explicit "group by" necessary in a criteria query. Certain projection types are defined to be grouping projections, which also appear in the SQL group by clause.

An alias may optionally be assigned to a projection, so that the projected value may be referred to in restrictions or orderings. Here are two different ways to do this:

List results = session.createCriteria(Cat.class)
    .setProjection( Projections.alias( Projections.groupProperty("color"), "colr" ) )
    .addOrder( Order.asc("colr") )
    .list();
List results = session.createCriteria(Cat.class)
    .setProjection( Projections.groupProperty("color").as("colr") )
    .addOrder( Order.asc("colr") )
    .list();

The alias() and as() methods simply wrap a projection instance in another, aliased, instance of Projection. As a shortcut, you can assign an alias when you add the projection to a projection list:

List results = session.createCriteria(Cat.class)
    .setProjection( Projections.projectionList()
        .add( Projections.rowCount(), "catCountByColor" )
        .add( Projections.avg("weight"), "avgWeight" )
        .add( Projections.max("weight"), "maxWeight" )
        .add( Projections.groupProperty("color"), "color" )
    )
    .addOrder( Order.desc("catCountByColor") )
    .addOrder( Order.desc("avgWeight") )
    .list();
List results = session.createCriteria(Domestic.class, "cat")
    .createAlias("kittens", "kit")
    .setProjection( Projections.projectionList()
        .add( Projections.property("cat.name"), "catName" )
        .add( Projections.property("kit.name"), "kitName" )
    )
    .addOrder( Order.asc("catName") )
    .addOrder( Order.asc("kitName") )
    .list();

You can also use Property.forName() to express projections:

List results = session.createCriteria(Cat.class)
    .setProjection( Property.forName("name") )
    .add( Property.forName("color").eq(Color.BLACK) )
    .list();
List results = session.createCriteria(Cat.class)
    .setProjection( Projections.projectionList()
        .add( Projections.rowCount().as("catCountByColor") )
        .add( Property.forName("weight").avg().as("avgWeight") )
        .add( Property.forName("weight").max().as("maxWeight") )
        .add( Property.forName("color").group().as("color" )
    )
    .addOrder( Order.desc("catCountByColor") )
    .addOrder( Order.desc("avgWeight") )
    .list();

16.8. Detached queries and subqueries

The DetachedCriteria class lets you create a query outside the scope of a session, and then later execute it using some arbitrary Session.

DetachedCriteria query = DetachedCriteria.forClass(Cat.class)
    .add( Property.forName("sex").eq('F') );
    
Session session = ....;
Transaction txn = session.beginTransaction();
List results = query.getExecutableCriteria(session).setMaxResults(100).list();
txn.commit();
session.close();

A DetachedCriteria may also be used to express a subquery. Criterion instances involving subqueries may be obtained via Subqueries or Property.

DetachedCriteria avgWeight = DetachedCriteria.forClass(Cat.class)
	.setProjection( Property.forName("weight").avg() );
session.createCriteria(Cat.class)
	.add( Property.forName("weight).gt(avgWeight) )
	.list();
DetachedCriteria weights = DetachedCriteria.forClass(Cat.class)
	.setProjection( Property.forName("weight") );
session.createCriteria(Cat.class)
	.add( Subqueries.geAll("weight", weights) )
	.list();

Even correlated subqueries are possible:

DetachedCriteria avgWeightForSex = DetachedCriteria.forClass(Cat.class, "cat2")
	.setProjection( Property.forName("weight").avg() )
	.add( Property.forName("cat2.sex").eqProperty("cat.sex") );
session.createCriteria(Cat.class, "cat")
	.add( Property.forName("weight).gt(avgWeightForSex) )
	.list();