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

女爷i 2023-09-23 16:54 60阅读 0赞

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

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

![639f176b8ccd4928acfa2a40eb39f5bd.png][]

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

<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代码生成器][Mybatis]生成；

（2）定义controller层的查询实体，以及数据库层的查询实体，二者可以使用[Bean转换工具MapStruct][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);

![88e96d8524ce4328b7d49857576a4b79.png][]

改为：

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>

[Mybatis_Interceptor]: https://blog.csdn.net/minghao0508/article/details/124420953
[639f176b8ccd4928acfa2a40eb39f5bd.png]: https://img-blog.csdnimg.cn/639f176b8ccd4928acfa2a40eb39f5bd.png
[Mybatis_SQL]: https://blog.csdn.net/minghao0508/article/details/124406936
[Mybatis]: https://blog.csdn.net/minghao0508/article/details/123733219
[Bean_MapStruct]: https://blog.csdn.net/minghao0508/article/details/123972609
[88e96d8524ce4328b7d49857576a4b79.png]: https://img-blog.csdnimg.cn/88e96d8524ce4328b7d49857576a4b79.png