【Mybatis系列】Mybatis常见的分页方法以及源码理解

Mybatis-Plus的selectPage

1. 引入依赖

        <dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.1</version></dependency>

2. 添加分页插件

@Configuration
public class MybatisConfig {@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));return interceptor;}
}

3. Mapper类和实体类

Mapper类

public interface UserMapper extends BaseMapper<UserInfo> {}

实体类

@Data
@EqualsAndHashCode(callSuper = false)
@TableName(value = "cls_user", autoResultMap = true)
@NoArgsConstructor
public class UserInfo {private static final long serialVersionUID = 1L;@TableId(value = "id", type = IdType.AUTO)private Integer id;@TableField("user_name")private String userName;@TableField("password")private String password;
}

4. 使用方法

    @Overridepublic Page<UserInfo> pageQuery(String name) {return userMapper.selectPage(new Page<>(1,10), new QueryWrapper<>());}

5. 查询示例结果

{"records": [{"id": 1,"userName": "cc","password": "12345"},{"id": 2,"userName": "cc","password": "12345"},{"id": 3,"userName": "cc","password": "12345"}],"total": 11,"size": 3,"current": 1,"orders": [],"optimizeCountSql": true,"searchCount": true,"countId": null,"maxLimit": null,"pages": 4
}

源码解析

MybatisPlusInterceptor#intercept进行查询拦截，遍历其中的InnerInterceptor，核心方法是InnerInterceptor#willDoQuery和InnerInterceptor#beforeQuery。

    public Object intercept(Invocation invocation) throws Throwable {Object target = invocation.getTarget();Object[] args = invocation.getArgs();if (target instanceof Executor) {Executor executor = (Executor)target;Object parameter = args[1];boolean isUpdate = args.length == 2;MappedStatement ms = (MappedStatement)args[0];if (!isUpdate && ms.getSqlCommandType() == SqlCommandType.SELECT) {RowBounds rowBounds = (RowBounds)args[2];ResultHandler resultHandler = (ResultHandler)args[3];BoundSql boundSql;if (args.length == 4) {boundSql = ms.getBoundSql(parameter);} else {boundSql = (BoundSql)args[5];}Iterator var11 = this.interceptors.iterator();while(var11.hasNext()) {InnerInterceptor query = (InnerInterceptor)var11.next();if (!query.willDoQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql)) {return Collections.emptyList();}query.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);}CacheKey cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);}if (isUpdate) {Iterator var8 = this.interceptors.iterator();while(var8.hasNext()) {InnerInterceptor update = (InnerInterceptor)var8.next();if (!update.willDoUpdate(executor, ms, parameter)) {return -1;}update.beforeUpdate(executor, ms, parameter);}}} else {StatementHandler sh = (StatementHandler)target;if (null == args) {Iterator var14 = this.interceptors.iterator();while(var14.hasNext()) {InnerInterceptor innerInterceptor = (InnerInterceptor)var14.next();innerInterceptor.beforeGetBoundSql(sh);}} else {Connection connections = (Connection)args[0];Integer transactionTimeout = (Integer)args[1];Iterator var18 = this.interceptors.iterator();while(var18.hasNext()) {InnerInterceptor innerInterceptor = (InnerInterceptor)var18.next();innerInterceptor.beforePrepare(sh, connections, transactionTimeout);}}}return invocation.proceed();}

PaginationInnerInterceptor#willDoQuery，核心逻辑就是根据参数中的Page对象构造查询总数的sql，进行sql查询，存放到Page对象中。

    public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {IPage<?> page = (IPage)ParameterUtils.findPage(parameter).orElse((Object)null);if (page != null && page.getSize() >= 0L && page.searchCount()) {MappedStatement countMs = this.buildCountMappedStatement(ms, page.countId());BoundSql countSql;if (countMs != null) {countSql = countMs.getBoundSql(parameter);} else {countMs = this.buildAutoCountMappedStatement(ms);String countSqlStr = this.autoCountSql(page, boundSql.getSql());MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);countSql = new BoundSql(countMs.getConfiguration(), countSqlStr, mpBoundSql.parameterMappings(), parameter);PluginUtils.setAdditionalParameter(countSql, mpBoundSql.additionalParameters());}CacheKey cacheKey = executor.createCacheKey(countMs, parameter, rowBounds, countSql);List<Object> result = executor.query(countMs, parameter, rowBounds, resultHandler, cacheKey, countSql);long total = 0L;if (CollectionUtils.isNotEmpty(result)) {Object o = result.get(0);if (o != null) {total = Long.parseLong(o.toString());}}page.setTotal(total);return this.continuePage(page);} else {return true;}}

PaginationInnerInterceptor#beforeQuery，对原有的查询语句进行封装改造，增加limit ?,?

    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {IPage<?> page = (IPage)ParameterUtils.findPage(parameter).orElse((Object)null);if (null != page) {boolean addOrdered = false;String buildSql = boundSql.getSql();List<OrderItem> orders = page.orders();if (CollectionUtils.isNotEmpty(orders)) {addOrdered = true;buildSql = this.concatOrderBy(buildSql, orders);}Long _limit = page.maxLimit() != null ? page.maxLimit() : this.maxLimit;if (page.getSize() < 0L && null == _limit) {if (addOrdered) {PluginUtils.mpBoundSql(boundSql).sql(buildSql);}} else {this.handlerLimit(page, _limit);IDialect dialect = this.findIDialect(executor);Configuration configuration = ms.getConfiguration();DialectModel model = dialect.buildPaginationSql(buildSql, page.offset(), page.getSize());MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);List<ParameterMapping> mappings = mpBoundSql.parameterMappings();Map<String, Object> additionalParameter = mpBoundSql.additionalParameters();model.consumers(mappings, configuration, additionalParameter);mpBoundSql.sql(model.getDialectSql());mpBoundSql.parameterMappings(mappings);}}}

PageHelper.startPage(int pageNum, int pageSize);

1. 引入依赖

        <dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper-spring-boot-starter</artifactId><!-- 特别注意版本问题 --><version>1.4.5</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.2</version></dependency>

2. Mapper类和实体类

Mapper类

public interface UserMapper extends BaseMapper<UserInfo> {}

实体类

@Data
@EqualsAndHashCode(callSuper = false)
@TableName(value = "cls_user", autoResultMap = true)
@NoArgsConstructor
public class UserInfo {private static final long serialVersionUID = 1L;@TableId(value = "id", type = IdType.AUTO)private Integer id;@TableField("user_name")private String userName;@TableField("password")private String password;
}

3. 使用方法

    public PageInfo search() {PageHelper.startPage(1, 3);QueryWrapper<UserInfo> wrapper = new QueryWrapper();List<UserInfo> testEntityList = userMapper.selectList(wrapper);return new PageInfo<UserInfo>(testEntityList);}

4. 运行结果

{"total": 11,"list": [{"id": 1,"userName": "cc","password": "12345"},{"id": 2,"userName": "cc","password": "12345"},{"id": 3,"userName": "cc","password": "12345"}],"pageNum": 1,"pageSize": 3,"size": 3,"startRow": 1,"endRow": 3,"pages": 4,"prePage": 0,"nextPage": 2,"isFirstPage": true,"isLastPage": false,"hasPreviousPage": false,"hasNextPage": true,"navigatePages": 8,"navigatepageNums": [1,2,3,4],"navigateFirstPage": 1,"navigateLastPage": 4
}

源码解析

1. 引入pagehelper-spring-boot-starter，Springboot会扫描jar包中的spring.factories，自动装配机制可以看我这篇文章，原理讲的透透的，SpringBoot自动装配的实现原理。在pagehelper-spring-boot-autoconfigure的jar中有spring.factories，会引入PageHelperAutoConfiguration。

org.springframework.boot.autoconfigure.EnableAutoConfiguration=\
com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration

2. PageHelperAutoConfiguration该类实现了InitializingBean，在启动的时候创建该对象之后会执行afterPropertiesSet，Spring的Bean的生命周期，详细了解可以看这篇文章。该类主要是添加了PageInterceptor进行拦截。

public class PageHelperAutoConfiguration implements InitializingBean {@Overridepublic void afterPropertiesSet() throws Exception {PageInterceptor interceptor = new PageInterceptor();interceptor.setProperties(this.properties);for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();if (!containsInterceptor(configuration, interceptor)) {configuration.addInterceptor(interceptor);}}}
}

3. PageInterceptor#intercept，该方法是拦截查询语句，分别组装查询总数的sql和分页查询的sql。

    public Object intercept(Invocation invocation) throws Throwable {try {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];}checkDialectExists();//对 boundSql 的拦截处理if (dialect instanceof BoundSqlInterceptor.Chain) {boundSql = ((BoundSqlInterceptor.Chain) dialect).doBoundSql(BoundSqlInterceptor.Type.ORIGINAL, boundSql, cacheKey);}List resultList;//调用方法判断是否需要进行分页，如果不需要，直接返回结果if (!dialect.skip(ms, parameter, rowBounds)) {//开启debug时，输出触发当前分页执行时的PageHelper调用堆栈// 如果和当前调用堆栈不一致，说明在启用分页后没有消费，当前线程再次执行时消费，调用堆栈显示的方法使用不安全debugStackTraceLog();//判断是否需要进行 count 查询if (dialect.beforeCount(ms, parameter, rowBounds)) {//查询总数Long count = count(executor, ms, parameter, rowBounds, null, boundSql);//处理查询总数，返回 true 时继续分页查询，false 时直接返回if (!dialect.afterCount(count, parameter, rowBounds)) {//当查询总数为 0 时，直接返回空的结果return dialect.afterPage(new ArrayList(), parameter, rowBounds);}}resultList = ExecutorUtil.pageQuery(dialect, executor,ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);} else {//rowBounds用参数值，不使用分页插件处理时，仍然支持默认的内存分页resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);}return dialect.afterPage(resultList, parameter, rowBounds);} finally {if (dialect != null) {dialect.afterAll();}}}

4. PageHelper.startPage，核心就是组装Page对象存放到线程变量中。那么会导致线程污染吗？拦截器中有remove的操作吗？看到线程变量，最关心的点应该就是使用ThreadLocal可能会导致内存泄露，线程变量ThreadLocal详解，该篇对ThreadLocal的相关知识点讲的明明白白。

    public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {Page<E> page = new Page<E>(pageNum, pageSize, count);page.setReasonable(reasonable);page.setPageSizeZero(pageSizeZero);//当已经执行过orderBy的时候Page<E> oldPage = getLocalPage();if (oldPage != null && oldPage.isOrderByOnly()) {page.setOrderBy(oldPage.getOrderBy());}setLocalPage(page);return page;}

5. PageHelper#skip，PageHelper判断是否需要分页查询。如果Page对象是null，则选择跳过分页查询的逻辑

    public boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {Page page = pageParams.getPage(parameterObject, rowBounds);if (page == null) {return true;} else {//设置默认的 count 列if (StringUtil.isEmpty(page.getCountColumn())) {page.setCountColumn(pageParams.getCountColumn());}autoDialect.initDelegateDialect(ms, page.getDialectClass());return false;}}

PageParams#getPage，会从线程变量中获取Page对象，进行组装

    public Page getPage(Object parameterObject, RowBounds rowBounds) {Page page = PageHelper.getLocalPage();if (page == null) {if (rowBounds != RowBounds.DEFAULT) {if (offsetAsPageNum) {page = new Page(rowBounds.getOffset(), rowBounds.getLimit(), rowBoundsWithCount);} else {page = new Page(new int[]{rowBounds.getOffset(), rowBounds.getLimit()}, rowBoundsWithCount);//offsetAsPageNum=false的时候，由于PageNum问题，不能使用reasonable，这里会强制为falsepage.setReasonable(false);}if (rowBounds instanceof PageRowBounds) {PageRowBounds pageRowBounds = (PageRowBounds) rowBounds;page.setCount(pageRowBounds.getCount() == null || pageRowBounds.getCount());}} else if (parameterObject instanceof IPage || supportMethodsArguments) {try {page = PageObjectUtil.getPageFromObject(parameterObject, false);} catch (Exception e) {return null;}}if (page == null) {return null;}PageHelper.setLocalPage(page);}//分页合理化if (page.getReasonable() == null) {page.setReasonable(reasonable);}//当设置为true的时候，如果pagesize设置为0（或RowBounds的limit=0），就不执行分页，返回全部结果if (page.getPageSizeZero() == null) {page.setPageSizeZero(pageSizeZero);}if (page.getKeepOrderBy() == null) {page.setKeepOrderBy(keepOrderBy);}if (page.getKeepSubSelectOrderBy() == null) {page.setKeepSubSelectOrderBy(keepSubSelectOrderBy);}return page;}

6. PageHelper#afterAll，清除线程变量。所以PageInterceptor已经帮我们把线程变量给清除了，不会产生线程污染。

    @Overridepublic void afterAll() {//这个方法即使不分页也会被执行，所以要判断 nullAbstractHelperDialect delegate = autoDialect.getDelegate();if (delegate != null) {delegate.afterAll();autoDialect.clearDelegate();}clearPage();}

Mybatisplus的PageHelper

1. 引入依赖

        <dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus</artifactId><version>2.1.9</version></dependency>

2. 引入Mybatis配置

@Configuration
public class MybatisPlusConfig {@Beanpublic PaginationInterceptor paginationInterceptor() {PaginationInterceptor paginationInterceptor = new PaginationInterceptor();}

3. 分页查询，PageHelper是com.baomidou.mybatisplus.plugins.pagination.PageHelper。

	@RequestMapping("/list")public Pagination search() {Wrapper wrapper = new EntityWrapper();PageHelper.startPage(1, 10);List<TestEntity> testEntityList = testMapper.selectList(wrapper);Pagination pagination = PageHelper.getPagination();PageHelper.remove();return pagination;}

源码解析

1. PaginationInterceptor#intercept，核心也是从PageHelper中获取线程变量，对象封装存储在线程变量中。

    public Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = (StatementHandler)PluginUtils.realTarget(invocation.getTarget());MetaObject metaObject = SystemMetaObject.forObject(statementHandler);this.sqlParser(metaObject);MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {return invocation.proceed();} else {RowBounds rowBounds = (RowBounds)metaObject.getValue("delegate.rowBounds");if (rowBounds == null || rowBounds == RowBounds.DEFAULT) {if (!this.localPage) {return invocation.proceed();}rowBounds = PageHelper.getPagination();if (rowBounds == null) {return invocation.proceed();}}BoundSql boundSql = (BoundSql)metaObject.getValue("delegate.boundSql");String originalSql = boundSql.getSql();Connection connection = (Connection)invocation.getArgs()[0];DBType dbType = StringUtils.isNotEmpty(this.dialectType) ? DBType.getDBType(this.dialectType) : JdbcUtils.getDbType(connection.getMetaData().getURL());if (rowBounds instanceof Pagination) {Pagination page = (Pagination)rowBounds;boolean orderBy = true;if (page.isSearchCount()) {SqlInfo sqlInfo = SqlUtils.getOptimizeCountSql(page.isOptimizeCountSql(), this.sqlParser, originalSql);orderBy = sqlInfo.isOrderBy();this.queryTotal(this.overflowCurrent, sqlInfo.getSql(), mappedStatement, boundSql, page, connection);if (page.getTotal() <= 0) {return invocation.proceed();}}String buildSql = SqlUtils.concatOrderBy(originalSql, page, orderBy);originalSql = DialectFactory.buildPaginationSql(page, buildSql, dbType, this.dialectClazz);} else {originalSql = DialectFactory.buildPaginationSql((RowBounds)rowBounds, originalSql, dbType, this.dialectClazz);}metaObject.setValue("delegate.boundSql.sql", originalSql);metaObject.setValue("delegate.rowBounds.offset", 0);metaObject.setValue("delegate.rowBounds.limit", 2147483647);return invocation.proceed();}}

总结一下

1. Mybatis-Plus的selectPage是我最喜欢用的，不需要引入额外的jar包；pagehelper-spring-boot-starter中的PageHelper.startPage，封装形式也很简单。

2. Mybatisplus的PageHelper真的是大坑，在项目中我以为是pagehelper-spring-boot-starter类的PageHelper，结果查询结果中pages返回的数据一直是0，跟踪源码发现PageHelper获取的Page对象为null，不进行分页查询。跟踪设置环境变量的方法，发现原来是调用的MybatisPlus老版本的PageHelper，关键的是Mybatis-Plus使用PageHelper和pagehelper-spring-boot-starter使用PageHelper格式会如此的相似。这意味着不看包名，很容易搞混。这种出现一样的类名很容易出现你以为用的是A包，实际上用的是B包的情况。