Am trying without success to use the setFirstResult method of the Criteria class.
To resume without restriction, I get 27 results, so, if I put my setFirstResult(14), I would like to receive 13 objects. 14 + 13 = 27

But the problem is that I get 15 results.

To this problem, I found an explanation.

Here is a short overview of my database:

Plane 1 <----> n Passenger

My query is built with the passenger constraint which results in an inner join between Plane and Passenger.
So, we get as results something like this:

My root entity when I build my Criteria is Plane so in this case, for my query without restriction I get with this example 7 planes. Because of the way Hibernate builds the business objects only find 7 differents planes which is good.

In the second case, there could be a misunderstanding about the way it should work.
If I actually set firstResult to the value 6, Hibernate will find plane 5,6,7 because it uses first Result as the first row returned from the database. In this case Hibernate will return 3 objects when I intend to get 1 object, or 0 when I consider the starting index 0,because I am working with the ORM!!! In this case, the firstResult applies to the database rows results instead of the BO list results! User is not supposed to know that for one main entity (here, plane) there could be more than one row returned.

So here is the question after this long and necessary introduction.

How can I do an efficient pagination with firstResult and maxResults, without burning my brain with database consideration? what is what I am supposed to do when I am using such product as Hibernate.

The same problem exists with the setMaxResults method. In the API we find:
Set a limit upon the number of objects to be retrieved. Are we talking about BusinessObjects or Database rows?


Thanks a lot for an explanation.

the setMaxResults, etc works on the database level.

I can only assume that you are using outer join fetching for 1 or more collections? The outer join can give you a cartesian which will throw off your maxResults. You might be able to change your mapping to use a subselect fetch rather than an outer join as a workaround.

That's the point. I do not understand why we aren't working on the objects level here but on the database level. That wasn't what I expected.


I am going to check this solution.

Thanks.

You can implement it easilly at the object level yourself: use the java.util.List.subList method on your query results !

It's harder to implement it in a portable way at the database level as this feature is not standardized in SQL. Somehow Hibernate standardizes it for you and make it easier to use at the database level.
Unfortunately, the way it's possible to do it at the database level doesn't work well with joined tables (it limits the results on the joined cartesian product rather than on the specific entity you want to retreive). That reduces greatly the possible usages of the functionality. However this is more a database problem than a Hibernate problem.