mybatis-plus中collection一对多关联查询分页出错问题总结

最近项目里要返回一个下面这种json的数据,用的mybatis-plus框架

sql查数据出来是这种的

要组装成上面的json

首先想到的是用一对多关联查询,一条sql搞定,然后用collection自己组装就可以

 <resultMap id="CheckItemResponse" type="com.xxx.response.subject.CheckItemDtoResponse">
        <id column="id" property="id" />
        <result column="value" property="deviceTypeCodeName" />
        <result column="check_item" property="checkItem" />
        <result column="subject_type" property="subjectType" />
        <result column="subject" property="subject" />
        <collection property="checkItemInfoDtoResponses" ofType=com.xxx.CheckItemInfoDtoResponse">
            <result property="no" column="no"/>
            <result property="option" column="option"/>
        </collection>
    </resultMap>

用上面的collection后数据一切都OK了,后面前端调接口的人找到我说分页是不是有问题??然后我自己测试了一下,分页真的有问题!!!!

{
    "msg": "success",
    "code": 200,
    "data": {
        "total": 4,
        "size": 10,
        "pages": 1,
        "current": 1,
        "records": [
            {
                "id": 4,
                "deviceTypeCode": null,
                "deviceTypeCodeName": null,
                "checkItem": null,
                "subjectType": null,
                "subject": "标题名称",
                "checkItemInfoDtoResponses": [
                    {
                        "id": null,
                        "itemId": null,
                        "no": "a",
                        "option": "正常"
                    }
                ]
            },
            {
                "id": 5,
                "deviceTypeCode": null,
                "deviceTypeCodeName": null,
                "checkItem": null,
                "subjectType": null,
                "subject": "检查外观",
                "checkItemInfoDtoResponses": [
                    {
                        "id": null,
                        "itemId": null,
                        "no": "a",
                        "option": "正常"
                    },
                    {
                        "id": null,
                        "itemId": null,
                        "no": "b",
                        "option": "异常"
                    },
                    {
                        "id": null,
                        "itemId": null,
                        "no": "c",
                        "option": "瞎吃"
                    }
                ]
            }
        ]
    }
}

total应该是2条,结果查出来是4条,后面网上搜索了一下,确实用上面collection这么查会导致分页不正确

所以只好改一下collection就可以了,分成两次查就可以了

<collection property="checkItemInfoDtoResponses" column="id" select="getCheckItemInfo">
            <result property="no" column="no"/>
            <result property="option" column="option"/>
        </collection>

column是关联的id,意思就是根据哪个字段来查询关联表的字段

<select id="getCheckItemInfo" resultType="com.xxx.CheckItemInfoDtoResponse">
        SELECT
        b.no,
        b.option
        FROM
        check_item_info b
        WHERE b.item_id=#{id}
        order by b.sort asc
    </select>

使用上面的collection 分页就可以正常了

会出现N+1问题,所有还是谨慎使用

©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页