通过Mybatis拦截器实现分页

思路：在mybatis中使用Interceptor可以对Executor、StatementHandler、PameterHandler和ResultSetHandler进行拦截，而这四个对象构成了mybatis中的sql执行链，因此在它们上面加上拦截器，就可以干预sql链的执行。基于此，我们通过修改待执行的sql来完成物理分页。
（本文不讲解mybatis拦截器相关内容）

1. 拦截器

Page:

public class Page {    private int startLine;    private int pageSize;    public Page(int startLine, int pageSize) {        this.startLine = startLine;        this.pageSize = pageSize;    }    public Page() {    }    public int getStartLine() {        return startLine;    }    public void setStartLine(int startLine) {        this.startLine = startLine;    }    public int getPageSize() {        return pageSize;    }    public void setPageSize(int pageSize) {        this.pageSize = pageSize;    }}

Interceptor:

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Map;import java.util.Properties;import com.google.common.base.Strings;import com.google.common.collect.Lists;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.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.scripting.defaults.DefaultParameterHandler;import org.slf4j.Logger;import org.slf4j.LoggerFactory;/** * 通过拦截<code>StatementHandler</code>的<code>prepare</code>方法，重写sql语句实现物理分页。 * <p/> * 详情请见： * 指定该拦截器拦截StatementHandler对象的prepare方法，且方法参数类型为Connection * * @author jinhua.zhang */@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})public class PageHandlerInterceptor implements Interceptor {    /**     * 日志     */    private static final Logger logger = LoggerFactory.getLogger(PageHandlerInterceptor.class);    /**     * 默认ObjectFactory     */    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();    /**     * 默认ObjectWrapperFactory     */    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();    @Override    public Object intercept(Invocation invocation) throws Throwable {        //获得拦截的对象        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();        //待执行的sql的包装对象        BoundSql boundSql = statementHandler.getBoundSql();        //判断是否是查询语句        if (isSelect(boundSql.getSql())) {            //获得参数集合            Object params = boundSql.getParameterObject();            if (params instanceof Map) {   //请求为多个参数，参数采用Map封装                return complexParamsHandler(invocation, boundSql, (Map<?, ?>) params);            } else if (params instanceof Page) {     //单个参数且为Page，则表示该操作需要进行分页处理                return simpleParamHandler(invocation, boundSql, (Page) params);            }        }        return invocation.proceed();    }    /**     * 判断是否是select语句     *     * @param sql sql     * @return true or false     */    private boolean isSelect(String sql) {        return !Strings.isNullOrEmpty(sql)                && sql.toUpperCase().trim().startsWith("SELECT");    }    /**     * 当多个参数时，执行此方法     *     * @param invocation Invocation     * @param boundSql   封装的待执行sql     * @param page       page's information     * @return 执行结果     * @throws Throwable     */    private Object simpleParamHandler(Invocation invocation, BoundSql boundSql, Page page) throws Throwable {        return pageHandlerExecutor(invocation, boundSql, page);    }    /**     * 当多个参数时，执行此方法     *     * @param invocation Invocation     * @param boundSql   封装的待执行sql     * @param params     参数集合     * @return 执行结果     * @throws Throwable     */    private Object complexParamsHandler(Invocation invocation, BoundSql boundSql, Map<?, ?> params) throws Throwable {        //判断参数中是否指定分页        if (containsPage(params)) {            return pageHandlerExecutor(invocation, boundSql, (Page) params.get("page"));        } else {            return invocation.proceed();        }    }    private boolean containsPage(Map<?, ?> params) {        return params != null && params.get("page") != null && params.get("page") instanceof Page;    }    /**     * 执行带有分页信息的查询语句     *     * @param invocation invocation     * @param boundSql   封装的待执行sql     * @param page       分页信息     * @return 执行结果     * @throws Throwable     */    private Object pageHandlerExecutor(Invocation invocation, BoundSql boundSql, Page page) throws Throwable {        //获得数据库连接        Connection connection = (Connection) invocation.getArgs()[0];        //使用Mybatis提供的MetaObject，该对象主要用于获取包装对象的属性值        MetaObject statementHandler = MetaObject.forObject(invocation.getTarget(), DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);        //获取该sql执行的结果集总数        int maxSize = getTotalSize(connection,                (MappedStatement) statementHandler.getValue("delegate.mappedStatement"),                boundSql);        //生成分页sql        String wrapperSql = createPageSql(boundSql.getSql(), page, maxSize);        MetaObject boundSqlMeta = MetaObject.forObject(boundSql, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);        //修改boundSql的sql        boundSqlMeta.setValue("sql", wrapperSql);        return invocation.proceed();    }    /**     * wrap the simple sql to complex sql which with page's information     *     * @param sql     simple sql     * @param page    the information of page     * @param maxSize the size of result     * @return the wrapper of simple sql     */    private String createPageSql(String sql, Page page, int maxSize) {        if (page == null) {            page = new Page(0, 10);        }        int pageSize = page.getPageSize();        int startIndex = page.getStartLine();        //超出范围，则显示最后一页        if (pageSize > maxSize) {            startIndex = 0;        } else {            startIndex = (startIndex > maxSize) ?                    maxSize - maxSize % pageSize : startIndex;        }        return sql + " limit " + startIndex + "," + pageSize;    }    /**     * 从数据库里查询总的记录数并计算总页数     *     * @param connection      数据库连接     * @param mappedStatement mappedStatement     * @param boundSql        封装的待执行sql     * @return 查询结果统计     */    private int getTotalSize(Connection connection, MappedStatement mappedStatement, BoundSql boundSql) {        String countSql = createCountSql(boundSql.getSql());        PreparedStatement countStmt;        ResultSet rs;        List<AutoCloseable> closeableList = Lists.newArrayList();        try {            countStmt = connection.prepareStatement(countSql);            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,                    boundSql.getParameterMappings(), boundSql.getParameterObject());            setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject());            rs = countStmt.executeQuery();            if (rs.next()) {                return rs.getInt(1);            }            closeableList.add(countStmt);            closeableList.add(rs);        } catch (SQLException e) {            logger.error("append an exception[{}] when execute sql[{}] with {}",                    e, countSql, boundSql.getParameterObject());        } finally {            for (AutoCloseable closeable : closeableList) {                try {                    if (closeable != null)                        closeable.close();                } catch (Exception e) {                    logger.error("append an exception[{}] when close resource[{}] ", e, closeable);                }            }        }        return 0;    }    /**     * 生成统计语句     *     * @param sql 待统计的查询语句     * @return 统计语句     */    private String createCountSql(String sql) {        return "select count(*) from (" + sql + ") as total";    }    /**     * 对SQL参数(?)设值     *     * @param ps              PreparedStatement     * @param mappedStatement MappedStatement     * @param boundSql        封装的待执行sql     * @param parameterObject 参数     * @throws SQLException     */    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,                               Object parameterObject) throws SQLException {        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);        parameterHandler.setParameters(ps);    }    @Override    public Object plugin(Object target) {        // 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数        if (target instanceof StatementHandler) {            return Plugin.wrap(target, this);        } else {            return target;        }    }    @Override    public void setProperties(Properties properties) {    }}

2. 使用方式

a. 在mybatis配置文件中配置该拦截器

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration>    <settings>        <setting name="safeRowBoundsEnabled" value="false"/>        <setting name="mapUnderscoreToCamelCase" value="true"/>    </settings>    <plugins>        <plugin interceptor="com.qunar.administrative.interceptors.mybatis.PageHandlerInterceptor"/>    </plugins></configuration>

b. 在编写的Mapper方法中传入Page对象（必须指定参数名字为“page”），如下所示：

@Repositorypublic interface GoodsDao {List<String> selectWithCondition(@Param("page") Page page);}

c. 在Mapper的配置文件写Dao的实现（where不需要携带page信息）

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><mapper namespace="com.qunar.administrative.dao.GoodsDao">    <select id="selectWithCondition" resultType="java.lang.String">        SELECT `address_name` FROM        goods        WHERE  is_delete = 0    </select></mapper>

3. 注意

Mybatis提供了foreach标签用于遍历集合，我们可通过指定item和index来访问集合元素。但是Mybatis在处理item参数时，为它生成了一个不可预测的参数名，在使用Interceptor时会出现异常。因此需使用index来访问集合元素。