编码技巧——Mybatis分页插件PageHelper使用、遍历表数据

本篇介绍Mybatis分页插件PageHelper的使用，本质上是Mybatis拦截器起作用，在执行目标SQL之前添加count语句及拼接limit到目标SQL后面，这里不再赘述，Mybatis拦截器相关知识可参考Mybatis——拦截器Interceptor，本篇仅简单介绍如何让一个使用Mybatis+springboot的项目快速接入PageHelper；

背景：接到需求，需要对一个老工程新写几个分页查询及导出的接口；查看了这个工程的代码，发现历史的分页接口竟然是手动拼出来的，非常之丑陋，准备引入Mybatis自带的分页插件，顺带记录下遇到的问题和最终引入PageHelper的步骤；

遇到问题1：根据经验，只需要引入一个PageHelper的二方包，在springboot下，会自动注册Mybatis Interceptor并生效；发现SQL可以正常执行，但是分页参数都没有填充；所以问题应该是PageHelper并没有被正确的加入Mybatis拦截器列表，这一步可以通过打印原始的SQL来验证，如果工程没有开启SQL日志，可参考《编码技巧——Mybatis输出SQL日志》；

解决方案：继续引入pagehelper-spring-boot-starter并在配置类加载PageHelper的Bean；

遇到问题2：按照上述方案成功的引入了pagehelper，并且分页参数都被正常的填充；但是当把分页插件原生的com.github.pagehelper.Page<T>作为返参返回时，发现缺失了分页参数如total、pageNum、pageSize等；发现Page其实仅继承了java.util.ArrayList，因此序列化的时候会当做一个List处理，分页参数都缺失了；

解决方案：自己封装分页返参实体类；

总结下正确引入Mybatis分页插件PageHelper的姿势

（1）引入依赖

以下依赖建议放到dal模块：

        <!--pagehelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.0</version>
        </dependency>

以下依赖建议放到启动/配置boot模块

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>

（2）配置Bean

@Configuration
public class PageHelperConfig {

    @Bean
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("offsetAsPageNum", "true");
        properties.setProperty("rowBoundsWithCount", "true");
        properties.setProperty("reasonable", "true");
        properties.setProperty("dialect", "mysql");
        pageHelper.setProperties(properties);
        return pageHelper;
    }
}

（3）封装返参

/**
 * @author Akira
 * @description 默认的分页查询的封装类
 */
@Data
public class DefaultPageDTO<T> {
    /**
     * 页码
     */
    private Integer pageNum;

    /**
     * 页面大小，默认size为10
     */
    private Integer pageSize;

    /**
     * 总数
     */
    private Long total;

    /**
     * 总页数
     */
    private Integer pages;

    /**
     * 查询记录列表
     */
    private List<T> records;

    /**
     * 将com.github.pagehelper.Page封装成能打印出分页参数的DefaultPageDTO
     *
     * @param page
     * @param <T,R>
     * @return
     */
    public static <T, R> DefaultPageDTO<R> init(Page<T> page, Function<T, R> converter) {
        DefaultPageDTO<R> defaultPageDTO = new DefaultPageDTO<>();
        defaultPageDTO.setPageNum(page.getPageNum());
        defaultPageDTO.setPages(page.getPages());
        defaultPageDTO.setTotal(page.getTotal());
        defaultPageDTO.setPageSize(page.getPageSize());
        if (page.getTotal() > 0) {
            defaultPageDTO.setRecords(page.getResult().stream().map(converter).collect(Collectors.toList()));
        }
        return defaultPageDTO;
    }

}

补充1：条件查询的一般写法

（1）定义好DAO接口、xml、实体，如使用Mybatis代码生成器生成；

（2）定义controller层的查询实体，以及数据库层的查询实体，二者可以使用Bean转换工具MapStruct做转换；数据库层的查询实体可以继承原实体，并附带上一些额外的查询条件，如批量查询、时间区间、排序规则等；示例如下：

@Data
public class UserAuditFlowQuery extends UserAuditFlowDO {

    /**
     * 时间区间
     */
    private Date submitTimeLeft;
    private Date submitTimeRight;

    /**
     * 批量查询
     */
    private List<Long> flowIdList;

    /**
     * 排序规则
     */
    private Boolean orderByFlowId;

}

（3）在xml中使用<if>标签拼接SQL语句

    /**
     * 分页查询
     *
     * @param pageQuery
     * @return
     */
    public DefaultPageDTO<UserAuditFlowDTO> pageQueryMyReading(AuditFlowMyReadingQuery pageQuery) {
        PageHelper.startPage(pageQuery.getPage(), pageQuery.getPageSize());
        final UserAuditFlowQuery query = beanConvertMapper.convert2UserAuditFlowQuery(pageQuery);
        final Page<UserAuditFlowDO> page = userAuditFlowDAO.pageQueryByCondition(query);
        final DefaultPageDTO<UserAuditFlowDTO> defaultPageDTO = DefaultPageDTO.init(page, domain -> beanConvertMapper.convert2UserAuditFlowDTO(domain));
        return defaultPageDTO;
    }

    Page<UserAuditFlowDO> pageQueryByCondition(@Param("condition") UserAuditFlowQuery userAuditFlowQuery);

    <select id="pageQueryByCondition" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user_audit_flow
        <where>
            <if test="condition.title != null and condition.title != ''">
                and `title` like "%"#{condition.title,jdbcType=VARCHAR}"%"
            </if>
            <if test="condition.workNumber != null and condition.workNumber != ''">
                and `work_number` = #{condition.workNumber,jdbcType=VARCHAR}
            </if>
            <if test="condition.roleCode != null and condition.roleCode != ''">
                and `role_code` = #{condition.roleCode,jdbcType=VARCHAR}
            </if>
            <if test="condition.flowId != null">
                and `flow_id` = #{condition.flowId,jdbcType=BIGINT}
            </if>
            <if test="condition.secondSubModuleCode != null and condition.secondSubModuleCode != ''">
                and `second_sub_module_code` = #{condition.secondSubModuleCode,jdbcType=VARCHAR}
            </if>
            <if test="condition.auditStatus != null">
                and `audit_status` = #{condition.auditStatus,jdbcType=INTEGER}
            </if>
            <if test="condition.submitor != null and condition.submitor != ''">
                and `submitor` = #{condition.submitor,jdbcType=VARCHAR}
            </if>
            <if test="condition.submitTimeLeft != null and condition.submitTimeRight != null">
                and `create_time` between #{condition.submitTimeLeft,jdbcType=TIMESTAMP} and
                #{condition.submitTimeRight,jdbcType=TIMESTAMP}
            </if>
        </where>

        <if test="condition.orderByFlowId != null">
            <if test="condition.orderByFlowId == true">
                order by flow_id
            </if>
            <if test="condition.orderByFlowId == false">
                order by flow_id desc
            </if>
        </if>
    </select>

补充2：遍历表数据

使用limit语句遍历表，当表数据量很大时，limit扫描的行数会越来越多，导致查询变慢；比如说limit 10000,10，MYSQL会去查询10010条数据，然后返回最后10条数据；当表数据量很大时，越往后需要查询的数据量越来越大，导致时间越来越长；

常用的解决方案有：

1. 记录主键id，例如顺序查询遍历表数据的场景，从上次查询结果中记录最后一位主键id，在查询语句中使用主键范围条件，减小回表次数；如：

select * from tableA where id > 10000 order by id limit 10;

2. 嵌套子查询，先查主键id，再根据选好的pageSize数量的主键id回表查，减小扫描的数据量；在有二级索引的情况下，查主键id会遍历空间大小更小二级索引树而非聚簇索引树，扫描的速度更快；注意，这种语句可能较低的MySQL版本不支持，可以在代码中分别执行2句SQL或改为使用join语句；

select * from tableA where id in (select id from tableA order by id LIMIT 10000,10);

改为：

select id from audit_flow_main order by id LIMIT 10000,10;
select * from audit_flow_main where id IN (...);

以上语句也可以通过使用join语句改写，原理是一样的：

SELECT * FROM tableA a JOIN (SELECT b.id FROM tableA b order by id LIMIT 5000,10) AS c on a.id = c.id;

注意：如果要按照主键id遍历数据，则需要在limit前加上order by id，否则查出来的数据与扫描主键索引查出来的数据不一致！原因很简单，二级索引有序，二级索引的叶子节点存放的主键id无序；

 3. 使用between语句，如果仅遍历表数据，对每页数据大小没有严格要求时，如执行一些扫描表的定时任务时，推荐使用对主键做between，即手动算出每次查询的主键id区间，这样扫描的行数是最小的，效率也很高；一个定时任务的代码示例如下：

public ExecutionResult execute(ExecutionContext executionContext) {
        long maxId = auditMainFlowMapper.selectMaxId();
		// 执行时就确定好id上限，仅刷新当前时刻的所有数据
        long idRight = maxId;
        long idLeft = Math.max(1, idRight - pageSize + 1);
        log.warn("任务开始");
        while (true) {
            if (idRight <= 1) {
                log.warn("任务跳出");
                break;
            }
            final QueryCondition queryCondition = new QueryCondition();
            queryCondition.setIdLeft(idLeft);
            queryCondition.setIdRight(idRight);
            final List<MyData> pageData = mDataMapper.selectByCondition(queryCondition);
            if (CollectionUtils.isNotEmpty(pageData)) {
                try {
                    doTaskForCurrentPage(pageData);
                } catch (Exception e) {
                    log.warn("当前批次执行失败, e);
                }
            }
			// 翻页
            idRight = idRight - pageSize;
            idLeft = Math.max(1, idRight - pageSize + 1);
        }
        log.warn("任务结束");
        return ExecutionResult.SUCCESS;
    }

    <select id="selectByCondition" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from my_data
        <where>
            <if test="condition.idLeft != null and condition.idRight != null">
                and `id` between #{condition.idLeft,jdbcType=BIGINT} and #{condition.idRight,jdbcType=BIGINT}
            </if>
        </where>
    </select>