Avri Blog

Let's say you have an Object TheList, which has a list of Company object: companies, and you want to paginate the companies of a specific list, using the criteria API. A company is composed of an Id, and a List of urls (String).

The first solution I think about is the following :

final Criteria rootCriteria = session.createCriteria(TheList.class, "t");
	    final Criteria companiesCriteria = rootCriteria.createCriteria("t.companies");
	    companiesCriteria.setFirstResult(firstResult).setMaxResults(maxResult);
	    rootCriteria.add(Restrictions.eq("t.id", listId)).add(Restrictions.eq("t.owner.id", ownerId));
	    return rootCriteria.list();

This will return a list of "maxResult" of TheList objects.
Unfortunately, you can't get just the list of companies, hibernate's criteria api can't handle this next query:

final Criteria rootCriteria = session.createCriteria(TheList.class, "t");
	    final Criteria companiesCriteria = rootCriteria.createCriteria("t.companies");
	    companiesCriteria.setFirstResult(firstResult).setMaxResults(maxResult);
	    rootCriteria.add(Restrictions.eq("t.id", listId)).add(Restrictions.eq("t.owner.id", ownerId));
	    rootCriteria.setProjection(Projections.property("t.companies"));
	    return rootCriteria.list();

you will have a nice error :

java.lang.ArrayIndexOutOfBoundsException: 0
	at org.hibernate.loader.criteria.CriteriaLoader.getResultColumnOrRow(CriteriaLoader.java:148)
...

So we can stick to the first solution, and add a built-in transformer:

...
rootCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

But this doesn't fit our need, since the limit is applied on the TheList entity, and not on the companies. Useless...

The only solution I found was to create a subQuery to select the companies ids, and another main request where we select the companies, based on the id of the first request.
This looks like as following:

final Criteria rootCriteria = session.createCriteria(Company.class, "c").setMaxResults(maxResult).setFirstResult(firstResult) ;
	    final DetachedCriteria subQuery = DetachedCriteria.forClass(Target.class, "t") //
		    .add(Restrictions.eq("t.id", targetId)).add(Restrictions.eq("t.owner.id", ownerId)) //
		    .createAlias("t.companies", "cp") //
		    .setProjection(Projections.property("cp.id"));
	    final List<Company> companies = rootCriteria.add(Subqueries.propertyIn("c.id", subQuery)).list(); //"exists" may be a better solution than "in".

The main concern about this solution is that you can't set the setMaxResult/setFirstResult on a DetachedCriteria, and a Subqueries needs to be a DetachedCriteria. Stupid: the subquery will return all the companies, and we only need a few of them...
We could also make 2 separated queries : get the ids on the first one, and the companies on the second one.

Now that you have a list of company, you will probably need to fetch the list of urls contained in each company, because you want to display them on your web page.

final Criteria rootCriteria = session.createCriteria(Company.class, "c").setMaxResults(maxResult).setFirstResult(firstResult) //
		    .setFetchMode("c.urls", FetchMode.JOIN) ;
	    final DetachedCriteria subQuery = DetachedCriteria.forClass(Target.class, "t") //
		    .add(Restrictions.eq("t.id", targetId)).add(Restrictions.eq("t.owner.id", ownerId)) //
		    .createAlias("t.companies", "cp") //
		    .setProjection(Projections.property("cp.id"));
	    final List<Company> companies = rootCriteria.add(Subqueries.propertyIn("c.id", subQuery)).list(); //"exists" may be a better solution than "in".

Damn ! this doesn't work either ! Not yet implemented with hibernate + criteria, only hql... (fell free to send a patch...) https://hibernate.onjira.com//browse/HHH-869

org.hibernate.MappingException: collection was not an association: com.model.company.Company.urls
...

So the only thing we can do is to create an extra query to fetch all the urls, and map the urls to the companies.
Adding urls to a company in the session will result to an extra update, so I decided to evict the companies from my session (I'm in read-only)
Enjoy !

final List<Long> ids = new ArrayList<Long>();
	    for (final Company c : companies) {
		ids.add(c.getId());
		session.evict(c);
	    }
	    final List<Company> cs = session.createQuery("select c from Company c left join fetch c.urls where id IN (:companiesId)").setParameterList("companiesId", ids).list();

Ok, that's just too painfull.
Let's first get the ids, and then use hql to do the fetch join I need. I'll use a StringBuilder to build dynamically the extra criteria of my request...

Conclusion: Criteria api has some features missing...