通过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”***），如下所示：

@Repository
public 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来访问集合元素。

一起学习交流呀