带你手写PageHelper分页插件

1,265 阅读6分钟

1:PageHelper是一个非常好用的插件,以至于很想知道它底层是怎么实现的。至于MyBatis插件概念原理网上有很多,我不太喜欢去写一些概念性的东西,我比较喜欢自己动手实现的那种,话不多说,我们开干

2:搭建一个SpringBoot+MyBatis+MySql项目

  。。。。。,省略

3:编写我们的插件类

package com.example.demo.plugin;


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.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

@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}),
        }
)
public class MyPagePlugin implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameter = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        ResultHandler resultHandler = (ResultHandler) args[3];
        Executor executor = (Executor) invocation.getTarget();
        CacheKey cacheKey;
        BoundSql boundSql;
        //由于逻辑关系,只会进入一次
        if (args.length == 4) {
            //4 个参数时
            boundSql = ms.getBoundSql(parameter);
            cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
        } else {
            //6 个参数时
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }
        
        == 其实在这之上的代码都是拷贝PageHelper源码来的,下面才是重头戏,上面都是获取一些必要的参数==
        /**
        * 下面4行代码暂时只需要知道是用来传参数的就行,分页不是需要
        * 二个参数嘛 一个是当前页,一个是数量
        */
        Page page = ThreadLocalUtil.getPage();
        Map<String,Object> params = new HashMap<>();
        params.put("first_key",page.getPageNum());
        params.put("second_key",page.getPageSize());
        
        /**
        * 重点:获取数据库记录总数
        */
        //统计总数
        Long count = MyExecutorUtil.executeAutoCount(executor,ms,parameter,boundSql,rowBounds,resultHandler);

        /**
        * 重点:分页查询
        */
        List<Object> objects = MyExecutorUtil.pageQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey, params);
        return objects;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

4:上面有二个核心方法

  • 1:获取记录总数:MyExecutorUtil.executeAutoCount(executor,ms,parameter,boundSql,rowBounds,resultHandler);
  • 2:分页查询:MyExecutorUtil.pageQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey,params);

5:获取记录总数

  • 1:MyBatis源码底层会封装好我们在配置文件中写的每一条SQL语句,封装到一个SqlSource对象中去,在我们执行SQL的时候,会获取到这条SQL,然后封装到BoundSql这个对象中,所以在这里,我们既然能拿到BoundSql,那么也就意味着我们能拿到我们需要执行的那条Sql了

  • 2:获取到了我们的sql之后,怎么查询总记录数呢??其实很简单,改Sql语句不就好了,Pagehelper底层也是这么做的,但是PageHelper底层比我这个版本的复杂太多了,但是我们无非就是将原先的SQL转换成 -> SELECT COUNT(0) FROM TABLE,就这样,但是我这个人比较懒,而且昨天看这个源码实现头疼,所以在这里直接写死了,但是问题不大哈

  • 3:改好了Sql之后是不是就完成了呢??当然不是,改好了SQL当然是要去执行它了,如果就这样执行,还是会执行原先的SQL,但是你要知道这个有个很关键的东西,就是MappedStatement的id,如果不改这个id的话,即使你的Sql能够执行成功,那么返回的记录总数是个NULL,这里我想可能是因为ResultSetHandle的关系,因为这个id对应的还是我们之前的sql,也就是select * from student,那么必然有一个resultType的属性,也就是实体类映射,但是我们现在的sql是select count(0) from student,那么就对应不上了,也就是数据库查询出来的列与实体类对应不上,所以我们需要改变这个id。

    //改变MapperStatement id的方法

          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();
              }
    
  • 4:MyBatis的Sql是封装到BoundSql中去的,而原先的BoundSql中的Sql是我们配置文件中的,所以我们需要将select count(0) from student这条Sql语句封装到一个新的BoundSql中去

    public static Long executeAutoCount(Executor executor, MappedStatement countMs,
                                           Object parameter, BoundSql boundSql,
                                           RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
           //改变MapperStatement中的ID属性
           String countMsId = countMs.getId() + "_COUNT";
           countMs = newCountMappedStatement(countMs,countMsId);
           //创建 count 查询的缓存 key
           CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
           //调用获取count的sql
           String countSql = "select count(0) from student";
           //重新封装BoundSql对象
           BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
           //执行 count 查询
           List<Long> countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
           Long count = countResultList.get(0);
           return count;
       }
       
    
  • 5:最后调用executor.query方法就可以得到我们的记录总数了

6:分页查询记录数

  • 1:其实分页查询与查询记录总数的原理是一样的
  • 2:首先获取原先的BoundSql中的Sql,也就是我们在配置文件中的Sql,比如 select * from student
  • 3:然后获取我们的分页参数,也就是前端传递过来的pageNum和pageSize二个参数
  • 4:修改Sql,select * from student limit ?,?
  • 5:这次我们就不用修改MapperStatement中的ID了,因为查询出来的都是studnet,所以映射不用修改
  • 6:重新实例化一个BoundSql对象,将新的Sql传递给它
  • 7:最后执行executor.query方法查询,得到结果

7:如何获取前端传递过来的参数?

  • 1:我这里使用的是ThreadLocal,

8:总结

  • 1:首选我们需要获取到原先需要执行的sql
  • 2:然后修改这条修改,分页查询无非就是总记录数,分页查询的记录数
  • 3:分别执行这二条Sql,得到我们想要的结果

9:源码:就下面这5个类,其它的就是将SpringBoot+MyBatis+MySql日常配置就行了,然后再Controller记得传递一下参数

package com.example.demo.plugin;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;


@Configuration
@MapperScan({"com.example.demo.mapper"})
public class MapperConfig {


    @Bean
    public MyPagePlugin myPagePlugin() {
        return new MyPagePlugin();
    }
}

===============================================================================

package com.example.demo.plugin;

import com.example.demo.entity.Student;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class MyExecutorUtil {

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

    public static  <E> List<E> pageQuery(Executor executor, MappedStatement ms, Object parameter,
                                         RowBounds rowBounds, ResultHandler resultHandler,
                                         BoundSql boundSql, CacheKey cacheKey,Map<String,Object> params) throws SQLException {

        executor.clearLocalCache();

        //生成分页的缓存 key
        CacheKey pageKey = cacheKey;
        //处理参数对象
        if(params.size() < 0 || params.size() > 2) {
            System.out.println("参数错误");
        }
        //获取sql
        String pageSql = getPageSql(params.size(),boundSql);
        List<ParameterMapping> mappingList = new ArrayList<>();
        mappingList.add(new ParameterMapping.Builder(ms.getConfiguration(), "first_key", Integer.class).build());
        mappingList.add(new ParameterMapping.Builder(ms.getConfiguration(), "second_key", Integer.class).build());
        //实例化新的BoundSql对象
        BoundSql pageBoundSql = new BoundSql(ms.getConfiguration(), pageSql, mappingList, params);
        //执行分页查询
        return executor.query(ms, params, RowBounds.DEFAULT, resultHandler, pageKey, pageBoundSql);
    }



    public static Long executeAutoCount(Executor executor, MappedStatement countMs,
                                        Object parameter, BoundSql boundSql,
                                        RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
        //改变MapperStatement中的ID属性
        String countMsId = countMs.getId() + "_COUNT";
        countMs = newCountMappedStatement(countMs,countMsId);
        //创建 count 查询的缓存 key
        CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
        //调用获取count的sql
        String countSql = "select count(0) from student";
        //重新封装BoundSql对象
        BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
        //执行 count 查询
        List<Long> countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
        Long count = countResultList.get(0);
        return count;
    }

    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 static String getPageSql(int paramsLength,BoundSql boundSql){
        StringBuilder str = new StringBuilder();
        str.append(boundSql.getSql());
        if(paramsLength == 1) {
            str.append(" LIMIT ?");
        }else {
            str.append(" LIMIT ?,? ");
        }
        return str.toString();
    }
}

===============================================================================

    package com.example.demo.plugin;


  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.plugin.*;
  import org.apache.ibatis.session.ResultHandler;
  import org.apache.ibatis.session.RowBounds;

  import java.util.HashMap;
  import java.util.List;
  import java.util.Map;
  import java.util.Properties;

  @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}),
          }
  )
  public class MyPagePlugin implements Interceptor {

      @Override
      public Object intercept(Invocation invocation) throws Throwable {
          Object[] args = invocation.getArgs();
          MappedStatement ms = (MappedStatement) args[0];
          Object parameter = args[1];
          RowBounds rowBounds = (RowBounds) args[2];
          ResultHandler resultHandler = (ResultHandler) args[3];
          Executor executor = (Executor) invocation.getTarget();
          CacheKey cacheKey;
          BoundSql boundSql;
          //由于逻辑关系,只会进入一次
          if (args.length == 4) {
              //4 个参数时
              boundSql = ms.getBoundSql(parameter);
              cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
          } else {
              //6 个参数时
              cacheKey = (CacheKey) args[4];
              boundSql = (BoundSql) args[5];
          }
          Page page = ThreadLocalUtil.getPage();
          Map<String,Object> params = new HashMap<>();
          params.put("first_key",page.getPageNum());
          params.put("second_key",page.getPageSize());
          //统计总数
          Long count = MyExecutorUtil.executeAutoCount(executor,ms,parameter,boundSql,rowBounds,resultHandler);
          System.out.println("count = " + count);
          List<Object> objects = MyExecutorUtil.pageQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey, params);
          return objects;
      }



      @Override
      public Object plugin(Object target) {
          return Plugin.wrap(target, this);
      }

      @Override
      public void setProperties(Properties properties) {

      }


  }
  
  

===============================================================================

  package com.example.demo.plugin;

public class Page {

    private Integer pageNum;
    private Integer pageSize;

    public Integer getPageNum() {
        return pageNum;
    }

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

    public Integer getPageSize() {
        return pageSize;
    }

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

===============================================================================

   package com.example.demo.plugin;


  public class ThreadLocalUtil {


      public static ThreadLocal<Page> PAGE_INFO = new ThreadLocal<>();

      public static void startPage(int pageNum,int pageSize) {
          Page page = new Page();
          page.setPageNum(pageNum);
          page.setPageSize(pageSize);
          PAGE_INFO.set(page);
      }

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

  }

===============================================================================

  @RestController
  public class StudnetController {



      @Autowired
      private StudentMapper studentMapper;

      @GetMapping("/getData")
      public Object getData() {
          ThreadLocalUtil.startPage(0,2);
          return studentMapper.getData();
      }
  }