Implementing result paging in hibernate (getting total number of rows)

Question

How do I implement paging in Hibernate? The Query objects has methods called setMaxResults and setFirstResult which are certainly helpful. But where can I get the total number of results, so that I can show link to last page of results, and print things such as results 200 to 250 of xxx?

Answer 1

you could perform two queries - a count(*) type query, which should be cheap if you are not joining too many tables together, and a second query that has the limits set. Then you know how many items exists but only grab the ones being viewed.

Answer 2

You can use Query.setMaxResults(int results) and Query.setFirstResult(int offset).

Editing too: There's no way to know how many results you'll get. So, first you must query with "select count(*)...". A little ugly, IMHO.

Answer 3

I personally think you should handle the paging in the front-end. I know this isn't that efficiënt but at least it would be less error prone.

If you would use the count(*) thing what would happen if records get deleted from the table in between requests for a certain page? Lots of things could go wrong this way.

Answer 4

You must do a separate query to get the max results...and in the case where between time A of the first time the client issues a paging request to time B when another request is issued, if new records are added or some records now fit the criteria then you have to query the max again to reflect such. I usually do this in HQL like this

Integer count = (Integer) session.createQuery("select count(*) from ....").uniqueResult();

for Criteria queries I usually push my data into a DTO like this

ScrollableResults scrollable = criteria.scroll(ScrollMode.SCROLL_INSENSITIVE);
if(scrollable.last()){//returns true if there is a resultset
    genericDTO.setTotalCount(scrollable.getRowNumber() + 1);
    criteria.setFirstResult(command.getStart())
            .setMaxResults(command.getLimit());
    genericDTO.setLineItems(Collections.unmodifiableList(criteria.list()));
}
scrollable.close();
return genericDTO;