问题形容
最近在我的项目中应用PageHelper分页工具+Mybatis实现分页查问逻辑，然而发现分页逻辑并没有失效，代码片段如下：
public PageDTO<ChargeMetaDO> pageByParams(SearchChargeReq searchChargeRequest, int pageNo, int pageSize) { PageHelper.startPage(pageNo, pageSize); ChargePOExample example = buildExample(searchChargeRequest); long total = chargeMapper.countByExample(example); List<ChargeMetaDO> chargeMetaDoList = selectByExample(example); return new PageDTO<>(total, chargeMetaDoList); }这段代码中传入了查问参数和分页信息，并返回总页数和当前页号的数据。然而理论执行的时候返回了全副的数据。
修复形式
排查的时候发现，count代码通过mybatis生成的sql语句中蕴含了分页参数，然而select语句却没有，因而将查问数据列表的申请放在计算总数据行数后面即可解决这个问题。
public PageDTO<ChargeMetaDO> pageByParams(SearchChargeReq searchChargeRequest, int pageNo, int pageSize) { PageHelper.startPage(pageNo, pageSize); ChargePOExample example = buildExample(searchChargeRequest); // 须要先执行分页查问，再查问总数 List<ChargeMetaDO> chargeMetaDoList = selectByExample(example); long total = chargeMapper.countByExample(example); return new PageDTO<>(total, chargeMetaDoList); }起因剖析
这里浏览了一下PageHepler，简略介绍一下PageHelper是如何将分页信息塞入当查问申请中的。
在调用PageHelper.startPage办法后最终会进入这段逻辑：
protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal(); public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) { Page<E> page = new Page(pageNum, pageSize, count); page.setReasonable(reasonable); page.setPageSizeZero(pageSizeZero); Page<E> oldPage = getLocalPage(); if (oldPage != null && oldPage.isOrderByOnly()) { page.setOrderBy(oldPage.getOrderBy()); } setLocalPage(page); return page; } protected static void setLocalPage(Page page) { LOCAL_PAGE.set(page); }这里要关注setLocalPage这个办法，这一行代码将分页的信息放入了以后线程上下文LOCAL_PAGE中，使得后续的同线程内查问能够从该变量中取到分页信息。
那么这个分页信息是在哪里被编入到SQL的呢？PageHelper工具实现了一个mybatis的拦截器PageInterceptor，在申请通过该拦截器时会读取LOCAL_PAGE中的分页信息并写入到SQL中。这里通过Dialect接口进行了形象，Dialect接口定义了在通过该切面时的各种行为（如是否跳过，执行前操作，执行后操作等），并子类PageHelper实现分页逻辑。
public class PageInterceptor implements Interceptor { 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) { boundSql = ms.getBoundSql(parameter); cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql); } else { cacheKey = (CacheKey)args[4]; boundSql = (BoundSql)args[5]; } this.checkDialectExists(); List resultList; // 须要跳过该Dialect if (!this.dialect.skip(ms, parameter, rowBounds)) { // 是否要执行count操作 if (this.dialect.beforeCount(ms, parameter, rowBounds)) { // 计算数据总量 Long count = this.count(executor, ms, parameter, rowBounds, resultHandler, boundSql); // 判断是否须要执行count后的逻辑 if (!this.dialect.afterCount(count, parameter, rowBounds)) { // 无需执行分页，间接返回 Object var12 = this.dialect.afterPage(new ArrayList(), parameter, rowBounds); return var12; } } // 分页查问数据 resultList = ExecutorUtil.pageQuery(this.dialect, executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey); } else { // rowBounds用参数值，不应用分页插件解决 resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql); } // 分页完结，返回后果 Object var16 = this.dialect.afterPage(resultList, parameter, rowBounds); return var16; } finally { // 分页操作的收尾工作 if (this.dialect != null) { this.dialect.afterAll(); } } } private Long count(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException { String countMsId = ms.getId() + countSuffix; Long count; //先判断是否存在手写的 count 查问 MappedStatement countMs = ExecutorUtil.getExistedMappedStatement(ms.getConfiguration(), countMsId); if (countMs != null) { count = ExecutorUtil.executeManualCount(executor, countMs, parameter, boundSql, resultHandler); } else { // 优先获取缓存的SQL语句，缩小从新生成的老本 countMs = msCountMap.get(countMsId); //主动创立 if (countMs == null) { //拼接count语句 countMs = MSUtils.newCountMappedStatement(ms, countMsId); msCountMap.put(countMsId, countMs); } // 执行count语句 count = ExecutorUtil.executeAutoCount(dialect, executor, countMs, parameter, boundSql, rowBounds, resultHandler); } return count; }}上文中对要害行减少了正文，能够看到intercept办法其实就是对Dialect的几个办法进行编排：
public interface Dialect { /** * 跳过 count 和 分页查问 * * @param ms MappedStatement * @param parameterObject 办法参数 * @param rowBounds 分页参数 * @return true 跳过，返回默认查问后果，false 执行分页查问 */ boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds); /** * 执行分页前，返回 true 会进行 count 查问，false 会持续上面的 beforePage 判断 * * @param ms MappedStatement * @param parameterObject 办法参数 * @param rowBounds 分页参数 * @return */ boolean beforeCount(MappedStatement ms, Object parameterObject, RowBounds rowBounds); /** * 执行完 count 查问后 * * @param count 查问后果总数 * @param parameterObject 接口参数 * @param rowBounds 分页参数 * @return true 持续分页查问，false 间接返回 */ boolean afterCount(long count, Object parameterObject, RowBounds rowBounds); /** * 执行分页前，返回 true 会进行分页查问，false 会返回默认查问后果 * * @param ms MappedStatement * @param parameterObject 办法参数 * @param rowBounds 分页参数 * @return */ boolean beforePage(MappedStatement ms, Object parameterObject, RowBounds rowBounds); /** * 生成分页查问 sql * * @param ms MappedStatement * @param boundSql 绑定 SQL 对象 * @param parameterObject 办法参数 * @param rowBounds 分页参数 * @param pageKey 分页缓存 key * @return */ String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey); /** * 分页查问后，解决分页后果，拦截器中间接 return 该办法的返回值 * * @param pageList 分页查问后果 * @param parameterObject 办法参数 * @param rowBounds 分页参数 * @return */ Object afterPage(List pageList, Object parameterObject, RowBounds rowBounds); /** * 实现所有工作后 */ void afterAll(); /** * 设置参数 * * @param properties 插件属性 */ void setProperties(Properties properties);}PageHelper中次要是对PageAutoDialect加了一层代理实现，二者的代码如下：
public class PageHelper extends PageMethod implements Dialect { private PageParams pageParams; private PageAutoDialect autoDialect; @Override public boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) { if (ms.getId().endsWith(MSUtils.COUNT)) { throw new RuntimeException("在零碎中发现了多个分页插件，请查看系统配置!"); } // 获取上下文中的分页信息 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); return false; } } /** * 代理模式 */ @Override public boolean beforeCount(MappedStatement ms, Object parameterObject, RowBounds rowBounds) { return autoDialect.getDelegate().beforeCount(ms, parameterObject, rowBounds); } /** * 代理模式 */ @Override public boolean afterCount(long count, Object parameterObject, RowBounds rowBounds) { return autoDialect.getDelegate().afterCount(count, parameterObject, rowBounds); } @Override public boolean beforePage(MappedStatement ms, Object parameterObject, RowBounds rowBounds) { return autoDialect.getDelegate().beforePage(ms, parameterObject, rowBounds); } @Override public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) { return autoDialect.getDelegate().getPageSql(ms, boundSql, parameterObject, rowBounds, pageKey); } public String getPageSql(String sql, Page page, RowBounds rowBounds, CacheKey pageKey) { return autoDialect.getDelegate().getPageSql(sql, page, pageKey); } @Override public Object afterPage(List pageList, Object parameterObject, RowBounds rowBounds) { //这个办法即便不分页也会被执行，所以要判断 null AbstractHelperDialect delegate = autoDialect.getDelegate(); if (delegate != null) { return delegate.afterPage(pageList, parameterObject, rowBounds); } return pageList; } @Override public void afterAll() { AbstractHelperDialect delegate = autoDialect.getDelegate(); if (delegate != null) { delegate.afterAll(); autoDialect.clearDelegate(); } // 清理线程上下文中的分页信息，避免影响后续的调用 clearPage(); }}public abstract class AbstractHelperDialect extends AbstractDialect implements Constant { /** * 获取分页参数 * * @param <T> * @return */ public <T> Page<T> getLocalPage() { return PageHelper.getLocalPage(); } @Override public final boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) { //该办法不会被调用 return true; } @Override public boolean beforeCount(MappedStatement ms, Object parameterObject, RowBounds rowBounds) { // page配置中要求count Page page = getLocalPage(); return !page.isOrderByOnly() && page.isCount(); } @Override public boolean afterCount(long count, Object parameterObject, RowBounds rowBounds) { Page page = getLocalPage(); page.setTotal(count); if (rowBounds instanceof PageRowBounds) { ((PageRowBounds) rowBounds).setTotal(count); } //pageSize < 0 的时候，不执行分页查问 //pageSize = 0 的时候，还须要执行后续查问，然而不会分页 if (page.getPageSize() < 0) { return false; } return count > ((page.getPageNum() - 1) * page.getPageSize()); } @Override public boolean beforePage(MappedStatement ms, Object parameterObject, RowBounds rowBounds) { Page page = getLocalPage(); // 页数大于0，须要进行分页查问 if (page.isOrderByOnly() || page.getPageSize() > 0) { return true; } return false; } // 该办法会在以后SQL前面增加上分页信息，须要查看MySqlDialect子类的实现 @Override public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) { String sql = boundSql.getSql(); Page page = getLocalPage(); //反对 order by String orderBy = page.getOrderBy(); if (StringUtil.isNotEmpty(orderBy)) { pageKey.update(orderBy); sql = OrderByParser.converToOrderBySql(sql, orderBy); } if (page.isOrderByOnly()) { return sql; } return getPageSql(sql, page, pageKey); } @Override public Object afterPage(List pageList, Object parameterObject, RowBounds rowBounds) { Page page = getLocalPage(); if (page == null) { return pageList; } page.addAll(pageList); if (!page.isCount()) { page.setTotal(-1); } else if ((page.getPageSizeZero() != null && page.getPageSizeZero()) && page.getPageSize() == 0) { page.setTotal(pageList.size()); } else if(page.isOrderByOnly()){ page.setTotal(pageList.size()); } return page; } @Override public void afterAll() { } @Override public String getPageSql(String sql, Page page, CacheKey pageKey) { StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14); sqlBuilder.append(sql); if (page.getStartRow() == 0) { sqlBuilder.append(" LIMIT ? "); } else { sqlBuilder.append(" LIMIT ?, ? "); } return sqlBuilder.toString(); }}总结
PageHelper实质上是通过线程上下文变量来实现，并且仅失效于设置分页参数后的第一条SQL。因而须要先执行分页查问，再执行count。