30. Legacy Hibernate Criteria Queries

Hibernate features an intuitive, extensible criteria query API.

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

30.2. JPA vs. Hibernate entity name

When using the Session#createCriteria(String entityName) or StatelessSession#createCriteria(String entityName), the entityName means the fully-qualified name of the underlying entity and not the name denoted by the name attribute of the JPA @Entity annotation.

Considering you have the following entity:

@Entity(name = "ApplicationEvent")
public static class Event {
    @Id
    private Long id;
    private String name;
}

If you provide the JPA entity name to a legacy Criteria query:

List<Event> events = entityManager.unwrap( Session.class )
    .createCriteria( "ApplicationEvent" )
    .list();

Hibernate is going to throw the following MappingException:

org.hibernate.MappingException: Unknown entity: ApplicationEvent

On the other hand, the Hibernate entity name (the fully qualified class name) works just fine:

List<Event> events = entityManager.unwrap( Session.class )
    .createCriteria( Event.class.getName() )
    .list();

For more about this topic, check out the HHH-2597 JIRA issue.

30.3. 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 can 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 a range of built-in criterion types (Restrictions subclasses). One of the most useful Restrictions allows you to specify SQL directly.

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

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

You can also obtain a criterion 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();

30.4. Ordering the results

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

List cats = sess.createCriteria( Cat.class )
    .add( Restrictions.like( "name", "F%" ) )
    .addOrder( Order.asc( "name" ).nulls( NullPrecedence.LAST ) )
    .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();

30.5. Associations

By navigating associations using createCriteria(), you can specify constraints upon related entities:

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

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

There is also an alternate form that 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();

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

The kittens collections held by the Cat instances returned by the previous two queries are not pre-filtered by the criteria. If you want 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" );
}

Additionally, you may manipulate the result set using a left outer join:

List cats = session.createCriteria( Cat.class )
   .createAlias( "mate", "mt", Criteria.LEFT_JOIN, Restrictions.like( "mt.name", "good%" ) )
   .addOrder( Order.asc( "mt.age" ) )
   .list();

This will return all of the Cats with a mate whose name starts with “good” ordered by their mate’s age, and all cats who do not have a mate. This is useful when there is a need to order or limit in the database prior to returning complex/large result sets, and removes many instances where multiple queries would have to be performed and the results unioned by Java in memory.

Without this feature, firstly all of the cats without a mate would need to be loaded in one query. Then a second query would need to retrieve the cats with mates whose name started with “good” sorted by the mates age. Thirdly, in memory, the lists would need to be joined manually.

30.6. Dynamic association fetching

You can 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.

30.7. Components

To add a restriction against a property of an embedded component, the component property name should be prepended to the property name when creating the Restriction. The criteria object should be created on the owning entity, and cannot be created on the component itself. For example, suppose the Cat has a component property fullName with sub-properties firstName and lastName:

List cats = session.createCriteria( Cat.class )
    .add( Restrictions.eq( "fullName.lastName", "Cattington" ) )
    .list();

Note: this does not apply when querying collections of components, for that see Collections below.

30.8. Collections

When using criteria against collections, there are two distinct cases. One is if the collection contains entities (e.g. <one-to-many/> or <many-to-many/>) or components (<composite-element/> ), and the second is if the collection contains scalar values (<element/>). In the first case, the syntax is as given above in the section Associations where we restrict the kittens collection. Essentially, we create a Criteria object against the collection property and restrict the entity or component properties using that instance.

For querying a collection of basic values, we still create the Criteria object against the collection, but to reference the value, we use the special property “elements”. For an indexed collection, we can also reference the index property using the special property “indices”.

List cats = session.createCriteria( Cat.class )
    .createCriteria( "nickNames" )
    .add( Restrictions.eq( "elements", "BadBoy" ) )
    .list();

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

30.10. Projections, aggregation and grouping

The class org.hibernate.criterion.Projections is a factory for Projection instances. You can 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 can be assigned to a projection so that the projected value can 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();

30.11. Detached queries and subqueries

The DetachedCriteria class allows you to create a query outside the scope of a session and then execute it using an 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 can also be used to express a subquery. Criterion instances involving subqueries can 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();

Correlated subqueries are also 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();

Example of multi-column restriction based on a subquery:

DetachedCriteria sizeQuery = DetachedCriteria.forClass( Man.class )
    .setProjection( Projections.projectionList()
        .add( Projections.property( "weight" ) )
        .add( Projections.property( "height" ) )
    )
    .add( Restrictions.eq( "name", "John" ) );
session.createCriteria( Woman.class )
    .add( Subqueries.propertiesEq( new String[] { "weight", "height" }, sizeQuery ) )
    .list();

30.12. Queries by natural identifier

For most queries, including criteria queries, the query cache is not efficient because query cache invalidation occurs too frequently. However, there is a special kind of query where you 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, 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>

This functionality is not intended for use with entities with mutable natural keys.

Once you have enabled the Hibernate query cache, the Restrictions.naturalId() allows you 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();