Author:赵志乾
Date:2019-07-24
Declaration:All Right Reserved!!!
踩坑:
场景:分页查询带嵌套结果集合的Vo,如下:
// 结果记录
package com.zhaozhiqian;
import java.util.List;
public class NodeVo {
private String id;
private String name;
private List<NodeVo> children;
// getter 和setter
}
做法:使用MyBatis的resultMap进行结果集映射,如下:
<!-- 映射配置 -->
<resultMap type="com.zhaozhiqian.NodeVo" id="nodeVoId">
<result column="pid" property="id" jdbcType="VARCHAR"/>
<result column="pname" property="name" jdbcType="VARCHAR"/>
<collection property="children" javaType="list" ofType="com.zhaozhiqian.NodeVo">
<result column="cid" property="id" jdbcType="VARCHAR"/>
<result column="cname" property="name" jdbcType="VARCHAR"/>
</collection>
</resultMap>
<!-- sql查询 -->
<select id="queryNodes" parameterType="java.util.Map" resultMap="nodeVoId">
select
p.id as pid,
p.name as pname,
c.id as cid,
c.name as cname
from tb_node as p left join tb_node as c
on p.id=c.parent_id
where p.type=#{type}
order by p.create_time desc, p.id, c.create_time desc, c.id
limit #{index} ,#{num}
</select>
现象:查询到的记录条数和指定条数不一致。如查询10条,结果只有5条(每条children长度都为2)。该问题是由MyBatis结果集映射发生于sql执行完之后所导致的。即sql确实返回了10条记录,但MyBatis在进行结果集映射时进行了记录的合并,导致记录条数和所查条数不一致的问题。
解决方案:
方案1:分两层查询--先用分页查询父节点信息,之后循环调用数据库查询填充子节点信息。该方案比较耗时,并且会增加数据库的压力,所以不建议采用。
方案2:使用group_concat将子节点信息拼接成字符串,查询后进行反序列化。如下:
// 结果记录
package com.zhaozhiqian;
import java.util.List;
public class NodeVo {
private String id;
private String name;
private List<NodeVo> children;
private String childrenStr;
// getter 和setter
}
<!-- 映射配置 -->
<resultMap type="com.zhaozhiqian.NodeVo" id="nodeVoId">
<result column="pid" property="id" jdbcType="VARCHAR"/>
<result column="pname" property="name" jdbcType="VARCHAR"/>
<result column="childrenStr" property="childrenStr" jdbcType="VARCHAR"/>
</resultMap>
<!-- sql查询 -->
<select id="queryNodes" parameterType="java.util.Map" resultMap="nodeVoId">
select
p.id as pid,
p.name as pname,
concat('[', group_concat('{"id":',c.id,'","name":"',c.name,'"}'),']' ) as childrenStr
from tb_node as p left join tb_node as c
on p.id=c.parent_id
where p.type=#{type}
group by p.id
order by p.create_time desc, p.id
limit #{index} ,#{num}
</select>