MyBatis 分页插件详解（带源码）

Mybatis的分页功能很弱，它是基于内存的分页（查出所有记录再按偏移量和limit取结果），在大数据量的情况下这样的分页基本上是没有用的。本文基于插件，通过拦截StatementHandler重写sql语句，实现数据库的物理分页。

1.分页参数

既然要分页，肯定得有分页参数吧，下面提供了一个排序、分页的参数类。

package yjc.framework.orm.db;

import java.util.List;

/**
 * Created by yangjiachang on 2016/9/23.
 */
public class Pager<E> {

    /**
     * 每页最大记录数限制
     */
    public static final Integer MAX_PAGE_SIZE = Integer.MAX_VALUE;

    /**
     * 当前页码
     */
    private Integer currentPage = 1;

    /**
     * 每页记录数
     */
    private Integer pageSize = 20;

    /**
     * 总记录数
     */
    private Integer totalCount = 0;

    /**
     * 总页数
     */
    private Integer pageCount = 0;

    /**
     * 数据List
     */
    private List<E> list;

    /**
     * 排序方式，默认为desc
     */
    protected OrderType orderType = OrderType.DESC;
    /**
     * 排序字段
     */
    protected String orderColumns;

    public boolean hasNext() {
        if (this.pageCount > this.currentPage) {
            return true;
        }
        return false;
    }

    public boolean hasForward() {
        if (this.currentPage <= 1) {
            return false;
        }
        return true;
    }

    public Integer getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage < 1 ? 0 : currentPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize < 1 ? 1 : pageSize;
    }

    public Integer getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(Integer totalCount) {
        this.totalCount = totalCount;
        this.pageCount = (totalCount + pageSize - 1) / pageSize;
    }

    public Integer getPageCount() {
        return pageCount;
    }

    public void setPageCount(Integer pageCount) {
        this.pageCount = pageCount;
    }

    public List<E> getList() {
        return list;
    }

    public void setList(List<E> list) {
        this.list = list;
    }

    public OrderType getOrderType() {
        return orderType;
    }

    public void setOrderType(OrderType orderType) {
        this.orderType = orderType;
    }

    public String getOrderColumns() {
        return orderColumns;
    }

    public void setOrderColumns(String orderColumns) {
        this.orderColumns = orderColumns;
    }
}

package yjc.framework.orm.db;

/**
 * Created by yangjiachang on 2016/9/23.
 */
public enum OrderType {
    DESC,ASC;
}

1.MySQL方言处理

各种数据库的SQL方言多少都会有一些不一样，本文以MySQL为例，感兴趣的也可以自己实现Oracle等其他数据库方言。

package yjc.framework.orm.db;

/**
 * Created by yangjiachang on 2016/9/23.
 */
public interface Dialect {

    /**
     * 将sql转换为分页SQL
     *
     * @param sql    SQL语句
     * @param offset 开始条数
     * @param limit  每页显示多少纪录条数
     * @return 分页查询的sql
     */
    String getLimitString(String sql, int offset, int limit);


    /**
     * 将sql转换为排序SQL
     *
     * @param sql
     * @param orderColumns 排序的列，多个由逗号隔开
     * @param orderType 排序类型
     * @return
     */
    String getOrderString(String sql, String orderColumns, OrderType orderType);
}

package yjc.framework.orm.db;

/**
 * Created by yangjiachang on 2016/9/23.
 */
public class MySqlDialect implements Dialect {
    @Override
    public String getLimitString(String sql, int offset, int limit) {
        StringBuilder stringBuilder = new StringBuilder(sql);
        stringBuilder.append(" limit ");
        if (offset > 0) {
            stringBuilder.append(offset).append(",").append(limit);
        } else {
            stringBuilder.append(limit);
        }
        return stringBuilder.toString();
    }

    @Override
    public String getOrderString(String sql, String orderColumns, OrderType orderType) {
        return new StringBuilder(sql).append(" order by ").append(orderColumns).append(" ")
                .append(orderType.toString()).toString();
    }
}

3.拦截器

重写SQL，实现业务逻辑的地方

package yjc.framework.orm.db;


import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.BaseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.springframework.util.ReflectionUtils;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.Map;
import java.util.Properties;

/**
 * Created by yangjiachang on 2016/9/23.
 */
//从签名里可以看出，要拦截的目标类型是StatementHandler（注意：type只能配置成接口类型），拦截的方法是名称为prepare参数为Connection类型的方法。
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class QueryInterceptor implements Interceptor,Serializable{

    private static final long serialVersionUID = 1L;

    protected Dialect DIALECT = new MySqlDialect();

    /**
     * 对ID做正则匹配，只对query开头的方法进行处理
     */
    protected String _SQL_PATTERN = ".*query.*";

    /**
     * 真正实现拦截器业务逻辑的方法
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //StatementHandler的默认实现类是RoutingStatementHandler，因此拦截的实际对象是它。
        // RoutingStatementHandler的主要功能是分发，它根据配置Statement类型创建真正执行数据库操作的StatementHandler，
        // 并将其保存到delegate属性里。
        if (invocation.getTarget() instanceof RoutingStatementHandler) {
            RoutingStatementHandler statementHandler = (RoutingStatementHandler) invocation.getTarget();
            BaseStatementHandler delegate =
                    (BaseStatementHandler) getField(RoutingStatementHandler.class, "delegate").get(statementHandler);
            MappedStatement mappedStatement =
                    (MappedStatement) getField(BaseStatementHandler.class, "mappedStatement").get(delegate);
            //重新需要分页的SQL
            if (mappedStatement.getId().matches(_SQL_PATTERN)) {
                BoundSql boundSql = delegate.getBoundSql();
                String originalSql = boundSql.getSql();
                if(StringUtils.isBlank(originalSql)){
                    return invocation.proceed();
                }

                Map parameterObject = (Map) boundSql.getParameterObject();
                //查询参数--上下文传参
                Pager pager = getPager(parameterObject.get("pager"));
                if (pager != null) {
                    //处理排序
                    originalSql = generateOrderSql(originalSql, pager, DIALECT);
                    //处理分页
                    String pageSql = generatePageSql(originalSql, pager, DIALECT);
                    //赋值，将新的SQL覆盖原SQL
                    setFieldValue(boundSql, "sql", pageSql);
                }
            }
        }
        //交给下一个拦截器
        return invocation.proceed();
    }

    /** 这几个私有方法可以单独提取出去，在SQL处理类里面，这里放在一个类里是为了方便查看 */

    private Field getField(Class<?> clazz, String name){
        Field field = ReflectionUtils.findField(clazz, name);
        field.setAccessible(true);
        return field;
    }

    private Pager getPager(Object object){
        if (object instanceof Pager){
            return (Pager)object;
        }
        return null;
    }

    private String generateOrderSql(String sql,Pager pager,Dialect dialect){
        if (StringUtils.isBlank(pager.getOrderColumns())){
            return sql;
        }
        return dialect.getOrderString(sql, pager.getOrderColumns(),pager.getOrderType());
    }

    private String generatePageSql(String sql,Pager pager,Dialect dialect){
        int pageSize = pager.getPageSize();
        int index = (pager.getCurrentPage() - 1) * pageSize;
        int start = index < 0 ? 0 : index;
        return dialect.getLimitString(sql, start, pageSize);
    }

    private void setFieldValue(Object object,String fieldName,Object value){
        try {
            Field field = object.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            field.set(object,value);
        } catch (NoSuchFieldException | IllegalAccessException e) {
            //这里不可能抛出异常
            e.printStackTrace();
        }
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            //目标属于StatementHandler时才包装该类
            return Plugin.wrap(target, this);
        } else {
            //否则，直接返回目标类，减少代理次数
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

4.MyBatis配置中加入分页插件

<bean id="demoSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="demoDatasource"/>
    <property name="typeAliasesPackage" value="yjc.demo.entities"/>
    <property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/>
    <property name="mapperLocations" value="classpath:mappers/*/*Dao.xml"/>
    <property name="plugins">
        <array>
            <!--page interceptor-->
            <bean class="yjc.framework.orm.db.QueryInterceptor"/>
        </array>
    </property>
</bean>

4.dao层代码这里就不写了，根据自己的业务写就好了

5.测试代码

@Test
public void pageTest(){
    User user = new User();
    user.setGender("MALE");
    user.setStatus(1);
    Pager<User> pager = new Pager<>();
    pager.setCurrentPage(2);
    pager.setPageSize(5);
    pager.setOrderColumns("id");
    pager.setOrderType(OrderType.DESC);
    List<User> list =  userDao.query(pager ,user);
    list.forEach(e -> System.out.println(e.getId()));
}

6.运行结果

44
43
42
41
40