Paging with ordering

Ordering with examples in the previous post won’t work as expected.
#1. If we include .addOrder(Order.desc(“createDate”)) to the first query, it will be illegal. Because order by clause should also be present in the results.
#2. If we include .addOrder(Order.desc(“createDate”)) to the second query, it will only sort within one page.

Solution:

List<Object[]> results = (List<Object[]>) session.createCriteria(LessonPlan.class)
.add(Restrictions.eq("individualId", individualId))
.setFirstResult(firstResult).setMaxResults(pageSize)
.addOrder(Order.desc("createDate"))
.setProjection(Projections.distinct(Projections.projectionList()
.add(Projections.id())
.add(Projections.property("createDate"))))
.list();

List<Long> resultIds = new ArrayList<Long>();
for(Object[] objArray : results){
 resultIds.add((Long)objArray[0]);
}

List<LessonPlan> result = new ArrayList<LessonPlan>();
if(resultIds.size() > 0){
result = (List<LessonPlan>) session.createCriteria(LessonPlan.class)
.add(Restrictions.in("id", resultIds))
.addOrder(Order.desc("createDate"))
.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
.list();
}
return result;

Notes:
– all items in bold above are important
– so now we got list of object arrays from the first query
– we need to get only IDs from that list
second list also need to have .addOrder specified, otherwise items within a single page won’t be sorted.

More here: http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html

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!

Let’s say Content entity contains Label entities (as “labels”), the relationship might be one-to-many or many-to-many.
In this case the code below won’t work:

session.createCriteria(Content.class)
.add(Restrictions.eq("labels.labelId", labelId))
.list();

It should be as follows:

session.createCriteria(Content.class)
.createAlias("labels", "x")
.add(Restrictions.eq("x.labelId", labelId))
.list();

OR

session.createCriteria(Content.class)
.createCriteria("labels")
.add(Restrictions.eq("labelId", labelId))
.list();

NOTE:
In the second statement above, if we try to add one more clause that belong to Content class, we should be careful. It should be added after createCriteria(Content.class) statment and before createCriteria(“labels”). This is because if we add if after, it will belong to “labels” object.

JOIN vs JOIN FETCH

Consider the query below:

List<Individual> individuals = session.createQuery("select i from Individual i left outer join fetch i.roles " +
" left outer join fetch i.credentialUPs " +
" join i.properties p " +
" where p.individualIdPropertyName.name = :emailType and p.value = :emailAddress")
.setParameter("emailType", "EMAIL_ADDRESS")
.setParameter("emailAddress", emailAddress)
.list();

This will select “credentialUP” and “roles” during the query. Interesting thing happens with “properties” association. The query will select an entity according to the where statement provided, however “properties” won’t be available by default.
To make Hibernate load all “properties” of an object we’ll need to call those properties explicitly within the current session.

Ex:

int size = individuals.size();
if (size == 1) {
 Individual result = individuals.get(0);
 result.getProperties().isEmpty();
 return result;
}

This will result in Hibernate making additional “select” calls to fetch a required associations.

Now consider doing call like this:

List<Individual> individuals = session.createQuery("select i from Individual i left outer join fetch i.roles " +
" left outer join fetch i.credentialUPs " +
" join fetch i.properties p " +
" where p.individualIdPropertyName.name = :emailType and p.value = :emailAddress")
.setParameter("emailType", "EMAIL_ADDRESS")
.setParameter("emailAddress", emailAddress)
.list();

Here, as we’ve explicitly specified that we want to “join fetch” this association, it will not do additional selects. We will however get only one “property” entity, the one that matches our “where” condition (even though there might be many more associated “properties”). Also, as expected, it doesn’t matter if we do or don’t call the “isEmpty” within the session. It also doesn’t matter if we do “left outer join” or a regular “join”