Mybatis拦截器实现分页

本文介绍使用Mybatis拦截器，实现分页；并且在dao层，直接返回自定义的分页对象。

2017-09-29修改：添加Mybatis拦截器分页对oracle、sqlServer的支持

最终dao层结果：

public interface ModelMapper {
	Page<Model> pageByConditions(RowBounds rowBounds, Model record);      
}

接下来一步一步来实现分页。

一.创建Page对象：

public class Page<T> extends ArrayList<T>  {
    private static final long serialVersionUID = 1L;
    
    private int pageNo = 1;// 页码，默认是第一页
    private int pageSize = 15;// 每页显示的记录数，默认是15
    private int totalRecord;// 总记录数
    private int totalPage;// 总页数
    private List<T> result;
    
    public Page() {
        
    }
    
    public Page(int pageNo, int pageSize, int totalRecord,
            List<T> results) {
        this.pageNo = pageNo;
        this.pageSize = pageSize;
        this.totalRecord = totalRecord;
        this.setResult(results);
        int totalPage = totalRecord % pageSize == 0 ? totalRecord / pageSize : totalRecord / pageSize + 1;
        this.setTotalPage(totalPage);
    }

    public int getPageNo() {
        return pageNo;
    }

    public void setPageNo(int pageNo) {
        this.pageNo = pageNo;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotalRecord() {
        return totalRecord;
    }

    public void setTotalRecord(int totalRecord) {
        this.totalRecord = totalRecord;
        // 在设置总页数的时候计算出对应的总页数，在下面的三目运算中加法拥有更高的优先级，所以最后可以不加括号。
        int totalPage = totalRecord % pageSize == 0 ? totalRecord / pageSize : totalRecord / pageSize + 1;
        this.setTotalPage(totalPage);
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
    
    public List<T> getResult() {
        return result;
    }

    public void setResult(List<T> result) {
        this.result = result;
    }


    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("Page [pageNo=").append(pageNo).append(", pageSize=").append(pageSize).append(", results=")
                .append(getResult()).append(", totalPage=").append(totalPage).append(", totalRecord=").append(totalRecord)
                .append("]");
        return builder.toString();
    }
}

　　为什么Page需要继承ArrayList接口，这个会在稍后的代码中做解释。

二.提供Dao以及mapper.xml

　　dao的写法：

Page<Model> pageByConditions(RowBounds rowBounds, Model record);

　　mapper.xml:

  <!-- 表名 -->
  <sql id="tableName" >
    model
  </sql>
  
  <!-- 数据表所有列名 -->
  <sql id="Base_Column_List" >
    id,     
    name    
  </sql>
  
  <!-- 查询字段 -->
  <sql id="Base_Search_Param" >
    <if test="id != null" >
          and id = #{id,jdbcType=INTEGER}
    </if>
    <if test="name != null" >
          and name = #{name,jdbcType=VARCHAR}
    </if>
  </sql>
  
  <!-- 分页查询语句 -->
  <select id="pageByConditions" resultMap="BaseResultMap">
      SELECT 
          <include refid="Base_Column_List" />
      FROM 
          <include refid="tableName" />
      WHERE 1=1
          <include refid="Base_Search_Param" />
  </select>

　　ok,以上都是mybatis的基本操作，就不做多余解释。

三.创建拦截器：

　　拦截器原理以及执行顺序，可参考：http://www.cnblogs.com/fangjian0423/p/mybatis-interceptor.html、http://blog.csdn.net/abcd898989/article/details/51261163

　　我们需要做的是创建一个拦截器（PageInterceptor）、一个执行者(PageExecutor)。

　　1.PageInteceptor:实现Inteceptor接口，将PageExecutor进行执行，拦截sql添加分页sql（limit xx,xx）

　　2.PageExecutor:实现Executor接口，在查询时，添加查询总数并修改返回值类型。因为要做的是分页，是查询操作，所以里边的非查询方法都使用基本的实现，只修改两个query方法。

PageInteceptor完整代码：

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
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.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import cn.com.common.page.PageExecutor;


@Intercepts({
        @Signature(method = "query", type = Executor.class, args = { MappedStatement.class, Object.class,
                RowBounds.class, ResultHandler.class }),
        @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class }) })
public class PageInterceptor implements Interceptor {
    
    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    
    private String pattern = "^.*page.*$";    // 需要进行分页操作的字符串正则表达式
    
    /**  数据库方言：目前只支持mysql、oracle、sqlServer；默认mysql */
    private String dialect = "mysql";

    public String getPattern() {
        return pattern;
    }

    public void setPattern(String pattern) {
        this.pattern = pattern;
    }

    public String getDialect() {
        return dialect;
    }

    public void setDialect(String dialect) {
        this.dialect = dialect;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof StatementHandler) {
            return handleStatementHandler(invocation);
        }
        return invocation.proceed();
    }

    /**
     * @param invocation
     * @return
     * @throws IllegalAccessException 
     * @throws InvocationTargetException 
     */
    private Object handleStatementHandler(Invocation invocation)
            throws InvocationTargetException, IllegalAccessException {
        StatementHandler statementHandler = (StatementHandler) invocation
                .getTarget();
        MetaObject metaStatementHandler = MetaObject.forObject(
                statementHandler, DEFAULT_OBJECT_FACTORY,
                DEFAULT_OBJECT_WRAPPER_FACTORY);
        RowBounds rowBounds = (RowBounds) metaStatementHandler
                .getValue("delegate.rowBounds");
        if (rowBounds == null || (rowBounds.getOffset() == RowBounds.NO_ROW_OFFSET && rowBounds
                .getLimit() == RowBounds.NO_ROW_LIMIT)) {
            return invocation.proceed();
        }
        
        // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环可以分离出最原始的的目标类)
        while (metaStatementHandler.hasGetter("h")) {
            Object object = metaStatementHandler.getValue("h");
            metaStatementHandler = MetaObject.forObject(object,
                    DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
        }
        // 分离最后一个代理对象的目标类
        while (metaStatementHandler.hasGetter("target")) {
            Object object = metaStatementHandler.getValue("target");
            metaStatementHandler = MetaObject.forObject(object,
                    DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
        }

        // 将mybatis的内存分页，调整为物理分页
        BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
        String sql = boundSql.getSql();
        // 重写sql
        String pageSql = PageSqlFactory.getPageSqlByDialect(this.dialect, sql, rowBounds);
        metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
        // 采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数
        metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
        metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
        
        // 将执行权交给下一个拦截器
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object o) {
        if (Executor.class.isAssignableFrom(o.getClass())) {
            PageExecutor executor = new PageExecutor((Executor)o, pattern);
            return Plugin.wrap(executor, this);
        } else if (o instanceof StatementHandler) {
            return Plugin.wrap(o, this);
        }
        return o;
    }

    @Override
    public void setProperties(Properties properties) {
    }

}

PageSqlFactory在工厂中根据数据库方言创建分页sql

public class PageSqlFactory {
    
    /**
     * 根据数据库方言获取分页查询语句（目前只支持mysql、oracle、sqlServer；默认mysql）
     * @param dialect
     * @param originalSql
     * @param rowBounds
     * @return
     * @author Taocong
     * @date 2017年9月28日 下午4:12:57
     */
    public static String getPageSqlByDialect(String dialect, String originalSql, RowBounds rowBounds) {
        String pageSql = null;
        if (null == dialect || "mysql".equals(dialect)) {
            pageSql = originalSql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit();
        } else if ("oracle".equals(dialect)) {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("select * from ( select tmp_page.*, rownum row_id from ( ");
            sqlBuilder.append(originalSql);
            sqlBuilder.append(" ) tmp_page where rownum <= ");
            sqlBuilder.append(rowBounds.getLimit());
            sqlBuilder.append(" ) where row_id > ");
            sqlBuilder.append(rowBounds.getOffset());
            pageSql = sqlBuilder.toString();
        } else if ("sqlServer".equals(dialect)) {
            /* 查看sql中是否有排序规则：
             * 1.如果没有，按手动新增字段n（n=0）生成rown_number
             * 2.如果有，按指定的排序规则生成rown_number
             * 
             *  eg：
             *  1.select * from a where 1=1
             *  ->select * from (select row_number() over(ORDER BY n) as rownumber,* 
             *      from ( select top 20 n=0, * from a where 1=1)t )tt where rownumber> 0
             *
             *  2.select * from a where 1=1 ORDER BY b
             *  ->select * from (select row_number() over(ORDER BY b) as rownumber,* 
             *      from ( select top 20 n=0, * from a where 1=1 ORDER BY b)t )tt where rownumber> 0
             */
            // 找到sql中的排序
            // (由于indexof不能使用正则，所以先使用replaceAll对sql中的排序语法规范化;(?i)标识忽略大小写;\s*表示空格出现一次或多次)
            int orderStartIndex = originalSql.replaceAll("(?i)ORDER\s+BY", "ORDER BY").lastIndexOf("ORDER BY");
            String orderStr = "ORDER BY n";
            // 有排序，且是最外层的排序
            if (orderStartIndex != -1 && originalSql.lastIndexOf(")") < orderStartIndex) {
                orderStr = originalSql.substring(orderStartIndex);
            }
            pageSql = originalSql.replaceFirst("(?i)select", "select * from (select row_number() over(" + orderStr
                    + ") as rownumber,* from ( select top " + (rowBounds.getOffset() + rowBounds.getLimit()) + " n=0,");
            pageSql += ")t )tt where rownumber> " + rowBounds.getOffset();
        }

        return pageSql;
    }
}

PageExecutor完整代码：

package cn.com.common.page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.transaction.Transaction;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;

public class PageExecutor implements Executor {
    
    private Executor executor;
    
    private String pattern;
    
    public PageExecutor(Executor executor, String pattern) {
        this.executor = executor;
        this.pattern = pattern;
    }

    @Override
    public int update(MappedStatement ms, Object parameter) throws SQLException {
        return executor.update(ms, parameter);
    }

    @Override
    public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler,
            CacheKey cacheKey, BoundSql boundSql) throws SQLException {
        RowBounds rb = new RowBounds(rowBounds.getOffset(), rowBounds.getLimit());
        List<E> rows = executor.query(ms, parameter, rowBounds, resultHandler,
                cacheKey, boundSql);
        return pageResolver(rows, ms, parameter, rb);
    }
    
    /**
     * 修改返回值类型
     * @param rows
     * @param ms
     * @param parameter
     * @param rowBounds
     * @return
     */
    private <E> List<E> pageResolver(List<E> rows, MappedStatement ms,
            Object parameter, RowBounds rowBounds) {
        String msid = ms.getId();
        // 如果需要分页查询，修改返回类型为Page对象
        if (msid.matches(pattern)) {
            int count = getCount(ms, parameter);
            int offset = rowBounds.getOffset();
            int pagesize = rowBounds.getLimit();
            return new Page<E>(offset/pagesize + 1, pagesize, count, rows);
        }
        return rows;
    }
    
    /**
     * 获取总数
     * @param ms
     * @param parameter
     * @return
     */
    private int getCount(MappedStatement ms, Object parameter) {
        BoundSql bsql = ms.getBoundSql(parameter);
        String sql = bsql.getSql();
        String countSql = getCountSql(sql);
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            connection = ms.getConfiguration().getEnvironment().getDataSource()
                    .getConnection();
            stmt = connection.prepareStatement(countSql);
            setParameters(stmt, ms, bsql, parameter);
            rs = stmt.executeQuery();
            if (rs.next())
                return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null && !connection.isClosed()) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
    
    @SuppressWarnings("unchecked")
    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
            Object parameterObject) throws SQLException {
        ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {
            Configuration configuration = mappedStatement.getConfiguration();
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else {
                        value = metaObject == null ? null : metaObject.getValue(propertyName);
                    }
                    @SuppressWarnings("rawtypes")
                    TypeHandler typeHandler = parameterMapping.getTypeHandler();
                    if (typeHandler == null) {
                        throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName
                                + " of statement " + mappedStatement.getId());
                    }
                    typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
                }
            }
        }
    }
    
    private String getCountSql(String sql) {
        String countHql = " SELECT count(*) "
                + removeSelect(removeOrders(sql));

        return countHql;
    }
    
    protected String removeOrders(String sql) {
        Pattern p = Pattern.compile("ORDER\s*by[\w|\W|\s|\S]*", Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(sql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(sb, "");
        }
        m.appendTail(sb);
        return sb.toString();
    }
    
    // 去除sql语句中select子句
    private static String removeSelect(String hql) {
        int beginPos = hql.toLowerCase().indexOf("from");
        if (beginPos < 0) {
            throw new IllegalArgumentException(" hql : " + hql + " must has a keyword 'from'");
        }
        return hql.substring(beginPos);
    }

    @Override
    public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler)
            throws SQLException {
        BoundSql boundSql = ms.getBoundSql(parameter);
        return query(ms, parameter, rowBounds, resultHandler,
                executor.createCacheKey(ms, parameter, rowBounds, boundSql),
                boundSql);
    }

    @Override
    public List<BatchResult> flushStatements() throws SQLException {
        return executor.flushStatements();
    }

    @Override
    public void commit(boolean required) throws SQLException {
        executor.commit(required);
    }

    @Override
    public void rollback(boolean required) throws SQLException {
        executor.rollback(required);
    }

    @Override
    public CacheKey createCacheKey(MappedStatement ms, Object parameterObject,
            RowBounds rowBounds, BoundSql boundSql) {
        return executor
                .createCacheKey(ms, parameterObject, rowBounds, boundSql);
    }

    @Override
    public boolean isCached(MappedStatement ms, CacheKey key) {
        return executor.isCached(ms, key);
    }

    @Override
    public void clearLocalCache() {
        executor.clearLocalCache();
    }

    @Override
    public void deferLoad(MappedStatement ms, MetaObject resultObject,
            String property, CacheKey key, Class<?> targetType) {
        executor.deferLoad(ms, resultObject, property, key, targetType);
    }

    @Override
    public Transaction getTransaction() {
        return executor.getTransaction();
    }

    @Override
    public void close(boolean forceRollback) {
        executor.close(forceRollback);
    }

    @Override
    public boolean isClosed() {
        return executor.isClosed();
    }
    
}

　　关于Page需要实现List接口的原因：可以看到，query方法返回值是List<E>,而我们现在要在dao中使用Page<E>对象来接收mybatis返回的结果，所以需要让Page实现List接口。

　　分页查询执行顺序：进入PageInterceptor的plugin方法，拦截到执行者，进入PageExecutor的query方法，执行executor.query()时，又再次回到PageInterceptor的plugin方法，这次会执行

　　进入intercept方法，将执行的sql拼接上分页限制语句，然后查询出数据结果集合。executor.query()执行完成后，继续执行pageResolver，如果方法名称和配置的需要执行分页操作的字符串匹配时，查询数据总量，并返回Page对象；如果不匹配，直接返回List对象。

四.xml配置：

    <!-- myBatis文件 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/conf/mybatis/mybaties-config.xml"></property>
        <property name="mapperLocations">
            <list>
                <value>classpath:/conf/mybatis/**/*Mapper.xml</value>
            </list>
        </property>
        <property name="plugins">
            <list>
                <ref bean="pageInterceptor"/>
            </list>        
        </property>
    </bean>
    
    <!-- mybatis拦截器，实现分页 -->
    <bean id="pageInterceptor" class="cn.com.common.page.PageInterceptor">
        <property name="pattern" value="^.*page.*$"></property>
        <property name="dialect" value="mysql"></property>
    </bean>

 

五.测试代码：

    @Test
    public void testPage() {
        int pageNo = 1;
        int pageSize = 10;
        RowBounds bounds = new RowBounds((pageNo - 1) * pageSize, pageSize);
        Model record = new Model();
        
        Page<Model> list = modelMapper.pageByConditions(bounds, record);
    }