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

• Databases
• Java