Criteria Queries

A criteria query can include multiple criterias. Each criteria can include multiple criterions, which can include other criterions as well. See the following figure to understand the structure.

Simple select

List results = session.createCriteria(JavaClub3.class).list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   JavaClub3 club3 = (JavaClub3) iter.next();
   log.debug(club3);
}

Select with a unique result

If you expect a unique result, you can call uniqueResult instead of list to get one object only.

JavaClub3 aClub = (JavaClub3) session.createCriteria(JavaClub3.class)
   .add(Restrictions.eq("id", 5))
   .uniqueResult();
log.debug("one single club: " + aClub);

Select with join returning objects multiple times

If you fetch the members of a JavaClub3 you will receive multiple entries for a JavaClub3 if it has more than one member.

log.debug("Selecting with fetching does not result in an array of objects");
results = session.createCriteria(JavaClub3.class)
   .setFetchMode("members", FetchMode.JOIN)
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   JavaClub3 javaClub3 = (JavaClub3) iter.next();
   log.debug("Club:  " + javaClub3);
}

If you only want to initialize the members you can use a result transformer.

Select with join returning distinct objects

The former sample explained that a join can result into multiple lines for an object. In the HQL samples we used an approach like

Set set = new HashSet(getSession().createQuery(
   "select i from Invoice i inner join fetch i.orders").list());

The main disadvantage is that we loose any kind of ordering. Criteria queries provide an alternative: a ResultTransformer which keeps the ordering as well.

log.debug("Select with fetching with a result transformer");
results = session.createCriteria(JavaClub3.class)
   .addOrder(Order.desc("name"))
   .setFetchMode("members", FetchMode.JOIN)
   .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   JavaClub3 javaClub3 = (JavaClub3) iter.next();
   log.debug("Club:  " + javaClub3);
}

Select with a where condition on a related object

Simply add a further criteria for the related object. In this case we select all clubs with a member named Peter.

log.debug("Selecting an object with a where condition on a related object");
results = session.createCriteria(JavaClub3.class).createCriteria("members")
      .add(Restrictions.eq("name", "Peter")).list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   JavaClub3 javaClub3 = (JavaClub3) iter.next();
   log.debug("Club:  " + javaClub3);
}

Selecting a single column (scalar values, projections)

Simple types of Integer, String are called scalar values. You can select them explicitly.

results = session.createCriteria(JavaClub3.class).setProjection(
   Projections.projectionList()
      .add(Projections.property("id"))
      .add(Projections.property("name")))
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   Object object[] = (Object[]) iter.next();

   log.debug("Club Id:  " + object[0] + " name: " + object[1]);
}

Selecting multiple columns (scalar values, projections)

When you select multiple scalar you will get an array of objects.

results = session.createQuery("select c.id, c.name from JavaClub3 c")
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   Object object[] = (Object[]) iter.next();
   log.debug("Club Id:  "+object[0]+ " name: "+object[1]);
}

Selecting selective properties of a class

If you have a class with a lot of properties but need only a few of them you can create an object with selective properties. This is only useful when reading data. You can not persist such an object. With criteria queries you use a transformer to create the object.

results = session.createCriteria(JavaClub3.class)
   .setProjection(Projections.property("name"))
   .setResultTransformer(
      new AliasToBeanResultTransformer(JavaClub3.class))
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   JavaClub3 element = (JavaClub3) iter.next();
   log.debug(element.getName());
}

Walking through relations

Once your application becomes more complex, you will have to walk through deeper relations. The following picture shows a room having multiple cupboards with one lock each with one key each.

images:images/c_queries_walking_relations.jpg[]

You can easily walk through 1:1 and 1:n relations using criteria queries:

results = session.createCriteria(Cupboard.class).createCriteria("lock")
   .createCriteria("key")
   .add(Restrictions.eq("name", "old key"))
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   Cupboard cupboard = (Cupboard) iter.next();
   log.debug(cupboard);
}
results = session.createCriteria(Room.class).createCriteria("cupboards")
   .createCriteria("lock").createCriteria("key")
   .add(Restrictions.eq("name", "old key"))
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   Room room = (Room) iter.next();
   log.debug(room);
}

Simple where condition

The result set is a single object, so we return it with uniqueResult().

JavaClub3 aClub = (JavaClub3) session.createCriteria(JavaClub3.class)
   .add(Restrictions.eq("id", 5))
   .uniqueResult();
log.debug("one single club: " + aClub);

Where condition in relation

We need to create a second criteria for the property.

List results = session.createCriteria(JavaClub3.class)
   .createCriteria("members")
   .add(Restrictions.eq("name", "Peter"))
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   JavaClub3 club3 = (JavaClub3) iter.next();
   log.debug("Club with member named Peter: " + club3);
}

Where with or condition

We will select all clubs with id > than 5 or with a name \ starting with Java where there is a member named Peter.

Hint: disjunction is the same as or in HQL

// TODO when you use Java 1.4 or older you must replace the number 5
results = session.createCriteria(JavaClub3.class)
   .add(Restrictions.disjunction()
      .add(Restrictions.eq("name", "Java%"))
      .add(Restrictions.gt("id", 5)))
   .createCriteria("members")
      .add(Restrictions.eq("name", "Peter"))
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   JavaClub3 club3 = (JavaClub3) iter.next();
   log.debug("Club with member named Peter: " + club3);
}

Conditions on collections

If you want to select all authors having written more than 2 books, you can use the size condition on a collection.

List<Author> authors = session.createCriteria(Author.class)
   .add(Restrictions.sizeGt("books", 2)).list();
for (Author author : authors) {
    log.info(author);
}

To select entities with empty collections, you may use empty.

List<Author> authors = session.createCriteria(Author.class)
   .add(Restrictions.isEmpty("books")).list();

All, In, Some, Any elements queries

These element can be used to compare a field to values of a subselect. The following samples will explain the usage.

This query selects all pools not being in Frankfurt which size is at least bigger than one pool in Frankfurt. Some is a synonym for Any.

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(
      SwimmingPool.class).setProjection(Projections.property("size"))
   .add(Restrictions.eq("city", "Frankfurt"));
pools = session.createCriteria(SwimmingPool.class)
   .add(Restrictions.ne("city", "Frankfurt"))
   .add(Property.forName("size").gtSome(detachedCriteria))
   .list();
for (Iterator iter = pools.iterator(); iter.hasNext();)
{
   SwimmingPool pool = (SwimmingPool) iter.next();
   log.debug(pool);
}

This query selects all pools not being in Frankfurt which size is bigger than all pools in Frankfurt.

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(
      SwimmingPool.class).setProjection(Projections.property("size")).add(
      Restrictions.eq("city", "Frankfurt"));
pools = session.createCriteria(SwimmingPool.class).add(
      Restrictions.ne("city", "Frankfurt")).add(
      Property.forName("size").gtAll(detachedCriteria)).list();
for (Iterator iter = pools.iterator(); iter.hasNext();)
{
   SwimmingPool pool = (SwimmingPool) iter.next();
   log.debug(pool);
}

This query selects all pools being the same size as a pool in Frankfurt.

detachedCriteria = DetachedCriteria.forClass(SwimmingPool.class)
   .setProjection(Projections.property("size"))
      .add(Restrictions.eq("city", "Frankfurt"));
pools = session.createCriteria(SwimmingPool.class)
   .add(Restrictions.ne("city", "Frankfurt"))
   .add(Property.forName("size").in(detachedCriteria))
   .list();
for (Iterator iter = pools.iterator(); iter.hasNext();)
{
   SwimmingPool pool = (SwimmingPool) iter.next();
   log.debug(pool);
}

Finally, we select all PoolOwner having a swimming pool outside of Frankfurt which size is not greater than 20.

detachedCriteria = DetachedCriteria.forClass(SwimmingPool.class)
   .add(Restrictions.gt("size", 20))
   .add(Restrictions.ne("city", "Frankfurt"))
   .setProjection(Projections.property("id"));
pools = session.createCriteria(PoolOwner.class).createAlias("swimmingPools", "s")
   .add(Subqueries.propertyIn("s.id", detachedCriteria))
   .list();
for (Iterator iter = pools.iterator(); iter.hasNext();)
{
   PoolOwner pool = (PoolOwner) iter.next();
   log.debug(pool);
}

Important: Frequently, it is better to use a projection in subqueries to select an id, instead of an object like SwimmingPools. I found that working on objects does not work as expected in some situations.