Hibernate Criteria - Order and Pagination

Question

I want to ask if Criteria setFirstResult and setMaxResults can interfere with the Order I'm applying before using both methods. I have noticed that when addingOrder on field, the results if any repeated by that field are sorted by id of the records. If I apply setFirstResult, setMaxResults on criteria after the Order, same results are sorted by on how they've been saved.

Answer 1

the results if any repeated by that field are sorted by id of the records.

Based on the above statement, my basic assumption/interpretation is that you are referring to a scenario where you are applying addOrder on a field/column that has duplicate/same values (column with non-unique values).

I tried sample code and noticed that the SQL that is generated is same in both the cases. (whether addOrder is specified before or after setFirst/MaxResults).

Hibernate: select this_.id as id1_0_0_, this_.name as name2_0_0_, this_.version as version3_0_0_ from SimplePost this_ order by this_.name asc limit ?

I have noticed that when addingOrder on field, the results if any repeated by that field are sorted by id of the records. If I apply setFirstResult, setMaxResults on criteria after the Order, same results are sorted by on how they've been saved.

The above two statement are also not guaranteed, unless the underlying the database ensures that it returns the rows the same order even if sorted by non-unique column values. See the below output.

NOTE: I have tested with Hibernate5 and h2 in-memory database. And the order of inserts is same across multiple runs.

With addOrder after setMaxResults, the query used :

session1.createCriteria(OrderByPost.class).setFirstResult(0).setMaxResults(3).addOrder(Order.asc("name")).list()

Results:

[Post [id=1, name=Apost], Post [id=3, name=Bpost], Post [id=6, name=Bpost]]
[Post [id=1, name=Apost], Post [id=4, name=Bpost], Post [id=6, name=Bpost]]
[Post [id=1, name=Apost], Post [id=2, name=Bpost], Post [id=6, name=Bpost]]
[Post [id=1, name=Apost], Post [id=3, name=Bpost], Post [id=6, name=Bpost]]
[Post [id=1, name=Apost], Post [id=4, name=Bpost], Post [id=6, name=Bpost]]

With addOrder before setFirstResult, the query used:

session1.createCriteria(OrderByPost.class).addOrder(Order.asc("name")).setFirstResult(0).setMaxResults(3).list()

Results:

[Post [id=1, name=Apost], Post [id=3, name=Bpost], Post [id=6, name=Bpost]]
[Post [id=1, name=Apost], Post [id=6, name=Bpost], Post [id=4, name=Bpost]]
[Post [id=1, name=Apost], Post [id=6, name=Bpost], Post [id=4, name=Bpost]]
[Post [id=1, name=Apost], Post [id=3, name=Bpost], Post [id=6, name=Bpost]]
[Post [id=1, name=Apost], Post [id=6, name=Bpost], Post [id=4, name=Bpost]]
[Post [id=1, name=Apost], Post [id=6, name=Bpost], Post [id=4, name=Bpost]]

I think if you run it couple of times you might see that order is not guaranteed even if we don't change the addOrder(..) method relative to setFirst/MaxResults.

Based on the above observations, I believe, when we specify addOrder on a field, all that hibernate guarantees is the results are sorted by that field.

And if that field/column happens to have duplicate/same values for some rows, hibernate doesn't ensure that we will getting those rows in the same order for multiple executions.

One option to ensure that we get the results in the same order every time, if we are ordering on field/column that can have duplicate/same values, is to add another addOrder on some unique field, say id as below:

session1.createCriteria(OrderByPost.class).setFirstResult(0).setMaxResults(7).addOrder(Order.asc("name")).addOrder(Order.asc("id")).list()

By the way, let me know if I got my basic assumption/interpretation itself wrong, which I hope not.