Mybatis分頁插件PageHelper手寫實現示例
引言
PageHelper是一個非常好用的插件,以至於很想知道它底層是怎麼實現的。至於MyBatis插件概念原理網上有很多,我不太喜歡去寫一些概念性的東西,我比較喜歡自己動手實現的那種,話不多說,我們開幹
搭建一個SpringBoot+MyBatis+MySql項目
編寫我們的插件類
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) { } }
上面有二個核心方法
1:獲取記錄總數:
MyExecutorUtil.executeAutoCount(executor,ms,parameter,boundSql,rowBounds,resultHandler);
2:分頁查詢:
MyExecutorUtil.pageQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey,params);
獲取記錄總數
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方法就可以得到我們的記錄總數瞭
分頁查詢記錄數
- 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方法查詢,得到結果
如何獲取前端傳遞過來的參數?
1:我這裡使用的是ThreadLocal,
總結
- 1:首選我們需要獲取到原先需要執行的sql
- 2:然後修改這條修改,分頁查詢無非就是總記錄數,分頁查詢的記錄數
- 3:分別執行這二條Sql,得到我們想要的結果
源碼:
就下面這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(); } }
以上就是Mybatis分頁插件PageHelper手寫實現示例的詳細內容,更多關於Mybatis分頁插件PageHelper的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- 解決mybatis分頁插件PageHelper導致自定義攔截器失效
- Mybatis第三方PageHelper分頁插件的使用與原理
- 詳解Mybatis的緩存
- MyBatis自定義SQL攔截器示例詳解
- MyBatis攔截器的實現原理