最近项目里要返回一个下面这种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问题,所有还是谨慎使用