Is there a more efficient way of making pagination

问题:

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

回答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.

回答2:

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

}

回答3:

If you don't need to display the total number of pages then I'm not sure you need the count query. Lots of sites including google don't show the total on the paged results. Instead they just say "next>".

回答4:

You can use MultiQuery to execute both queries in a single database call, which is much more efficient. You can also generate the count query, so you don't have to write it each time. Here's the general idea ...

var hql = "from Item where i.Age > :age"
var countHql = "select count(*) " + hql;

IMultiQuery multiQuery = _session.CreateMultiQuery()
    .Add(s.CreateQuery(hql)
            .SetInt32("age", 50).SetFirstResult(10))
    .Add(s.CreateQuery(countHql)
            .SetInt32("age", 50));

var results = multiQuery.List();
var items = (IList<Item>) results[0];
var count = (long)((IList<Item>) results[1])[0];

I imagine it would be easy enough to wrap this up into some easy-to-use method so you can have paginateable, countable queries in a single line of code.

As an alternative, if you're willing to test the work-in-progress Linq for NHibernate in nhcontrib, you might find you can do something like this:

var itemSpec = (from i in Item where i.Age > age);
var count = itemSpec.Count();
var list = itemSpec.Skip(10).Take(10).AsList(); 

Obviously there's no batching going on, so that's not as efficient, but it may still suite your needs?

Hope this helps!

回答5:

There is a way

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

Reference: FOUND_ROWS()

回答6:

I know this problem and have faced it before. For starters, the double query mechanism where it does the same SELECT conditions is indeed not optimal. But, it works, and before you go off and do some giant change, just realize it might not be worth it.

But, anyways:

1) If you are dealing with small data on the client side, use a result set implementation that lets you set the cursor to the end of the set, get its row offset, then reset the cursor to before first.

2) Redesign the query so that you get COUNT(*) as an extra column in the normal rows. Yes, it contains the same value for every row, but it only involves 1 extra column that is an integer. This is improper SQL to represent an aggregated value with non aggregated values, but it may work.

3) Redesign the query to use an estimated limit, similar to what was being mentioned. Use rows per page and some upper limit. E.g. just say something like "Showing 1 to 10 of 500 or more". When they browse to "Showing 25o to 260 of X", its a later query so you can just update the X estimate by making the upper bound relative to page * rows/page.

回答7:

I think the solution depends on database you are using. For example, we are using MS SQL and using next query

select 
  COUNT(Table.Column) OVER() as TotalRowsCount,
  Table.Column,
  Table.Column2
from Table ...

That part of query can be changed with database specified SQL.

Also we set the query max result we are expecting to see, e.g.

query.setMaxResults(pageNumber * itemsPerPage)

And gets the ScrollableResults instance as result of query execution:

ScrollableResults result = null;
try {
    result = query.scroll();
    int totalRowsNumber = result.getInteger(0);
    int from = // calculate the index of row to get for the expected page if any

    /*
     * Reading data form page and using Transformers.ALIAS_TO_ENTITY_MAP
     * to make life easier.
     */ 
}
finally {
    if (result != null) 
        result.close()
}

回答8:

At this Hibernate wiki page:

https://www.hibernate.org/314.html

I present a complete pagination solution; in particular, the total number of elements is computed by scrolling to the end of the resultset, which is supported by now by several JDBC drivers. This avoids the second "count" query.

回答9:

I found a way to do paging in hibernate without doing a select count (*) over a large dataset size. Look at the solution that I posted for my answer here.

processing a large number of database entries with paging slows down with time

you can perform paging one at a time without knowing how many pages you will need originally

回答10:

Here is a solution by Dr Richard Kennard (mind the bug fix in the blog comment!), using Hibernate Interceptors

For summary, you bind your sessionFactory to your interceptor class, so that your interceptor can give you the number of found rows later.

You can find the code on the solution link. And below is an example usage.

SessionFactory sessionFactory = ((org.hibernate.Session) mEntityManager.getDelegate()).getSessionFactory();
MySQLCalcFoundRowsInterceptor foundRowsInterceptor = new MySQLCalcFoundRowsInterceptor( sessionFactory );
Session session = sessionFactory.openSession( foundRowsInterceptor );

try {
   org.hibernate.Query query = session.createQuery( ... )   // Note: JPA-QL, not createNativeQuery!
   query.setFirstResult( ... );
   query.setMaxResults( ... );

   List entities = query.list();
   long foundRows = foundRowsInterceptor.getFoundRows();

   ...

} finally {

   // Disconnect() is good practice, but close() causes problems. Note, however, that
   // disconnect could lead to lazy-loading problems if the returned list of entities has
   // lazy relations

   session.disconnect();
}

回答11:

here's the way pagination is done in hibernate

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

you can get more info from hibernate docs at : http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#objectstate-querying-executing-pagination 10.4.1.5 and 10.4.1.6 section give you more flexbile options.

BR,
~A