HowTo use Hibernate Criteria Queries

From SONIVIS:Wiki

This article is work in progress.

Hibernate provides with the Criteria Queries a way to programmatically query the persistence layer. This wrapper creates the illusion to query persistable classes and work with their objects without knowing anything about the underlying structure of the database layout.

1 Obtaining a Criteria instance
2 Narrowing the result set - or: programmatic WHERE clause
- 2.1 Restrictions API
3 Ordering the result set
4 Limiting the size of the result set
5 Including associated entities
6 Dynamic fetching of associations
7 Result transformation
- 7.1 Getting more than one entity
- 7.2 Distinct entities
8 Projections
- 8.1 Projections API
9 DetachedCriteria
10 Links

Obtaining a `Criteria` instance

To get hold of a Criteria instance is straight forward - as long as you have session at hand. The following code snippet shows the creation of a Criteria instance that operates on the Actor class of the SONIVIS:Data Model. See code line 08.

import java.util.list;
import org.hibernate.Session;
import org.Hibernate.Transaction;
import org.hibernate.Criteria;
...

01  Session session = ModelManager.getInstance().getCurrentSession();
02  Transaction tx = null;
03  List<Actor> actorList = null;
04
05  try {
06    tx = session.beginTransaction();
07
08    Criteria criteria = session.createCriteria(Actor.class);
09    actorList = criteria.list();
10    
11    // do some work
12
13    tx.commit();
14  } catch (HibernateException he) {
15    tx.rollback()
16
17    // error handling
18  }

In line 09 one can see how to execute a Criteria query. (Actually, the query will be executed with the surrounding transaction tx being committed in line 13 unless session.flush() is called before.) That line also shows that (usually) the call to Criteria.list() will produce a List as result that is of the same type the Criteria instance was created with - in this case of type Actor.

In the following the result obtained from an executed query will be referred to by a standard database term: result set. So for now, the content of actorList is the current result set.

There are four methods to get the result set:

list() - returns a java.util.List
scroll() - returns a org.hibernate.ScrollableResults
scroll(ScrollMode scrollMode) - returns a org.hibernate.ScrollableResult
uniqueResult() - returns a java.lang.Object. Will throw an exception if the result is not unique.

org.hibernate.ScrollableResults can be worked with like an iterator with extended functionality, i.e. for navigating the result set.

Narrowing the result set - or: programmatic `WHERE` clause

In SQL the WHERE clause is used to pose restrictions on the queried table(s) so that the result set is a subset of the overall content of this/these tabel(s). The methods within the Criteria API that restrict the result set are named appropriately: Restrictions.

The following piece of code restricts the list of Actors to those whose name is starting with an A.

01  Session session = ModelManager.getInstance().getCurrentSession();
02  Transaction tx = null;
03  List<Actor> actorList = null;
04
05  try {
06    tx = session.beginTransaction();
07
08    Criteria criteria = session.createCriteria(Actor.class);
09    criteria.add( Restrictions.like("name", "A%") );
10    actorList = criteria.list();
11
12    tx.commit();
13  } catch (HibernateException he) {
14    tx.rollback()
15    // error handling
16  }

There is a nice issue related to the Criteria API: Most of its methods (22 out of 27) return the (modified) Criteria instance the method was called upon. Therefore you can create (almost) endless chains of method calls, i.e. rewriting the above code as follows.

01  Session session = ModelManager.getInstance().getCurrentSession();
02  Transaction tx = null;
03  List<Actor> actorList = null;
04
05  try {
06    tx = session.beginTransaction();
07
08    actorList = session.createCriteria(Actor.class)
09               .add( Restrictions.like("name", "A%") )
10               .list();
11    
13    tx.commit();
14  } catch (HibernateException he) {
15    tx.rollback()
16    // error handling
17  }

The syntax is pretty close to SQL and thus makes it more or less easy for database as well as Java programmers to get acquainted with it. In terms of SQL the query set up before would look like this (when expecting an actor table corresponding to an Actor class).

SELECT * FROM actor WHERE name LIKE 'A%';

An alternative way to narrow the result set is to use a Property instance. So, the following piece of code essentially does the same as the previous ones, only line 09 has changed.

01  Session session = ModelManager.getInstance().getCurrentSession();
02  Transaction tx = null;
03  List<Actor> actorList = null;
04
05  try {
06    tx = session.beginTransaction();
07
08    actorList = session.createCriteria(Actor.class)
09               .add( Property.forName("name").like("A%") );
10               .list();
11
12    tx.commit();
13  } catch (HibernateException he) {
14    tx.rollback()
15    // error handling
16  }

`Restrictions` API

The following table presents the rich set of static methods of the org.hibernate.criterion.Restrictions API.

org.hibernate.criterion.Restrictions API (lexicographical order of method names)
Returns	Method	Comment
`Criterion`	`allEq(Map propertyNameValues)`	Applies equality constraints on all entries of the specified map. The map keys are the property names its values the values to match against.
`LogicalExpression`	`and(Criterion lhs, Criterion rhs)`	Logical conjunction of two `Criterion` instances. If the requirement is that two restrictions must be `true` this method can be used for their conjunction. I.e., and( isNotNull("property"), ge("property", new Integer(555)); One may also nest multiple calls to this method (theoretically indefinitely): and( and( isNotNull("prop1"), ge("prop1", new Integer(78) ), like("name", "Chap%") );
`Criterion`	`between(String propertyName, Object lo, Object hi)`	Include all entities in result set that have the value of the property `propertyName` between `lo` and `hi`. The method works inclusively referring to the specified `lo` and `hi` values. I.e., if you check an `int` field with values `Integer(10)` and `Integer(50)` entities matching one of these values exactly, will be included in the result set. The `lo` and `hi` values can be used with numeric instances but also, for example, with `Date` objects. So the following code snippet covers all `Actor`s that registered within 2006. // imports and other stuff left out DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); Date start = (Date) format.parse("2006-01-01 00:00:00"); Date end = (Date) format.parse("2006-12-31 23:59:59"); // session and transaction setup left out session.createCriteria(Actor.class) .add( Restrictions.between("registered", start, end) ).list(); // commiting transaction, exception handling left out
`Conjunction`	`conjunction()`	Group several expressions into one conjunction. The use of a `Conjunction` should be preferred over the use of `and(Criterion lhs, Criterion rhs)` if several restrictions are required to be `true` at the same time. // session and transaction setup left out session.createCriteria(ContentElement.class) .add( Restrictions.conjunction() .add( Restrictions.eq("namespaceId", new Integer(0)) ) .add( Restrictions.like("name", "Chap%" ) .add( Restrictions.gt("externalId", new Integer(100))) .add( Restrictions.isNotNull("created") ) ) .list(); // commiting transaction, exception handling left out
`Disjunction`	`disjunction()`	Group several expressions into one disjunction. If several restrictions are to be `or`-ed together using this method is preferred over nesting a bunch of `or(Criterion lhs, Criterion rhs)` statements. // session and transaction setup left out session.createCriteria(ContentElement.class) .add( Restrictions.isNotNull("namespaceId") ) .add( Restrictions.disjunction() .add( Restrictions.eq("namespaceId", new Integer(0)) ) .add( Restrictions.eq("namespaceId", new Integer(10) ) .add( Restrictions.eq("namespaceId", new Integer(14) ) .add( Restrictions.ge("namespaceId", new Integer(100) ) ) .list(); // commiting transaction, exception handling left out
`SimpleExpression`	`eq(String propertyName, Object value)`	Restrict the result set to entities having the value `value` set for the property `propertyName`. Do not use this method on the entity's identifier property. In that case use `idEq(Object value)` instead. For a code example refer to comment on `ge(String propertyName, Object value)`.
`PropertyExpression`	`eqProperty(String propertyName, String otherPropertyName)`	Restrict the result set to entities having equal values set for the properties `propertyName` and `otherPropertyName`. For a code example refer to comment on `geProperty(String propertyName, String otherPropertyName)`.
`SimpleExpression`	`ge(String propertyName, Object value)`	Restrict the result set to entities having a value greater than or equal to the `value` set for the property `propertyName`. The following piece of code will retrieve all `ContentElement` entities with a `namespaceId` greater than or equal to 100. // session and transaction setup left out session.createCriteria(ContentElement.class) .add( Restrictions.ge("namespaceId", new Integer(100) ) .list(); // commiting transaction, exception handling left out The methods `eq(String propertyName, Object value)`, `gt(String propertyName, Object value)`, `le(String propertyName, Object value)`, `lt(String propertyName, Object value)` , and `ne(String propertyName, Object value)` work similar.
`PropertyExpression`	`geProperty(String propertyName, String otherPropertyName)`	Restrict the result set to entities having the value set for the property `propertyName` greater than or equal to the value set for property `otherPropertyName`. The following code fragment finds all `ContentElement` entities having the value of the field `externalId` greater than or equal to the value of `namespaceId` . // session and transaction setup left out session.createCriteria(ContentElement.class) .add( Restrictions.geProperty( "externalId", "namespaceId" ) .list(); // commiting transaction, exception handling left out The methods `eqProperty(String propertyName, String otherPropertyName)`, `gtProperty(String propertyName, String otherPropertyName)`, `leProperty(String propertyName, String otherPropertyName)`, `ltProperty(String propertyName, String otherPropertyName)`, and `neProperty(String propertyName, String otherPropertyName)` work similar.
`SimpleExpression`	`gt(String propertyName, Object value)`	Restrict the result set to entities having a value greater than the `value` set for the property `propertyName`. For a code example refer to comment on `ge(String propertyName, Object value)`.
`PropertyExpression`	`gtProperty(String propertyName, String otherPropertyName)`	Restrict the result set to entities having the value set for the property `propertyName` greater than the value set for property `otherPropertyName`. For a code example refer to comment on `geProperty(String propertyName, String otherPropertyName)`.
`Criterion`	`idEq(Object value)`	Equality operation for the identifier property. If there is a need to compare the entities identifier property (often a surrogate key) use this method instead of `eq(String propertyName, Object value)`.
`Criterion`	`ilike(String propertyName, Object value)`	Case-insensitive `LIKE` operator. Restricts the result set to entities having a value for property `propertyName` that is `LIKE value` .
`Criterion`	`ilike(String propertyName, Object value, MatchMode matchMode)`	Case-insensitive `LIKE` operator. Restricts the result set to entities having a value for property `propertyName` that is `LIKE value` . The MatchMode allows to configure how the `LIKE` operator should match. `MatchMode.ANYWHERE` matches the pattern anywhere in the string, `MatchMode.END` at the end of the string, `MatchMode.START` at the beginning of the string, and `MatchMode.EXACT` checks for an entire match.
`Criterion`	`in(String propertyName, Collection values)`	Restricts the result set to entities having a value for property `propertyName` that is contained in the `values` collection. The same as the code example for `disjunction()` achieves the following: // imports left out Collection<Integer> list = new ArrayList<Integer>(); list.add(new Integer(0)); list.add(new Integer(10)); list.add(new Integer(14)); // session and transaction setup left out session.createCriteria(ContentElement.class) .add( Restrictions.isNotNull("namespaceId") ) .or( in("namespaceId", list), Restrictions.ge("namespaceId", new Integer(100) ) ) .list(); // commiting transaction, exception handling left out
`Criterion`	`in(String propertyName, Object[] values)`	Restricts the result set to entities having a value for property `propertyName` that is contained in the `values` array.
`Criterion`	`isEmpty(String propertyName)`	Checks if a collection valued property named `propertyName` is empty.
`Criterion`	`isNotEmpty(String propertyName)`	Checks if a collection valued property named `propertyName` is not empty.
`Criterion`	`isNotnull(String propertyName)`	Checks if the property `propertyName` is not `null`.
`Criterion`	`isNull(String propertyName)`	Checks if the property `propertyName` is `null`.
`SimpleExpression`	`le(String propertyName, Object value)`	Restrict the result set to entities having a value less than or equal to the `value` set for the property `propertyName`. For a code example refer to comment on `ge(String propertyName, Object value)`.
`PropertyExpression`	`leProperty(String propertyName, String otherPropertyName)`	Restrict the result set to entities having the value set for the property `propertyName` less than or equal to the value set for property `otherPropertyName`. For a code example refer to comment on `geProperty(String propertyName, String otherPropertyName)`.
`SimpleExpression`	`like(String propertyName, Object value)`	Restricts the result set to entities having a value for property `propertyName` that is `LIKE value`.
`SimpleExpression`	`like(String propertyName, Object value, MatchMode matchMode)`	Restricts the result set to entities having a value for property `propertyName` that is `LIKE value`. The MatchMode allows to configure how the `LIKE` operator should match. `MatchMode.ANYWHERE` matches the pattern anywhere in the string, `MatchMode.END` at the end of the string, `MatchMode.START` at the beginning of the string, and `MatchMode.EXACT` checks for an entire match.
`SimpleExpression`	`lt(String propertyName, Object value)`	Restrict the result set to entities having a value less than the `value` set for the property `propertyName`. For a code example refer to comment on `ge(String propertyName, Object value)`.
`PropertyExpression`	`ltProperty(String propertyName, String otherPropertyName)`	Restrict the result set to entities having the value set for the property `propertyName` less than the value set for property `otherPropertyName`. For a code example refer to comment on `geProperty(String propertyName, String otherPropertyName)`.
`NaturalIdentifier`	`naturalId()`
`SimpleExpression`	`ne(String propertyName, Object value)`	Restrict the result set to entities having a value not equal to the `value` set for the property `propertyName`.
`PropertyExpression`	`neProperty(String propertyName, String otherPropertyName)`	Restrict the result set to entities having the value set for the property `propertyName` not' equal to the value set for property `otherPropertyName`.
`Criterion`	`not(Criterion expression)`	Negate the result of `expression`.
`LogicalExpression`	`or(Criterion lhs, Criterion rhs)`	Logical disjunction of two `Criterion` instances. If the requirement is that at least one of two restrictions must be `true` this method can be used for their disjunction. I.e., or( isNotNull("property"), ge("property", new Integer(555)); One may also nest multiple calls to this method (theoretically indefinitely): or( or( isNotNull("prop1"), ge("prop1", new Integer(78) ), like("name", "Chap%") );
`Criterion`	`sizeEq(String propertyName, int size)`	Checks if a collection valued property named `propertyName` has size `size`.
`Criterion`	`sizeGe(String propertyName, int size)`	Checks if a collection valued property named `propertyName` has size `size` or greater.
`Criterion`	`sizeGt(String propertyName, int size)`	Checks if a collection valued property named `propertyName` is of a greater size than `size`.
`Criterion`	`sizeLe(String propertyName, int size)`	Checks if a collection valued property named `propertyName` has size `size` or less.
`Criterion`	`sizeGt(String propertyName, int size)`	Checks if a collection valued property named `propertyName` is of a lesser size than `size`.
`Criterion`	`sizeNe(String propertyName, int size)`	Checks if a collection valued property named `propertyName` does not have size `size`.
`Criterion`	`sqlRestriction(String sql)`	Apply a constraint expressed as a SQL fragment. `{alias}` occurrences are being replaced with the appropriate table alias.
`Criterion`	`sqlRestriction(String sql, Object[] values, Type[] types)`	Apply a constraint expressed as a SQL fragment using the JDBC parameters given as `values` and `types`. `{alias}` occurrences are being replaced with the appropriate table alias.
`Criterion`	`sqlRestriction(String sql, Object value, Type type)`	Apply a constraint expressed as a SQL fragment using the JDBC parameter given as `value` and `type`. `{alias}` occurrences are being replaced with the appropriate table alias. The code fragment below finds all `Actor` entities whose `name` field starts with Charl. The comparison is case insensitive. `{alias}` is replaced with the actual table name that is referred to as given in the Hibernate mapping file. Note, the JDBC placeholder ? where the second argument will be used for. The last argument indicates the org.hibernate.type.Type to use for `value`. // imports left out // session and transaction setup left out session.createCriteria(Actor.class) .add( Restrictions.sqlRestriction( "lower({alias}.name) like lower(?)", "Charl%", Hibernate.STRING) ) .list(); // commiting transaction, exception handling left out

All the return value types implement the interface org.hibernate.criterion.Criterion. Furthermore, the package org.hibernate.criterion provides even more useful Criterion implementers.

Ordering the result set

When it comes to ordering Hibernate sticks closely to SQL - no promises pertaining result sets. In case an ordered result set is required the org.hibernate.criterion.Order class should be used. It offers two static methods that expect the name of the property to pose the ordering on.

asc(String propertyName) orders by property propertyName ascendingly
desc(String propertyName) orders by property propertyName descendingly

The code fragment shows how to apply ordering to a Criteria. Note, that the special addOrder(Order order) method beloings to the Criteria and not to some Criterion as, i.e. a Restriction.

actorList = sess.createCriteria(Actor.class)
    .add( Restrictions.ilike("name", "Admi%")
    .addOrder( Order.asc("name") )
    .addOrder( Order.desc("registered") )
    .list();

Limiting the size of the result set

Sometimes not all matching entities of a query a required, at least not at once. The Criteria interface offers methods for result set size limitation.

To get the first 50 ContentElement entities having namespaceId 0 use the following code:

ceList = session.createCriteria(ContentElement.class)
        .add( Restrictions.eq( "namespaceId", new Integer(0) ) )
        .setMaxResults(50)
        .list();

Usually a size limited query makes only sense when adding and ordering.

With the help of another method paging of results is possible.

int firstRowIdx = 0;
int resultSize = 50;

// session and transaction setup left out

while (true) {
  ceList = session.createCriteria(ContentElement.class)
          .add( Restrictions.eq( "structure.structurevalue", new Integer(0) ) )
          .addOrder( Order.asc("name") )
          .setFirstResult(firstRowIdx)
          .setMaxResults(resultSize)
          .list();
  
  if (ceList != null && !ceList.isEmpty() {
    firstRowIdx += resultSize;
  }
  else {
    break;
  }
}

Including associated entities

Associated entities can easily be joined with additional calls to createCriteria().

The next code snippet extends the query on Context entities to InfoSpaceItemType entities.

ctxList = session.createCriteria(Context.class).
         .createCriteria("infoSpaceItemType")
             .add( Restrictions.eq( "name", "WikiLink" ) )
         .list();

The overloaded method createCriteria() provides four signatures.

createCriteria(String associationPath)
createCriteria(String associationPath, int joinType)
createCriteria(String associationPath, String alias)
createCriteria(String associationPath, String alias, int joinType)

It will always create a new Criteria instance.

The associationPath is equal to the name of the field that the associated entity has in the root entity. The joinType defaults to CriteriaSpecification.INNER_JOIN. It can be switched to CriteriaSpecification.LEFT_JOIN or CriteriaSpecification.FULL_JOIN if need be.

In some cases a table alias is necessary to achieve the goal. Imagine you wanted to compare the name field of the Actor with that of the ContentElement entity associated with a Knowledge entity.

klList = session.createCriteria(Knowledge.class).
         .createCriteria("actor", "ac")
         .createCriteria("contentElement", "ce")
         .add( Restrictions.eq( "ac.name", "ce.name" ) )
         .list();

Alternatively use

klList = session.createCriteria(Knowledge.class).
         .createAlias("actor", "ac")
         .createAlias("contentElement", "ce")
         .add( Restrictions.eq( "ac.name", "ce.name" ) )
         .list();

The method comes in two flavours

createAlias(String associationPath, String alias)
createAlias(String associationPath, String alias, int joinType)

and never creates a new Criteria instance.

It is always a good idea to provide an alias for an association. Play it safe, always use it and always include it when referring to a field of the associated entity.

Dynamic fetching of associations

Depending on the chosen fetching strategy in the Hibernate mappings it might be necessary to deal with the fetch mode. In the SONIVIS:Data model most entity associations are fetched lazily. That means that associated entities are returned as a proxy instance and not a fully initialized object.

By setting an alternative fetch mode with setFetchMode(String associationPath, FetchMode mode) you can force the associated entity to be created as you'd expect it.

actorList = session.createCriteria(Actor.class)
        .add( Restrictions.like( "name", "Adm%" ) )
        .setFetchMode( "properties", FetchMode.JOIN )
        .list();

The following values may be used for mode

FetchMode.DEFAULT uses the presets from the mappings (might produce proxies).
FetchMode.JOIN uses an outer join on the association (does not produce proxies).
FetchMode.SELECT uses separate SELECT statement(s) to fetch eagerly (does not produce proxies).

Note, if you eagerly fetch an associated entity don't expect further associated entities of that one to be fetched eagerly, too. They will be fetched as set up in the mapping unless you coose a different mode explicitly.

Result transformation

Getting more than one entity

Unfortunately the Criteria queries sometimes do not quite returned what one would expect at first sight. When retrieving associated entities (one-to-many associations) neither createCriteria() nor createAlias() will lead to filtered result sets. Therefore, a ResultTransformer has to be applied.

The following query produces a list of Maps that have two entries, one mapping an alias for Context to a Context entity and one mapping the given alias ce to a ContentElement entity.

mapList = session.createCriteria(Context.class)
    .createCriteria("target", "ce")
        .add( Restrictions.eq("namespaceId", "0") )
    .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
    .list();

for ( Iterator iterator = mapList.iterator(); iterator.hasNext(); ) {
    Map resultMap = (Map) iterator.next();
    Context ctx = (Context) resultMap.get(CriteriaSpecification.ROOT_ALIAS);
    ContentElement ce = (ContentElement) resultMap.get("ce");
}

Distinct entities

When eagerly fetching associated collections you might not normally end up with distinct entities in the result set. Use a ResultTransformer to achieve the expected behaviour. The following example is a modified fragment from ContentElementDAO. It searches for entities with a given externalId in a given infoSpace. The associated collection of properties eagerly fetched which leads to the same number of rows for each ContentElement as it has properties.

ceList = session.createCriteria(ContentElement.class)
        .setFetchMode("properties", FetchMode.JOIN)
        .add( Restrictions.eq(AbstractGenericDAO.INFOSPACE_FIELD, infoSpace) )
        .add( Restrictions.eq(ContentElementDAO.EXTERNALID_FIELD, externalId) )
	.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
        .list();

The ResultTransformer CriteriaSpecification.DISTINCT_ROOT_ENTITY filters the retrieved result set to contain unique entities pertaining the root entity, that is, ContentElement, only.

Projections

The factory class org.hibernate.criterion.Projections can be used from a Criteria by invoking setProjection(Projection projection).

Projections API

org.hibernate.criterion.Projections API (lexicographical order of method names)
Returns	Method	Comment
`Projection`	`alias(Projection projection, String alias)`	Assign the `alias` to `projection` by wrapping it. The returned `Projection` is the same as `projection` but has an alias name now. An alias becomes necessary if the `Projection<code> is to be used in a <code>Restricton` is the following code snippet demonstrates ceList = session.createCriteria(ContentElement.class) .setProjection( Projections.alias( Projections.groupProperty("namespace"), "ns" ) ) .addOrder( Order.asc("ns") ) .list(); An alternative way to achieve this is ceList = session.createCriteria(ContentElementt.class) .setProjection( Projections.groupProperty("namespace").as("ns") ) .addOrder( Order.asc("ns") ) .list();
`AggregateProjection`	`avg(String propertyName)`	Calculates the average value of `propertyName`. Here some, actually not very realistic, code example. ceList = session.createCriteria(ContentElement.class) .setProjection( Projections.avg("externalId") ) .list(); The values to be taken into account may be restricted before ceList = session.createCriteria(ContentElement.class) .setProjection( Projections.avg("externalId") ) .add( Restrictions.isNotNull("externalId") ) .add( Restrictions.ge( "externalId", new Integer(1000) ) .list(); Other aggregation projections are `max(String propertyName)` `min(String propertyName)` `sum(String propertyName)` There are also count projections `count(String propertyName)` `countDistinct(String propertyName)` They all work basically the same. Note, that in these cases no explicit grouping is necessary
`AggregateProjection`	`max(String propertyName)`	Finds the maximum value of `propertyName`. For a code example see `avg(String propertyName)`.
`CountProjection`	`count(String propertyName)`	Counts the items of `propertyName`. For a code example see `avg(String propertyName)`.
`CountProjection`	`countDistinct(String propertyName)`	Counts distinct items of `propertyName`. For a code example see `avg(String propertyName)`.
`Projection`	`distinct(Projection projection)`	Create a distinct `Projection` from `projection`.
`PropertyProjection`	`groupProperty(String propertyName)`	Apply a result grouping by `propertyName`.
`IdentityProjection`	`id()`	Turn an identifier value into a `Projection`.
`AggregateProjection`	`min(String propertyName)`	Calculates the average value of `propertyName`. For a code example see `avg(String propertyName)`.
`ProjectionList`	`projectionList()`	Create a new `ProjectionList`. This is useful to query for several `Projection`s. ceList = session.createCriteria(ContentElement.class) .setProjection( Projections.projectionList() .add( Projections.rowCount() ) .add( Projections.avg("externalId") ) .add( Projections.max("externalId") ) .add( Projections.countDistinct("namespaceId") ) ) .list();
`PropertyProjection`	`property()`	Turn a property into a `Projection`.
`Projection`	`rowCount()`	Count rows.
`Projection`	`sqlGroupProjection(String sql, String groupBy, String[] columnAliases, Type[] types)`	Same as `sqlProjection(String sql, String[] columnAliases, Type[] types)`, except that `groupBy` specifies an SQL `GROUP BY` clause fragment.
`Projection`	`sqlProjection(String sql, String[] columnAliases, Type[] types)`	The `columnAliases` array holds the aliases for the columns selected in the SQL `SELECT` clause fragment given in `sql`. The `types` array the appropriate Hibernate types for these aliases. Keep an eye on the order of the arguments in `sql`, `columnAliases`, and `types`.
`AggregateProjection`	`sum(String propertyName)`	Calculates the sum of `propertyName` For a code example see `avg(String propertyName)`.

Projections can also be obtained from the static method Property.forName(String propertyName).

ceList = session.createCriteria(ContentElement.class)
    .setProjection( Property.forName("name") )
    .add( Property.forName("namespaceId").eq(new Integer(14)) )
    .list();

See the org.hibernate.criterion.Property API documentation for details what else is there.

`DetachedCriteria`

As presented earlier, a Criteria query is connected to a org.hibernate.Session instance. It is obtained by a call to Session.createCriteria(...).

The class DetachedCriteria allows for Criteria query creation independent of a Session. Only when it is to be executed it has to be attached to a Session object.

DetachedCriteria query = DetachedCriteria.forClass(ContentElement.class)
    .add( Property.forName("namespace").eq("Category") );

// do some work

Session session = ModelManager.getInstance().getCurrentSession();
Transaction tx = null;
List<ContentElement> ceList = null;

try {
  tx = session.beginTransaction();
  ceList = query.getExecutableCriteria(session).list();
  tx.commit();
} catch (HibernateException he) {
  tx.rollback()

  // error handling
}

Another feature of DetachedCriteria is the use as subquery.

DetachedCriteria avgSize = DetachedCriteria.forClass(InfoSpaceItemProperty.class)
    .add( Restrictions.eq("name", "size") )
    .setProjection( Property.forName("value").avg() );

session.createCriteria(InfoSpaceItemProperty.class)
    .add( Restrictions.eq("name", "size") )
    .add( Property.forName("value").gt(avgSize) )
    .list();

Links

Official Documentation on Hibernate Criteria Queries
Hibernate 3 Tutorial by RoseIndia with a lot of code examples. Criteria queries tutorial starts at section 22.

HowTo use Hibernate Criteria Queries

From SONIVIS:Wiki

Contents

Obtaining a `Criteria` instance

Narrowing the result set - or: programmatic `WHERE` clause

`Restrictions` API

Ordering the result set

Limiting the size of the result set

Including associated entities

Dynamic fetching of associations

Result transformation

Getting more than one entity

Distinct entities

Projections

Projections API

`DetachedCriteria`

Links

Views

Personal tools

Navigation

portals

website

Search

Toolbox

HowTo use Hibernate Criteria Queries

From SONIVIS:Wiki

Contents

Obtaining a Criteria instance

Narrowing the result set - or: programmatic WHERE clause

Restrictions API

Ordering the result set

Limiting the size of the result set

Including associated entities

Dynamic fetching of associations

Result transformation

Getting more than one entity

Distinct entities

Projections

Projections API

DetachedCriteria

Links

Views

Personal tools

Navigation

portals

website

Search

Toolbox

Obtaining a `Criteria` instance

Narrowing the result set - or: programmatic `WHERE` clause

`Restrictions` API

`DetachedCriteria`