[日常踩坑]--(5)MyBatis分页查询带嵌套集合的结果集

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>