解决mybatis一对多嵌套查询，解决分页数据少了的问题

转载自：https://blog.csdn.net/zjun1001/article/details/117671517

 

问题
在用mybatis做一对多查询时候，常用collection配合完成结果查询。在不涉及分页查询情况下，查询结果是没有问题的。但当涉及分页查询时，就会出现问题，即结果总数量total多于实际数量。

演示示例如下

实体类

@Data
public class JudgePicPointSatatusResVo {
private Integer id;
private String judgePicPoint;
private String productNum;
private String judgePicStation;
private String stationName;
private List<String> stationNameList;
private Integer status;
}

Dao层

List<JudgePicPointSatatusResVo> getJudgePicPointListByJudgePicStationId(JudgePicPointSatatusReqVo judgePicPointSatatusReqVo);

查询

    <resultMap id="judgePicPointListMap" type="com.qxmz.vo.judgePic.JudgePicPointSatatusResVo">
        <id column="id" property="id"/>
        <result column="judgePicPoint" property="judgePicPoint"/>
        <result column="productNum" property="productNum"/>
        <result column="judgePicStation" property="judgePicStation"/>
        <result column="status" property="status"/>
        <collection property="stationNameList" ofType="String">
            <id column="stationName"/>
        </collection>
    </resultMap>
    <select id="getJudgePicPointListByJudgePicStationId" parameterType="com.qxmz.vo.judgePic.JudgePicPointSatatusReqVo"
            resultMap="judgePicPointListMap">
        SELECT cj.`id`,cj.`productNum`,cj.`status`, jpp.`name` judgePicPoint,jps.`name`
        judgePicStation,st.station_name stationName FROM client_judgePicPoint cj
        LEFT JOIN judge_pic_point jpp ON cj.`judgePicPointId`=jpp.`id`
        LEFT JOIN judge_pic_station jps ON jpp.`judgePicStationId`=jps.`id`
        LEFT JOIN station st ON jps.`id`=st.`judgePicStationId`
        <where>
            cj.isAdd=1
            <if test="judgePicStationIdList!=null">
                and jps.id in
                <foreach item="judgePicStationId" collection="judgePicStationIdList" open="(" separator="," close=")">
                    #{judgePicStationId}
                </foreach>
            </if>
            <if test="status != null and status != 0">
                and cj.status = #{status}
            </if>
        </where>
    </select>

结果

{
    "code": 200,
    "message": "success",
    "data": {
        "total": 2,
        "rows": [
            {
                "id": 2,
                "judgePicPoint": "判图点3",
                "productNum": "002",
                "judgePicStation": "判图站B",
                "stationName": "郑州西站、南阳寨站",
                "stationNameList": [
                    "郑州西站",
                    "南阳寨站"
                ],
                "status": 2
            }
        ]
    }
}

从结果中可以看到，实际只查询出来一条数据，但总数却是2。这就是一对多分页查询会出现的问题。原因也很简单，以上面数据为例。在mysql客户端执行一对多SQL命令时，结果就是两条。一对多情况下，mybatis会自动将结果装备到collection中，但是在分页情况下，mybatis会认为查询的两台数据就是总的数据。

解决方法
采用父子查询来实现，具体代码如下：
父查询

    <resultMap id="judgePicPointListMap" type="com.qxmz.vo.judgePic.JudgePicPointSatatusResVo">
        <id column="id" property="id"/>
        <result column="judgePicPoint" property="judgePicPoint"/>
        <result column="productNum" property="productNum"/>
        <result column="judgePicStation" property="judgePicStation"/>
        <result column="status" property="status"/>
        <collection property="stationNameList" ofType="String" column="judgePicStationId"
                    select="selectStationNameByJudgePicStationId">
        </collection>
    </resultMap>
    <select id="getJudgePicPointListByJudgePicStationId" parameterType="com.qxmz.vo.judgePic.JudgePicPointSatatusReqVo"
            resultMap="judgePicPointListMap">
        SELECT cj.`id`,cj.`productNum`,cj.`status`, jpp.`name` judgePicPoint,jps.`name`
        judgePicStation,jps.id judgePicStationId FROM client_judgePicPoint cj
        LEFT JOIN judge_pic_point jpp ON cj.`judgePicPointId`=jpp.`id`
        LEFT JOIN judge_pic_station jps ON jpp.`judgePicStationId`=jps.`id`
        <where>
            cj.isAdd=1
            <if test="judgePicStationIdList!=null">
                and jps.id in
                <foreach item="judgePicStationId" collection="judgePicStationIdList" open="(" separator="," close=")">
                    #{judgePicStationId}
                </foreach>
            </if>
            <if test="status != null and status != 0">
                and cj.status = #{status}
            </if>
        </where>
    </select>

子查询

<select id="selectStationNameByJudgePicStationId" resultType="String">
        SELECT  station_name FROM station WHERE judgePicStationId=#{judgePicStationId}
    </select>

正确结果

{
    "code": 200,
    "message": "success",
    "data": {
        "total": 1,
        "rows": [
            {
                "id": 2,
                "judgePicPoint": "判图点3",
                "productNum": "002",
                "judgePicStation": "判图站B",
                "stationName": "郑州西站、南阳寨站",
                "stationNameList": [
                    "郑州西站",
                    "南阳寨站"
                ],
                "status": 2
            }
        ]
    }
}