Mybatis Plus自动开启关闭分页 - 仅统计总数

一、背景

Mybatis Plus支持分页，但是需要在前端判断是否开启分页、是否统计总数，不支持仅统计总数

在大数据的分页查询时，​​limit​​​和​​count​​常常有性能问题，需要根据容量评估来进行设计

常见的解决方式

1. 将数据按字段排序，然后依据字段 > offsetId，滚动查询，不返回​​count​​总数
2. 嵌套子查询获取offsetId（仅mysql）

等等等

本文仅讨论如何在​​Mybatis Plus​​中一个接口内实现分页、查全部、查总数

二、实现

接着上次文章Mybatis Plus自定义分页（自动开闭分页）

1. 定义扩展​​PageParam​​

@Data
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
public class PageParam<T> extends Page<T> {

    private static final long serialVersionUID = -3669295788865936942L;

    private Integer isOpenPage = 1;
    private Integer isSearchRecord = 1;

    @Override
    public long getSize() {
        if (1 == isOpenPage) {
            return super.getSize();
        }
        return -1;
    }

    @Override
    public long getTotal() {
        if (1 == isOpenPage) {
            return super.getTotal();
        }
        return getRecords().size();
    }
}

2. 扩展​​PaginationInnerInterceptor​​

@NoArgsConstructor
public class PaginationExInnerInterceptor extends PaginationInnerInterceptor {

    /**
     * 这里进行count,如果count为0这返回false(就是不再执行sql了)
     */
    @Override
    public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);
        if (page == null || page.getSize() < 0 || !page.isSearchCount()) {
            return isSearchRecord(page);
        }

        BoundSql countSql;
        MappedStatement countMs = buildCountMappedStatement(ms, page.countId());
        if (countMs != null) {
            countSql = countMs.getBoundSql(parameter);
        } else {
            countMs = buildAutoCountMappedStatement(ms);
            String countSqlStr = autoCountSql(page.optimizeCountSql(), boundSql.getSql());
            PluginUtils.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);
        Object result = executor.query(countMs, parameter, rowBounds, resultHandler, cacheKey, countSql).get(0);
        page.setTotal(result == null ? 0L : Long.parseLong(result.toString()));

        return isSearchRecord(page) && continuePage(page);
    }

    private boolean isSearchRecord(IPage<?> page) {
        if (!(page instanceof PageParam)) {
            return true;
        }

        return BaseIsField.YES.val.equals(((PageParam<?>) page).getIsSearchRecord());
    }
}

3. 优化

重写​​willDoQuery​​固然好，但当mybatis-plus升级版本修改了willDoQuery方法，那么存在不可控变化， 在修改内核代码时，最优的方式应该是扩展，而不是重写，故优化代码为

@NoArgsConstructor
public class PaginationExInnerInterceptor extends PaginationInnerInterceptor {

    /**
     * 这里进行count,如果count为0这返回false(就是不再执行sql了)
     */
    @Override
    public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        boolean willDoQuery = super.willDoQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);
        IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);
        if (page == null) {
            return willDoQuery;
        }
        return isSearchRecord(page) && willDoQuery;
    }

    private boolean isSearchRecord(IPage<?> page) {
        if (!(page instanceof PageParam)) {
            return true;
        }

        return BaseIsField.YES.val.equals(((PageParam<?>) page).getIsSearchRecord());
    }


}