Hibernate Criteria ordering and pagination not working

Question

I see a weird behavior when using criteria.addOrder(Order.asc("sortAttribute")) for sorting on non-unique attribute. The results returned are duplicate.

The same seems to work fine when the sortAttribute is a unique key in database.

resultObj = criteria.addOrder(order)
                        .setFirstResult(page.getPageNumber() * page.getPageSize())
                        .setMaxResults(page.getPageSize())
                        .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                        .list();

Also, I do have a join here between two entities. (Although I don't really see why this could be any reason for the unstable behavior)

Answer 1

Well, I think I found a solution to this issue. I did the following to obtain expected results:

• First sort on non unique key.
• Then sort on unique key.

This way the result was always in the proper order and ordering and pagination was ensured.

Hope this may help someone who comes across such an issue.