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:我这里使用的是ThreadLocal,
源码:
就下面这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(); } }