200行代码手撸PageHelper分页插件

一、pageHelper原理

        mybatis 有个插件机制，可以支持外部应用进行任意扩展。它在启动的时候会将 interceptor【拦截器】 添加到mybatis的上下文中。

        pageHelper就是使用这个拦截器在查询前对实际查询的sql进行改造。并使用ThreadLocal变量在分页操作与拦截器间共享变量。

源码github：https://github.com/pagehelper/Mybatis-PageHelper

pageHelper 的大体执行框架:

• 先解析各位置参数;
• 初始化 pageHelper 实例, 即 dialect;
• 判断是否要进行count, 如果需要则实现一次count；
• 调用方法判断是否需要进行分页，如果不需要，直接返回结果;
• 查询分页结果;
• 封装带分页的结果返回;

2.1 使用

SpringBoot 集成mybatis pageHelper分页查询_王文萱的博客-CSDN博客

2.2 包结构

二、手撸pageHelper源码

pageHelper做了很多除分页操作之外的其他优化操作，如属性读取配置，缓存实现，工厂模式获取相应数据库方言等。适配了很多奇奇怪怪的DML，为了简单，抽丝剥茧，楼主只实现基础的干路操作。

2.1 包结构

page： 分页对象

pageHelper：通用分页拦截器【面向用户】

pageInterceptor：mybatis 拦截器插件

2.2 page

这是一个普通的POJO对象，拥有分页页数，分页大小，是否需要count等属性，代码很简单，如下

package com.example.teasure.repository.page;

import lombok.Data;

import java.util.ArrayList;

/**
 * @author wenxuan wang
 * @date 2021/12/5 17:16
 * 原本包装了很多功能，这里简化只使用pageNum，pageSize和count参数
 */

@Data
public class Page<E> extends ArrayList<E> {

    private int pageNum;

    private int pageSize;

    private long total;

    private boolean count;

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

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

    public long getTotal() {
        return total;
    }

    public void setTotal(long total) {
        this.total = total;
    }

    public boolean isCount() {
        return count;
    }

    public void setCount(boolean count) {
        this.count = count;
    }

    private int startRow;

    private int endRow;

    private void calculateStartAndEndRow() {
        this.startRow = this.pageNum > 0 ? (this.pageNum - 1) * this.pageSize : 0;
        this.endRow = this.startRow + this.pageSize * (this.pageNum > 0 ? 1 : 0);
    }


    public int getStartRow() {
        return startRow;
    }

    public void setStartRow(int startRow) {
        this.startRow = startRow;
    }

    public int getEndRow() {
        return endRow;
    }

    public void setEndRow(int endRow) {
        this.endRow = endRow;
    }

    public Page(int pageNum, int pageSize, boolean count) {
        super(0);
        this.pageNum = pageNum;
        this.pageSize = pageSize;
        this.count = count;
        calculateStartAndEndRow();
    }

}

 2.3 PageHelper

面向用户的分页接口，用于维护一个ThreadLocal的Page变量，并对其赋值及露出。

package com.example.teasure.repository.page;

/**
 * @author wenxuan wang
 * @date 2021/12/5 17:14
 */
public class  PageHelper{

    private static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal<Page>();

    public static Page getPage() {
        return LOCAL_PAGE.get();
    }

    public static Page startPage(int pageNum,int pageSize,boolean count) {
        Page page = new Page(pageNum, pageSize, count);
        LOCAL_PAGE.set(page);
        return page;
    }

    public static void clearPage() {
        LOCAL_PAGE.remove();
    }



}

2.4 PageInterceptor

实现了Mabatis实现的拦截器接口，核心流程都在这里。拼接处理SQL语句的操作在各数据库会有所不同，可以使用工厂模式做接口，这里为了方便直接使用MYSQL的方言。

package com.example.teasure.repository.page;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SubSelect;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ResultMap;
import org.apache.ibatis.mapping.ResultMapping;
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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.ReflectorFactory;
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 java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;

/**
 * @author wenxuan wang
 * @date 2021/12/5 wenxuan wang
 * MappedStatement 对应mapper中的一个操作结点
 */

@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        }
)
public class PageInterceptor implements Interceptor {

    public static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    public static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    public static final ReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory();

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        BoundSql boundSql = ms.getBoundSql(args[1]);
        RowBounds rowBounds = (RowBounds) args[2];
        ResultHandler resultHandler = (ResultHandler) args[3];
        Object parameter = args[1];
        Page page = PageHelper.getPage();
        if (Objects.isNull(page)) {
            return invocation.proceed();
        }
        //如果需要count，计算出count值
        Long count = -1L;
        if (page.isCount()) {
            count = count(ms, args[1], boundSql, rowBounds, resultHandler, (Executor) invocation.getTarget());
        }
        //这里判断是否需要分页【根据count == 0】
        if (count == 0) {
            Page finalPage = new Page(0,0,false);
            page.setTotal(0L);
            return finalPage;
        }
        System.out.println(count);
        //分页函数
        Executor executor = (Executor) invocation.getTarget();
        CacheKey cacheKey = executor.createCacheKey(ms, args[1], rowBounds, boundSql);
        Field additionalParametersField = null;
        Map<String, Object> additionalParameters = null;
        try {
            //设置动态参数
            Map<String, Object> paramMap = new HashMap<>();
            //动态sql时的判断条件不会出现在ParameterMapping中，但是必须有，所以这里需要收集所有的getter属性
            //TypeHandlerRegistry可以直接处理的会作为一个直接使用的对象进行处理
            boolean hasTypeHandler = ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameter.getClass());
            MetaObject metaObject1= MetaObject.forObject(parameter, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());

            //需要针对注解形式的MyProviderSqlSource保存原值
            if (!hasTypeHandler) {
                for (String name : metaObject1.getGetterNames()) {
                    paramMap.put(name, metaObject1.getValue(name));
                }
            }

            /**
             * 整个一坨都是处理参数
             * 利用反射将limit x,y 中的x，y注入查询语句，并整理已有参数
             */
            if (boundSql.getParameterMappings() != null && boundSql.getParameterMappings().size() > 0) {
                for (ParameterMapping parameterMapping : boundSql.getParameterMappings()) {
                    String name = parameterMapping.getProperty();
                    if (!name.equals("First_PageHelper")
                            && !name.equals("Second_PageHelper")
                            && paramMap.get(name) == null) {
                        if (hasTypeHandler
                                || parameterMapping.getJavaType().equals(parameter.getClass())) {
                            paramMap.put(name, parameter);
                            break;
                        }
                    }
                }
            }


            paramMap.put("First_PageHelper", page.getStartRow());
            paramMap.put("Second_PageHelper", page.getPageSize());

            cacheKey.update(page.getStartRow());
            cacheKey.update(page.getPageSize());

            if (boundSql.getParameterMappings() != null) {
                List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());
                newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), "First_PageHelper", Integer.class).build());
                newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), "Second_PageHelper", Integer.class).build());

                MetaObject metaObject = MetaObject.forObject(boundSql, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
                metaObject.setValue("parameterMappings", newParameterMappings);
            }

            String pageSql = getPageSql(boundSql.getSql(), PageHelper.getPage(), cacheKey);

            additionalParametersField = BoundSql.class.getDeclaredField("additionalParameters");
            additionalParametersField.setAccessible(true);
            additionalParameters = (Map<String, Object>) additionalParametersField.get(boundSql);

            BoundSql pageBoundSql = new BoundSql(ms.getConfiguration(), pageSql, boundSql.getParameterMappings(), paramMap);

            for (String key : additionalParameters.keySet()) {
                pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
            }
            List resultList = executor.query(ms, paramMap, RowBounds.DEFAULT, resultHandler, cacheKey, pageBoundSql);
            Page finalPage = PageHelper.getPage();
            finalPage.addAll(resultList);
            finalPage.setTotal(count);
            return finalPage;
        } catch (NoSuchFieldException | IllegalAccessException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private String getPageSql(String sql, Page page, CacheKey cacheKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
        sqlBuilder.append(sql);
        sqlBuilder.append(" LIMIT ?, ? ");
        return sqlBuilder.toString();
    }

    private Long count(MappedStatement ms, Object param, BoundSql boundSql, RowBounds rb, ResultHandler resultHandler, Executor executor) {
        /**
         * 使用插件
         */
        String countMsId = ms.getId() + "_COUNT";
        //这里可以做个缓存，简化掉
        /**
         * 1)更改msId
         * 2)更改返回值类型，改成Long
         */
        MappedStatement mappedStatement = newCountMappedStatement(ms, countMsId);
        Field additionalParametersField = null;
        Map<String, Object> additionalParameters = null;
        try {
            additionalParametersField = BoundSql.class.getDeclaredField("additionalParameters");
            additionalParametersField.setAccessible(true);
            additionalParameters = (Map<String, Object>) additionalParametersField.get(boundSql);
            CacheKey countKey = executor.createCacheKey(mappedStatement, param, RowBounds.DEFAULT, boundSql);
            //拿到count计数的sql语句
            String countSql = getSmartSql(boundSql.getSql());
            BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), param);
            for (String key : additionalParameters.keySet()) {
                countBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
            }
            Object countResultList = executor.query(mappedStatement, param, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
            Long count = (Long) ((List) countResultList).get(0);
            return count;
        } catch (NoSuchFieldException | IllegalAccessException | SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    /**
     * 这里用了sql解析包
     * 有关更多信息和示例，请参阅此处：https://github.com/JSQLParser/JSQLParser/wiki。
     *
     * @param sql
     * @return
     */
    private String getSmartSql(String sql) {
        Statement stmt = null;
        try {
            stmt = CCJSqlParserUtil.parse(sql);
            Select select = (Select) stmt;
            SelectBody selectBody = select.getSelectBody();
            List<SelectItem> COUNT_ITEM = new ArrayList<SelectItem>();
            COUNT_ITEM.add(new SelectExpressionItem(new Column("count(0)")));
            PlainSelect plainSelect = new PlainSelect();
            SubSelect subSelect = new SubSelect();
            subSelect.setSelectBody(selectBody);
            subSelect.setAlias(new Alias("table_count"));
            plainSelect.setFromItem(subSelect);
            plainSelect.setSelectItems(COUNT_ITEM);
            select.setSelectBody(plainSelect);
            return select.toString();
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        return "";
    }

    private 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, new ArrayList<ResultMapping>(0)).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();
    }

    @Override
    public Object plugin(Object target) {
        //装饰器模式
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        this.setProperties(properties);
    }
}

2.5 使用

使用方法和com.github.pageHelper一样

1）将插件注入到mybatis数据源的sessionFactory中

 2）查询前进行分页

3）效果

使用controller接口测试成功返回student POJO的值