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(); } 

Answer 1

My solution will work for the very common use case of Hibernate+Spring+MySQL

Similar to the above answer, I based my solution upon Dr Richard Kennar's. However, since Hibernate is often used with Spring, I wanted my solution to work very well with Spring and the standard method for using Hibernate. Therefore my solution uses a combination of thread locals and singleton beans to achieve the result. Technically the interceptor is invoked on every prepared SQL statement for the SessionFactory, but it skips all logic and does not initialize any ThreadLocal(s) unless it is a query specifically set to count the total rows.

Using the below class, your Spring configuration looks like:

<bean id="foundRowCalculator" class="my.hibernate.classes.MySQLCalcFoundRowsInterceptor" />     <!-- p:sessionFactoryBeanName="mySessionFactory"/ -->  <bean id="mySessionFactory"     class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"     p:dataSource-ref="dataSource"     p:packagesToScan="my.hibernate.classes"     p:entityInterceptor-ref="foundRowCalculator"/> 

Basically you must declare the interceptor bean and then reference it in the "entityInterceptor" property of the SessionFactoryBean. You must only set "sessionFactoryBeanName" if there is more than one SessionFactory in your Spring context and the session factory you want to reference is not called "sessionFactory". The reason you cannot set a reference is that this would cause an interdependency between the beans that cannot be resolved.

Using a wrapper bean for the result:

package my.hibernate.classes;  public class PagedResponse<T> {     public final List<T> items;     public final int total;     public PagedResponse(List<T> items, int total) {         this.items = items;         this.total = total;     } } 

Then using an abstract base DAO class you must call "setCalcFoundRows(true)" before making the query and "reset()" after [in a finally block to ensure it's called]:

package my.hibernate.classes;  import org.hibernate.Criteria; import org.hibernate.Query; import org.springframework.beans.factory.annotation.Autowired;  public abstract class BaseDAO {      @Autowired     private MySQLCalcFoundRowsInterceptor rowCounter;      public <T> PagedResponse<T> getPagedResponse(Criteria crit, int firstResult, int maxResults) {         rowCounter.setCalcFoundRows(true);         try {             @SuppressWarnings("unchecked")             return new PagedResponse<T>(                 crit.                 setFirstResult(firstResult).                 setMaxResults(maxResults).                 list(),                 rowCounter.getFoundRows());         } finally {             rowCounter.reset();         }     }      public <T> PagedResponse<T> getPagedResponse(Query query, int firstResult, int maxResults) {         rowCounter.setCalcFoundRows(true);         try {             @SuppressWarnings("unchecked")             return new PagedResponse<T>(                 query.                 setFirstResult(firstResult).                 setMaxResults(maxResults).                 list(),                 rowCounter.getFoundRows());         } finally {             rowCounter.reset();         }     } } 

Then a concrete DAO class example for an @Entity named MyEntity with a String property "prop":

package my.hibernate.classes;  import org.hibernate.SessionFactory; import org.hibernate.criterion.Restrictions import org.springframework.beans.factory.annotation.Autowired;  public class MyEntityDAO extends BaseDAO {      @Autowired     private SessionFactory sessionFactory;      public PagedResponse<MyEntity> getPagedEntitiesWithPropertyValue(String propVal, int firstResult, int maxResults) {         return getPagedResponse(             sessionFactory.             getCurrentSession().             createCriteria(MyEntity.class).             add(Restrictions.eq("prop", propVal)),             firstResult,              maxResults);     } } 

Finally the interceptor class that does all the work:

package my.hibernate.classes;  import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;  import org.hibernate.EmptyInterceptor; import org.hibernate.HibernateException; import org.hibernate.SessionFactory; import org.hibernate.Transaction; import org.hibernate.jdbc.Work; import org.springframework.beans.BeansException; import org.springframework.beans.factory.BeanFactory; import org.springframework.beans.factory.BeanFactoryAware;  public class MySQLCalcFoundRowsInterceptor extends EmptyInterceptor implements BeanFactoryAware {        /**      *       */     private static final long serialVersionUID = 2745492452467374139L;      //     // Private statics     //      private final static String SELECT_PREFIX = "select ";      private final static String CALC_FOUND_ROWS_HINT = "SQL_CALC_FOUND_ROWS ";      private final static String SELECT_FOUND_ROWS = "select FOUND_ROWS()";      //     // Private members     //     private SessionFactory sessionFactory;      private BeanFactory beanFactory;      private String sessionFactoryBeanName;      private ThreadLocal<Boolean> mCalcFoundRows = new ThreadLocal<Boolean>();      private ThreadLocal<Integer> mSQLStatementsPrepared = new ThreadLocal<Integer>() {         @Override         protected Integer initialValue() {             return Integer.valueOf(0);         }     };      private ThreadLocal<Integer> mFoundRows = new ThreadLocal<Integer>();        private void init() {         if (sessionFactory == null) {             if (sessionFactoryBeanName != null) {                 sessionFactory = beanFactory.getBean(sessionFactoryBeanName, SessionFactory.class);             } else {                 try {                     sessionFactory = beanFactory.getBean("sessionFactory", SessionFactory.class);                 } catch (RuntimeException exp) {                  }                 if (sessionFactory == null) {                     sessionFactory = beanFactory.getBean(SessionFactory.class);                  }             }         }     }      @Override     public String onPrepareStatement(String sql) {         if (mCalcFoundRows.get() == null || !mCalcFoundRows.get().booleanValue()) {             return sql;         }         switch (mSQLStatementsPrepared.get()) {          case 0: {             mSQLStatementsPrepared.set(mSQLStatementsPrepared.get() + 1);              // First time, prefix CALC_FOUND_ROWS_HINT              StringBuilder builder = new StringBuilder(sql);             int indexOf = builder.indexOf(SELECT_PREFIX);              if (indexOf == -1) {                 throw new HibernateException("First SQL statement did not contain '" + SELECT_PREFIX + "'");             }              builder.insert(indexOf + SELECT_PREFIX.length(), CALC_FOUND_ROWS_HINT);             return builder.toString();         }          case 1: {             mSQLStatementsPrepared.set(mSQLStatementsPrepared.get() + 1);              // Before any secondary selects, capture FOUND_ROWS. If no secondary             // selects are             // ever executed, getFoundRows() will capture FOUND_ROWS             // just-in-time when called             // directly              captureFoundRows();             return sql;         }          default:             // Pass-through untouched             return sql;         }     }      public void reset() {         if (mCalcFoundRows.get() != null && mCalcFoundRows.get().booleanValue()) {             mSQLStatementsPrepared.remove();             mFoundRows.remove();             mCalcFoundRows.remove();         }     }      @Override     public void afterTransactionCompletion(Transaction tx) {         reset();     }      public void setCalcFoundRows(boolean calc) {         if (calc) {             mCalcFoundRows.set(Boolean.TRUE);         } else {             reset();         }     }      public int getFoundRows() {         if (mCalcFoundRows.get() == null || !mCalcFoundRows.get().booleanValue()) {             throw new IllegalStateException("Attempted to getFoundRows without first calling 'setCalcFoundRows'");         }         if (mFoundRows.get() == null) {             captureFoundRows();         }          return mFoundRows.get();     }      //     // Private methods     //      private void captureFoundRows() {         init();          // Sanity checks          if (mFoundRows.get() != null) {             throw new HibernateException("'" + SELECT_FOUND_ROWS + "' called more than once");         }          if (mSQLStatementsPrepared.get() < 1) {             throw new HibernateException("'" + SELECT_FOUND_ROWS + "' called before '" + SELECT_PREFIX + CALC_FOUND_ROWS_HINT + "'");         }          // Fetch the total number of rows          sessionFactory.getCurrentSession().doWork(new Work() {             @Override             public void execute(Connection connection) throws SQLException {                 final Statement stmt = connection.createStatement();                 ResultSet rs = null;                 try {                     rs = stmt.executeQuery(SELECT_FOUND_ROWS);                     if (rs.next()) {                         mFoundRows.set(rs.getInt(1));                     } else {                         mFoundRows.set(0);                     }                 } finally {                     if (rs != null) {                         rs.close();                     }                     try {                         stmt.close();                     } catch (RuntimeException exp) {                      }                 }             }         });     }      public void setSessionFactoryBeanName(String sessionFactoryBeanName) {         this.sessionFactoryBeanName = sessionFactoryBeanName;     }      @Override     public void setBeanFactory(BeanFactory arg0) throws BeansException {         this.beanFactory = arg0;     }  } 

Answer 2

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.