Case: fetching paged lists from a single Entity (that has related mapped entities, ex: one-to-many)

Problem:
This query doesn’t work as needed

session.createCriteria(SavedContentArticle.class)
.add(Restrictions.eq("individualId", individualId))
.setFirstResult(firstResult).setMaxResults(pageSize)
.list();

The query above results in the following:
NOTE: saved_content_label is eagerly loaded (specified in entity definition class)

select
blah blah
from
saved_content_article this_
left outer join article articlebas2_ on this_.article_id=articlebas2_.article_id
left outer join saved_content_article_saved_content_label labels3_ on this_.saved_content_id=labels3_.saved_content_id
left outer join saved_content_label contentlab4_ on labels3_.label_id=contentlab4_.label_id
where
this_.individual_id=? limit ? offset ?

Which obviously is wrong. It is wrong because Hibernate groups distinct entities after the query (which will return results smaller than the “pageSize” for each requested page)
Good discussion here: http://stackoverflow.com/questions/2183617/criteria-api-returns-a-too-small-resultset

Solution (the only solution as of right now):
Note: I don’t consider selecting an entire table and then paging it in the code a solution.

Create 2 separate queries:

List<Long> resultIds = (List<Long>) session.createCriteria(SavedContentArticle.class)
.add(Restrictions.eq("individualId", individualId))
.setFirstResult(firstResult).setMaxResults(pageSize)
.setProjection(Projections.distinct(Projections.id()))
.list();

return (List<SavedContentArticle>) session.createCriteria(SavedContentArticle.class)
.add(Restrictions.in("savedContentId", resultIds))
.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
.list();

NOTE: line: setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY) is required!

• Databases
• Java