Mybatis分頁插件PageHelper手寫實(shí)現(xiàn)示例

更新時(shí)間：2022年08月15日 10:12:06 作者：我是小趴菜

這篇文章主要為大家介紹了Mybatis分頁插件PageHelper手寫實(shí)現(xiàn)示例，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

引言

PageHelper是一個(gè)非常好用的插件，以至于很想知道它底層是怎么實(shí)現(xiàn)的。至于MyBatis插件概念原理網(wǎng)上有很多，我不太喜歡去寫一些概念性的東西，我比較喜歡自己動(dòng)手實(shí)現(xiàn)的那種，話不多說，我們開干

搭建一個(gè)SpringBoot+MyBatis+MySql項(xiàng)目

編寫我們的插件類

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;
        //由于邏輯關(guān)系，只會(huì)進(jìn)入一次
        if (args.length == 4) {
            //4 個(gè)參數(shù)時(shí)
            boundSql = ms.getBoundSql(parameter);
            cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
        } else {
            //6 個(gè)參數(shù)時(shí)
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }
        == 其實(shí)在這之上的代碼都是拷貝PageHelper源碼來的，下面才是重頭戲，上面都是獲取一些必要的參數(shù)==
        /**
        * 下面4行代碼暫時(shí)只需要知道是用來傳參數(shù)的就行，分頁不是需要
        * 二個(gè)參數(shù)嘛 一個(gè)是當(dāng)前頁，一個(gè)是數(shù)量
        */
        Page page = ThreadLocalUtil.getPage();
        Map<String,Object> params = new HashMap<>();
        params.put("first_key",page.getPageNum());
        params.put("second_key",page.getPageSize());
        /**
        * 重點(diǎn)：獲取數(shù)據(jù)庫記錄總數(shù)
        */
        //統(tǒng)計(jì)總數(shù)
        Long count = MyExecutorUtil.executeAutoCount(executor,ms,parameter,boundSql,rowBounds,resultHandler);
        /**
        * 重點(diǎn)：分頁查詢
        */
        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) {
    }
}

上面有二個(gè)核心方法

1：獲取記錄總數(shù)：

MyExecutorUtil.executeAutoCount(executor,ms,parameter,boundSql,rowBounds,resultHandler);

2：分頁查詢：

MyExecutorUtil.pageQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey,params);

獲取記錄總數(shù)

1：MyBatis源碼底層會(huì)封裝好我們?cè)谂渲梦募袑懙拿恳粭lSQL語句，封裝到一個(gè)SqlSource對(duì)象中去，在我們執(zhí)行SQL的時(shí)候，會(huì)獲取到這條SQL，然后封裝到BoundSql這個(gè)對(duì)象中，所以在這里，我們既然能拿到BoundSql,那么也就意味著我們能拿到我們需要執(zhí)行的那條Sql了

2：獲取到了我們的sql之后，怎么查詢總記錄數(shù)呢？？

其實(shí)很簡單，改Sql語句不就好了，Pagehelper底層也是這么做的，但是PageHelper底層比我這個(gè)版本的復(fù)雜太多了，但是我們無非就是將原先的SQL轉(zhuǎn)換成 -> SELECT COUNT(0) FROM TABLE,就這樣，但是我這個(gè)人比較懶，而且昨天看這個(gè)源碼實(shí)現(xiàn)頭疼，所以在這里直接寫死了，但是問題不大哈

3：改好了Sql之后是不是就完成了呢？？

當(dāng)然不是，改好了SQL當(dāng)然是要去執(zhí)行它了，如果就這樣執(zhí)行，還是會(huì)執(zhí)行原先的SQL，但是你要知道這個(gè)有個(gè)很關(guān)鍵的東西，就是MappedStatement的id，如果不改這個(gè)id的話，即使你的Sql能夠執(zhí)行成功，那么返回的記錄總數(shù)是個(gè)NULL，這里我想可能是因?yàn)镽esultSetHandle的關(guān)系，因?yàn)檫@個(gè)id對(duì)應(yīng)的還是我們之前的sql，也就是select * from student,那么必然有一個(gè)resultType的屬性，也就是實(shí)體類映射，但是我們現(xiàn)在的sql是select count(0) from student,那么就對(duì)應(yīng)不上了，也就是數(shù)據(jù)庫查詢出來的列與實(shí)體類對(duì)應(yīng)不上，所以我們需要改變這個(gè)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語句封裝到一個(gè)新的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);
       //創(chuàng)建 count 查詢的緩存 key
       CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
       //調(diào)用獲取count的sql
       String countSql = "select count(0) from student";
       //重新封裝BoundSql對(duì)象
       BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
       //執(zhí)行 count 查詢
       List&lt;Long&gt; countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
       Long count = countResultList.get(0);
       return count;
   }

5：最后調(diào)用executor.query方法就可以得到我們的記錄總數(shù)了

分頁查詢記錄數(shù)

1：其實(shí)分頁查詢與查詢記錄總數(shù)的原理是一樣的
2：首先獲取原先的BoundSql中的Sql，也就是我們?cè)谂渲梦募械腟ql，比如 select * from student
3：然后獲取我們的分頁參數(shù)，也就是前端傳遞過來的pageNum和pageSize二個(gè)參數(shù)
4：修改Sql，select * from student limit ?,?
5：這次我們就不用修改MapperStatement中的ID了，因?yàn)椴樵兂鰜淼亩际莝tudnet，所以映射不用修改
6：重新實(shí)例化一個(gè)BoundSql對(duì)象，將新的Sql傳遞給它
7：最后執(zhí)行executor.query方法查詢，得到結(jié)果

如何獲取前端傳遞過來的參數(shù)？

1：我這里使用的是ThreadLocal，

總結(jié)

1：首選我們需要獲取到原先需要執(zhí)行的sql
2：然后修改這條修改，分頁查詢無非就是總記錄數(shù)，分頁查詢的記錄數(shù)
3：分別執(zhí)行這二條Sql，得到我們想要的結(jié)果

源碼：

就下面這5個(gè)類,其它的就是將SpringBoot+MyBatis+MySql日常配置就行了，然后再Controller記得傳遞一下參數(shù)

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&lt;ResultMapping&gt; EMPTY_RESULTMAPPING = new ArrayList&lt;ResultMapping&gt;(0);
    public static  &lt;E&gt; List&lt;E&gt; pageQuery(Executor executor, MappedStatement ms, Object parameter,
                                         RowBounds rowBounds, ResultHandler resultHandler,
                                         BoundSql boundSql, CacheKey cacheKey,Map&lt;String,Object&gt; params) throws SQLException {
        executor.clearLocalCache();
        //生成分頁的緩存 key
        CacheKey pageKey = cacheKey;
        //處理參數(shù)對(duì)象
        if(params.size() &lt; 0 || params.size() &gt; 2) {
            System.out.println("參數(shù)錯(cuò)誤");
        }
        //獲取sql
        String pageSql = getPageSql(params.size(),boundSql);
        List&lt;ParameterMapping&gt; mappingList = new ArrayList&lt;&gt;();
        mappingList.add(new ParameterMapping.Builder(ms.getConfiguration(), "first_key", Integer.class).build());
        mappingList.add(new ParameterMapping.Builder(ms.getConfiguration(), "second_key", Integer.class).build());
        //實(shí)例化新的BoundSql對(duì)象
        BoundSql pageBoundSql = new BoundSql(ms.getConfiguration(), pageSql, mappingList, params);
        //執(zhí)行分頁查詢
        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);
        //創(chuàng)建 count 查詢的緩存 key
        CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
        //調(diào)用獲取count的sql
        String countSql = "select count(0) from student";
        //重新封裝BoundSql對(duì)象
        BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
        //執(zhí)行 count 查詢
        List&lt;Long&gt; 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 &amp;&amp; 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&lt;ResultMap&gt; resultMaps = new ArrayList&lt;ResultMap&gt;();
        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;
          //由于邏輯關(guān)系，只會(huì)進(jìn)入一次
          if (args.length == 4) {
              //4 個(gè)參數(shù)時(shí)
              boundSql = ms.getBoundSql(parameter);
              cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
          } else {
              //6 個(gè)參數(shù)時(shí)
              cacheKey = (CacheKey) args[4];
              boundSql = (BoundSql) args[5];
          }
          Page page = ThreadLocalUtil.getPage();
          Map&lt;String,Object&gt; params = new HashMap&lt;&gt;();
          params.put("first_key",page.getPageNum());
          params.put("second_key",page.getPageSize());
          //統(tǒng)計(jì)總數(shù)
          Long count = MyExecutorUtil.executeAutoCount(executor,ms,parameter,boundSql,rowBounds,resultHandler);
          System.out.println("count = " + count);
          List&lt;Object&gt; 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&lt;Page&gt; PAGE_INFO = new ThreadLocal&lt;&gt;();
      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();
      }
  }

以上就是Mybatis分頁插件PageHelper手寫實(shí)現(xiàn)示例的詳細(xì)內(nèi)容，更多關(guān)于Mybatis分頁插件PageHelper的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: