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.
List results = session.createCriteria(JavaClub3.class).list(); for (Iterator iter = results.iterator(); iter.hasNext();) { JavaClub3 club3 = (JavaClub3) iter.next(); log.debug(club3); }
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);
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.
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); }
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); }
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]); }
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]); }
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()); }
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); }
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);
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); }
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); }
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();
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.