Make a note of the troubleshooting of the PageHelper pagination failure

Problem Description

Recently, I used PageHelper paging tool + Mybatis to implement the paging query logic in the project, but found that the paging logic did not take effect. The code snippets are as follows:

    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);
    }

In this code, the query parameters and paging information are passed in, and the data of the total number of pages and the current page number are returned. However, all data was returned during actual execution.

Repair method

During the investigation, it was found that the count code contained paging parameters in the SQL statement generated by mybatis, but the select statement did not. Therefore, placing the request for querying the data list before calculating the total number of data rows can solve this problem.

    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);
    }

Cause Analysis

Read PageHepler here, and briefly introduce how PageHelper stuffs paging information into query requests.

After calling the PageHelper.startPage method, it will finally enter this logic:

    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);
    }

Here we should pay attention to the setLocalPage method. This line of code puts the paging information into the current thread context LOCAL_PAGE, so that subsequent queries within the same thread can get the paging information from this variable.

So where is this paging information compiled into SQL? The PageHelper tool implements a mybatis interceptor PageInterceptor. When the request passes through the interceptor, it will read the paging information in LOCAL_PAGE and write it into SQL. This is abstracted through the Dialect interface. The Dialect interface defines various behaviors when passing through the aspect (such as whether to skip, pre-execution, post-execution, etc.), and subclass PageHelper to implement paging logic.

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;
    }
}

A comment was added to the key lines above, and you can see that the intercept method is actually to arrange several methods of 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 mainly adds a layer of proxy implementation to PageAutoDialect, the codes of the two are as follows:

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();
    }
}

Summarize

PageHelper is essentially implemented by thread context variables, and only takes effect on the first SQL after setting the paging parameters. Therefore, it is necessary to execute the paged query first, and then execute the count.