Chapter 15. Criteria Queries
Prev |  | Next

## Chapter 15. Criteria Queries

Hibernate features an intuitive, extensible criteria query API.

## 15.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();

## 15.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.sqlRestriction("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();

## 15.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();

## 15.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 a `ResultTransformer`.

    List cats = sess.createCriteria(Cat.class)
        .createCriteria("kittens", "kt")
            .add( Restrictions.eq("name", "F%") )
        .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
        .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");
    }

## 15.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 19.1, “Fetching strategies” for more information.

## 15.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();

## 15.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();

## 15.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();

## 15.9. Queries by natural identifier

For most queries, including criteria queries, the query cache is not very efficient, because query cache invalidation occurs too frequently. However, there is one special kind of query where we can optimize the cache invalidation algorithm: lookups by a constant natural key. In some applications, this kind of query occurs frequently. The criteria API provides special provision for this use case.

First, you should map the natural key of your entity using `<natural-id>`, and enable use of the second-level cache.

    <class name="User">
        <cache usage="read-write"/>
        <id name="id">
            <generator class="increment"/>
        </id>
        <natural-id>
            <property name="name"/>
            <property name="org"/>
        </natural-id>
        <property name="password"/>
    </class>

Note that this functionality is not intended for use with entities with mutable natural keys.

Next, enable the Hibernate query cache.

Now, `Restrictions.naturalId()` allows us to make use of the more efficient cache algorithm.

    session.createCriteria(User.class)
        .add( Restrictions.naturalId()
            .set("name", "gavin")
            .set("org", "hb")
        ).setCacheable(true)
        .uniqueResult();

Prev | Up | Next
Chapter 14. HQL: The Hibernate Query Language | Home | Chapter 16. Native SQL