自定义 MyBatis 的分页插件

       目前要使用 MyBatis 的分页插件，大多是引入 pagehelper 依赖。这个插件非常强大，支持的数据库有 hsqldb，h2，postgresql，phoenix，mysql，mariadb，sqlite，oracle，db2，informix，sqlserver，sqlserver2012，derby。而其根本是实现 MyBatis 提供的拦截器 Interceptor ，拦截 Executor 的 query() 方法。本文的思路就是简化了 pagehelper ，但是目前只支持 MySql。

import lombok.Data;
import org.springframework.beans.BeanUtils;

import java.io.Serializable;
import java.util.List;

/**
 * 分页类
 */
@Data
@SuppressWarnings({"rawtypes", "unchecked"})
public class PageInfo<T> implements Serializable {
    protected static final ThreadLocal<PageInfo> LOCAL_PAGE = new ThreadLocal<PageInfo>();
    private int pages; // 总页数
    private int pageSize;// 当前页数量
    private List<T> list;// 当前页数据
    private Long total; // 总条数
    private int pageNum;// 当前页

    public PageInfo(int pageNum, int pageSize) {
        this.pageNum = pageNum;
        this.pageSize = pageSize;
        LOCAL_PAGE.set(this);
    }

    public static void setLocalPageTotal(Long total) {
        PageInfo localPage = getLocalPage();
        localPage.setTotal(total);
        localPage.calculate();
    }

    public PageInfo(List<T> list) {
        PageInfo localPage = getLocalPage();
        localPage.setList(list);
        BeanUtils.copyProperties(localPage,this);
    }

    public static PageInfo getLocalPage() {
        return LOCAL_PAGE.get();
    }

    private void calculate() {
        this.pages = (int) (total / pageSize) + ((total % pageSize) > 0 ? 1 : 0);
        // 如果当前页码超出总页数，自动更改为最后一页
        pageNum = pageNum > pages ? pages : pageNum;
    }
}

import com.xb.testMybatisInterceptor.pojo.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

@Slf4j
@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        }
)
@Component
@SuppressWarnings({"rawtypes", "unchecked"})
public class MyPageHelperExecutorInterceptor implements Interceptor {
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        Executor executor = (Executor) invocation.getTarget();
        // 参数
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameter = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        ResultHandler resultHandler = (ResultHandler) args[3];
        CacheKey cacheKey;
        BoundSql boundSql;
        if (args.length == 4) {
            boundSql = ms.getBoundSql(parameter);
            cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
        } else {
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }
        if (rowBounds != null) {
            // 先将分页参数保存到 ThreadLocal
            int offset = rowBounds.getOffset();
            int limit = rowBounds.getLimit();
            new PageInfo(offset, limit);
            // 分页
            // 先查数量
            Long total = getTotalSize(executor, newCountMappedStatement(ms, ms.getId() + "_COUNT"), parameter, rowBounds, resultHandler, boundSql);
            // 查数据
            if (total <= 0) {
                // 没有查到数据
                return new ArrayList<>();
            }
            PageInfo.setLocalPageTotal(total);
            // 将分页参数转换
            parameter = getExecuteParmeter((HashMap<String, Object>) parameter);
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            // 设置 pageNum 映射
            ParameterMapping.Builder pageNumPmBuilder = new ParameterMapping.Builder(ms.getConfiguration(), "First_PageHelper", Object.class);
            ParameterMapping pageNumPm = pageNumPmBuilder.build();
            parameterMappings.add(pageNumPm);

            // 设置 pageSize 映射
            ParameterMapping.Builder pageSizePmBuilder = new ParameterMapping.Builder(ms.getConfiguration(), "Second_PageHelper", Object.class);
            ParameterMapping pageSizePm = pageSizePmBuilder.build();
            parameterMappings.add(pageSizePm);

            BoundSql countBoundSql = new BoundSql(ms.getConfiguration(), boundSql.getSql()+" limit ? offset ?", boundSql.getParameterMappings(), parameter);

            //执行 count 查询
            return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, countBoundSql);
        }
        // 不分页
        return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
    }

    private static final List<ResultMapping> EMPTY_RESULTMAPPING = new ArrayList<ResultMapping>(0);

    /**
     * 新建count查询的MappedStatement
     */
    public static MappedStatement newCountMappedStatement(MappedStatement ms, String newMsId) {
        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), newMsId, ms.getSqlSource(), ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
            StringBuilder keyProperties = new StringBuilder();
            for (String keyProperty : ms.getKeyProperties()) {
                keyProperties.append(keyProperty).append(",");
            }
            keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
            builder.keyProperty(keyProperties.toString());
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        //count查询返回值int
        List<ResultMap> resultMaps = new ArrayList<ResultMap>();
        ResultMap resultMap = new ResultMap.Builder(ms.getConfiguration(), ms.getId(), Long.class, EMPTY_RESULTMAPPING).build();
        resultMaps.add(resultMap);
        builder.resultMaps(resultMaps);
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());

        return builder.build();
    }

    /***
     * 将分页参数转换
     */
    private Object getExecuteParmeter(HashMap<String, Object> parameter) {
        PageInfo localPage = PageInfo.getLocalPage();
        int pageNum = localPage.getPageNum();
        int pageSize = localPage.getPageSize();
        int offset = (pageNum - 1) * pageSize;
        int limit = pageSize;

        parameter.put("First_PageHelper", limit);
        parameter.put("Second_PageHelper", offset);
        return parameter;
    }

    /**
     * 查询总记录数
     */
    private Long getTotalSize(Executor executor, MappedStatement countMs, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        //创建 count 查询的缓存 key
        CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);

        String countSql = getCountSql(boundSql.getSql());
        BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);

        //执行 count 查询
        Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
        return (Long) ((List) countResultList).get(0);
    }

    /***
     * 获取统计sql
     * @param originalSql 原始sql
     * @return 返回统计加工的sql
     */
    private String getCountSql(String originalSql) {
        // 统一转换为小写
        originalSql = originalSql.trim().toLowerCase();
        // 判断是否存在 limit 标识
        boolean limitExist = originalSql.contains("limit");
        if (limitExist) {
            originalSql = originalSql.substring(0, originalSql.indexOf("limit"));
        }
        boolean distinctExist = originalSql.contains("distinct");
        boolean groupExist = originalSql.contains("group by");
        if (distinctExist || groupExist) {
            return "select count(1) from (" + originalSql + ") temp_count";
        }
        // 去掉 order by
        boolean orderExist = originalSql.contains("order by");
        if (orderExist) {
            originalSql = originalSql.substring(0, originalSql.indexOf("order by"));
        }
        int indexFrom = originalSql.indexOf("from");
        return "select count(*)  " + originalSql.substring(indexFrom);
    }
}

使用起来如 PageHelper 一样方便。

 // 设置分页参数
 RowBounds rowBounds = new RowBounds(page.getPageNum(), page.getPageSize());
 List<AfterOrderAddressDTo> afterOrderAddress = afterOrderDao.getAfterOrderAddress("user", rowBounds);
 // 获取分页结果
 PageInfo<AfterOrderAddressDTo> rest = new PageInfo<AfterOrderAddressDTo>(afterOrderAddress);

使用 Postman 查看结果：