java - Is there a more efficient way of making pagination in Hibernate than executing select and count queries?

Question

Usually pagination queries look like this. Is there a better way instead of making two almost equal methods, one of which executing "select *..." and the other one "count *..."?

public List<Cat> findCats(String name, int offset, int limit) {

    Query q = session.createQuery("from Cat where name=:name");

    q.setString("name", name);

    if (offset > 0) {
        q.setFirstResult(offset);
    }
    if (limit > 0) {
        q.setMaxResults(limit);
    }

    return q.list();

}

public Long countCats(String name) {
    Query q = session.createQuery("select count(*) from Cat where name=:name");
    q.setString("name", name);
    return (Long) q.uniqueResult();
}

question from:https://stackoverflow.com/questions/168084/is-there-a-more-efficient-way-of-making-pagination-in-hibernate-than-executing-s

Answer 1

Baron Schwartz at MySQLPerformanceBlog.com authored a post about this. I wish there was a magic bullet for this problem, but there isn't. Summary of the options he presented:

1. On the first query, fetch and cache all the results.
2. Don't show all results.
3. Don't show the total count or the intermediate links to other pages. Show only the "next" link.
4. Estimate how many results there are.