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?

Ridglea

I agree with the two other answers. In addition, I ask for implementation practices in this post : #1600940 – Decelerate

Answer 1

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.

Disrelish

What is the status today after 12 years? Still need separate queries? – Fulviah

Answer 2

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;

Dollarbird

Answer 3

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.

Cyrillic

Answer 4

You can do one thing. just prepare Criteria query as per your busness requirement with all Predicates , sorting , searching etc. and then do as below :-

 CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
   CriteriaQuery<Feedback> criteriaQuery = criteriaBuilder.createQuery(Feedback.class);

//Just Prepare your all Predicates as per your business need. //eg :-

 yourPredicateAsPerYourBusnessNeed = criteriaBuilder.equal(Root.get("applicationName"), applicationName);
 criteriaQuery.where(yourPredicateAsPerYourBusnessNeed).distinct(true);

   TypedQuery<Feedback> criteriaQueryWithPredicate = em.createQuery(criteriaQuery);
   //Getting total Count Here
   Long totalCount = criteriaQueryWithPredicate.getResultStream().distinct().count();

Now we have our actual data with us as above with total count , right. So now we can apply pagination on the data we have in our hand above , as below :-

List<Feedback> feedbackList = criteriaQueryWithPredicate.setFirstResult(offset).setMaxResults(pageSize).getResultList();

Now You can prepare a wrapper with your List return by DB along with the totalCount , startingPageNo that is offset here in this case, page Size etc and can return to your service / controller class.

I am 101 % sure , this will solve your problem, Because I was facing same problem and sorted it out same way. Thanks- Sunil Kumar Mali

Livengood

Hibernate Criteria Query with pagination and total count before pagination apply in single Hibernate call. – Livengood

Answer 5

You can just setMaxResults to the maximum number of rows you want returned. There is no harm in setting this value greater than the number of actual rows available. The problem the other solutions is they assume the ordering of records remains the same each repeat of the query, and there are no changes going on between commands.

To avoid that if you really want to scroll through results, it is best to use the ScrollableResults. Don't throw this object away between paging, but use it to keep the records in the same order. To find out the number of records from the ScrollableResults, you can simply move to the last() position, and then get the row number. Remember to add 1 to this value, since row numbers start counting at 0.

Stagger

Answer 6

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.

Publish

I don't think it is critical. If records get deleted and you get to browse the last pages then you'll receive an error plus a link to the latest existing records. Or something similar. – Ridglea

there are problems with paging on the front end if the amount of data is prohibitively large - say, 1 million records or something. – Cyrillic