利用mybatis实现物理分页

由于Mybatis的分页功能很弱，它是基于内存的分页（查出所有记录再按偏移量和limit取结果），在大数据量的情况下这样的分页基本上是没有用的。所以就自己写了一个分页插件，思想通过拦截StatementHandler重写sql语句，实现数据库的物理分页。测试使用的mybatis版本是3.2.7。以下是具代码体实现过程：

PaginationInterceptor类：该类完成对StatementHandler类里面的prepare方法的拦截，实现SQL语句的重写及获取数据的总条数。

package org.reacher.interceptor.pagination;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.apache.ibatis.executor.parameter.ParameterHandler;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.plugin.Interceptor;import org.apache.ibatis.plugin.Intercepts;import org.apache.ibatis.plugin.Invocation;import org.apache.ibatis.plugin.Plugin;import org.apache.ibatis.plugin.Signature;import org.apache.ibatis.reflection.MetaObject;import org.apache.ibatis.reflection.SystemMetaObject;import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;import org.apache.ibatis.session.RowBounds;import org.reacher.interceptor.pagination.dialect.Dialect;import org.reacher.interceptor.pagination.dialect.DialectFactory;import org.reacher.interceptor.pagination.model.PageBounds;/** * @author reacher * *PaginationInterceptor */@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})public class PaginationInterceptor implements Interceptor {private static final Log LOG = LogFactory.getLog(PaginationInterceptor.class);private Class<?> clazz = null;@Overridepublic Object intercept(Invocation invocation) throws Throwable {if(null == this.clazz) {LOG.error("Not found has been initialized database dialect!");return invocation.proceed();}final Dialect dialect = DialectFactory.getDialect(this.clazz);if(null == dialect) {LOG.error(this.clazz.getName() + " initialized failed!");return invocation.proceed();}final StatementHandler statementHandler = (StatementHandler) invocation.getTarget();final MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);final RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");PageBounds pageBounds = null;if (rowBounds instanceof PageBounds) {pageBounds = (PageBounds) rowBounds;}if(null == pageBounds || 0 >= pageBounds.getSize() || 0 >= pageBounds.getNumber()) {return invocation.proceed();}final MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");final BoundSql boundSql = statementHandler.getBoundSql();final Object parameterObject = boundSql.getParameterObject();final Connection connection = (Connection) invocation.getArgs()[0];pageBounds.setCount(this.getCount(mappedStatement, connection, parameterObject, dialect));metaStatementHandler.setValue("delegate.boundSql.sql", dialect.getLimitSql(boundSql.getSql(), pageBounds.getSize() * (pageBounds.getNumber() - 1), pageBounds.getSize()));        metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);        metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);return invocation.proceed();}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {String dialectName = properties.getProperty("dialect");if(null == dialectName) {LOG.error("Property dialectName isn't set!");return;}try {this.clazz = Class.forName(dialectName);} catch (ClassNotFoundException e) {LOG.error(e);}}private int getCount(final MappedStatement mappedStatement, final Connection connection, final Object parameterObject, final Dialect dialect) {final BoundSql boundSql = mappedStatement.getBoundSql(parameterObject);final String countSql = dialect.getCountSql(boundSql.getSql());PreparedStatement preparedStatement = null;ResultSet resultSet = null;int count = 0;try {preparedStatement = connection.prepareStatement(countSql);final ParameterHandler handler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);handler.setParameters(preparedStatement);resultSet = preparedStatement.executeQuery();if (resultSet.next()) {count = resultSet.getInt(1);}} catch (Exception e) {LOG.error(e);} finally {if (preparedStatement != null) {try {preparedStatement.close();} catch (SQLException e) {LOG.error(e);}}}return count;}}

Dialect接口：这个接口的作用主要是方便不同的数据库只需要实现这个接口都可以使用该插件。

package org.reacher.interceptor.pagination.dialect;/** * @author reacher * */public interface Dialect {/** * 检查数据库是否支持分页 */public abstract boolean supportsLimit();/** * 得到获取数据总条数的SQL语句 */public String getCountSql(String sql);/** * 得到分页的SQL语句 */public abstract String getLimitSql(String sql, long offset, long limit);}

DialectFactory工厂类：

package org.reacher.interceptor.pagination.dialect;import java.lang.reflect.Constructor;import java.util.HashMap;import java.util.Map;/** * @author reacher * */public final class DialectFactory {private static Map<String, Dialect> DIALECTS = new HashMap<String, Dialect>();public static Dialect getDialect(Class<?> clazz) throws Exception {Dialect dialect = DIALECTS.get(clazz.getSimpleName());if (dialect == null) {dialect = newInstance(clazz);if(null != dialect) {DIALECTS.put(clazz.getSimpleName(), dialect);}}return dialect;}private static Dialect newInstance(Class<?> clazz) throws Exception {if(null == clazz) {return null;}Constructor<?> constructor = clazz.getConstructor();constructor.setAccessible(true);Object object = constructor.newInstance();Dialect dialect = null;if(object instanceof Dialect) {dialect = (Dialect)object;}return dialect;}}

PageBounds类：

package org.reacher.interceptor.pagination.model;import org.apache.ibatis.session.RowBounds;/** * @author reacher * */public class PageBounds extends RowBounds {private int size;//页面大小private int number;//当前页数private int count;//数据总条数private int total;//总页数public PageBounds() {this.number = 1;this.size = 10;}public PageBounds(int pageSize) {this.number = 1;this.size = pageSize;}public int getSize() {return size;}public void setSize(int size) {this.size = size;}public int getNumber() {return number;}public void setNumber(int number) {this.number = number;}public int getCount() {return count;}public void setCount(int count) {this.count = count;}public int getTotal() {this.total = this.count / this.size + (this.count % this.size > 0 ? 1 : 0); return this.total;}public void setTotal(int total) {this.total = total;}}

MySQLDialect类：实现mysql数据库的分页。

package org.reacher.interceptor.pagination.dialect.database;import org.reacher.interceptor.pagination.dialect.Dialect;/** * @author reacher * */public class MySQLDialect implements Dialect {@Overridepublic boolean supportsLimit() {return true;}@Overridepublic String getCountSql(String sql) {return "SELECT COUNT(*) FROM (" + sql.replaceAll(";", "") + ") temp";}@Overridepublic String getLimitSql(String sql, long offset, long limit) {StringBuffer stringBuffer = new StringBuffer(sql.replaceAll(";", ""));stringBuffer.append(" LIMIT ").append(offset).append(", ").append(limit);return stringBuffer.toString();}}

mybatis-configuration.xml配置如下：

<plugins><span style="white-space:pre"></span><plugininterceptor="org.reacher.interceptor.pagination.PaginationInterceptor"><property name="dialect" value="org.reacher.interceptor.pagination.dialect.database.MySQLDialect"/></plugin></plugins>