mybatis一对多查询使用PageHelper分页问题

在多表联查，尤其是表关系为一对多时用left join联表查，使用PageHelper分页会出现总数据量错误、每页显示条数错误。

先做示例，后面记录需要的知识点

1、示例

1.1 表

b_demo（one） => b_demo1(many)，外键关系为b_demo1.demo_id = b_demo.demo_id

b_demo表

b_demo1表

1.2 两个实体类

import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * @TableName b_demo
 */
@Data
public class Demo implements Serializable {
    private Long demoId;

    private String name;

    private Integer del;

    private List<Demo1> demo1s;

    private static final long serialVersionUID = 1L;
}

import lombok.Data;

import java.io.Serializable;

/**
 * @TableName b_demo1
 */
@Data
public class Demo1 implements Serializable {
    private Long id;
    private Long demoId;

    private String name;

    private static final long serialVersionUID = 1L;
}

1.3 对应mapper.java

public interface DemoMapper{
   public List<Demo> findDemos();
}

1.4 mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sesan.mapper.DemoMapper">

    <resultMap id="BaseResultMap" type="com.sesan.domain.Demo">
            <id property="demoId" column="demo_id" jdbcType="BIGINT"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
            <result property="del" column="del" jdbcType="TINYINT"/>
        <collection property="demo1s" ofType="com.sesan.domain.Demo1" javaType="ArrayList"
                    column="demo_id" select="findDemo1List" >
            <id property="demoId" column="demo_id" jdbcType="BIGINT"/>
            <id property="id" column="id" jdbcType="BIGINT"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="findDemos" resultMap="BaseResultMap">
        select * from b_demo where demo_id = 1
    </select>

    <select id="findDemo1List" resultType="com.sesan.domain.Demo1">
        select * from b_demo1 where demo_id = 1
    </select>

</mapper>

service和controller以及PageHelper就正常写就行.

最终执行的sql日志：

16:33:43.933 [http-nio-9747-exec-3] DEBUG c.r.m.D.findDemos - [debug,137] - ==>  Preparing: select * from b_demo where demo_id = 1
16:33:43.933 [http-nio-9747-exec-3] DEBUG c.r.m.D.findDemos - [debug,137] - ==> Parameters: 
16:33:43.938 [http-nio-9747-exec-3] DEBUG c.r.m.D.findDemo1List - [debug,137] - ====>  Preparing: select * from b_demo1 where demo_id = 1
16:33:43.939 [http-nio-9747-exec-3] DEBUG c.r.m.D.findDemo1List - [debug,137] - ====> Parameters: 
16:33:43.943 [http-nio-9747-exec-3] DEBUG c.r.m.D.findDemo1List - [debug,137] - <====      Total: 3
16:33:43.943 [http-nio-9747-exec-3] DEBUG c.r.m.D.findDemos - [debug,137] - <==      Total: 1

执行的结果

{
    "code": 200,
    "status": "ok",
    "msg": "成功",
    "data": {
        "total": 1,
        "rows": [
            {
                "demoId": 1,
                "name": "1",
                "del": 0,
                "demo1s": [
                    {
                        "id": 1,
                        "demoId": 1,
                        "name": "1.1"
                    },
                    {
                        "id": 2,
                        "demoId": 1,
                        "name": "1.2"
                    },
                    {
                        "id": 3,
                        "demoId": 1,
                        "name": "1.3"
                    }
                ]
            }
        ]
    },
    "timestamp": 1692174823945
}

2、相关知识点介绍

解决分页问题主要是mapper.xml下的resultMap -> collection ；

<resultMap id="BaseResultMap" type="com.sesan.domain.Demo">             <id property="demoId" column="demo_id" jdbcType="BIGINT"/>             <result property="name" column="name" jdbcType="VARCHAR"/>             <result property="del" column="del" jdbcType="TINYINT"/>         <collection property="demo1s" ofType="com.sesan.domain.Demo1" javaType="ArrayList"                     column="demo_id" select="findDemo1List" >             <id property="demoId" column="demo_id" jdbcType="BIGINT"/>             <id property="id" column="id" jdbcType="BIGINT"/>             <result property="name" column="name" jdbcType="VARCHAR"/>         </collection> </resultMap> <select id="findDemos" resultMap="BaseResultMap">         select * from b_demo where demo_id = 1 </select> <select id="findDemo1List" resultType="com.sesan.domain.Demo1">         select * from b_demo1 where demo_id = 1 </select>

mapper.xml里总共有两个sql查询方法，id分别为findDemos、findDemo1List，

collection的作用是执行了分步查询，首先findDemos，执行了查b_demo表，然后再findDemo1List，执行了查b_demo1表，最后将查询结果封装到resultMap里返回。这样在执行分页语句的时候就不会把左连接的表查出的条数加上去了。