mybatis 一对多级联查询的4种方式

大家都知道mybatis的一对多关联查询collection有两种方式。
1、嵌套resultMap:

<resultMap id="DictionaryResultMap" type="com.xxx.darwin.model.DictionaryQueryModel" >
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="type_code" jdbcType="VARCHAR" property="typeCode" />
    <result column="type_name" jdbcType="VARCHAR" property="typeName" />
    <collection property="detailList" ofType="com.xxx.darwin.model.DictionaryDetailQueryModel">
      <result column="detail_code" jdbcType="VARCHAR" property="detailCode" />
      <result column="detail_name" jdbcType="VARCHAR" property="detailName" />
    </collection>
</resultMap>
  
<select id="listAll" resultMap="DictionaryResultMap">
     select a.id, a.type_code, a.type_name, b.detail_code, b.detail_name
     from bas_dict_header a
     left join bas_dict_detail b on a.type_code = b.type_code
     where a.deleted = 0 and a.enabled = 1 and b.deleted = 0 and b.enabled = 1
 </select>

这种方式其实就是left join 方式查询，collection作用是将多条结果转化成一对多的数据格式。这种方式在分页上会存在问题，
计算总页数会算成N的和，而不是1的和。

2、嵌套select:

<resultMap id="DictionaryResultMap2" type="com.xxx.darwin.model.DictionaryQueryModel">
    <id column="id" jdbcType="BIGINT" property="id"/>
    <result column="type_code" jdbcType="VARCHAR" property="typeCode"/>
    <result column="type_name" jdbcType="VARCHAR" property="typeName"/>
    <collection property="detailList" ofType="com.xxx.darwin.model.DictionaryDetailQueryModel"
                javaType="java.util.List" select="listDetail" column="type_code"/>
</resultMap>

<resultMap id="DictionaryDetailResultMap" type="com.xxx.darwin.model.DictionaryDetailQueryModel">
    <result column="detail_code" jdbcType="VARCHAR" property="detailCode"/>
    <result column="detail_name" jdbcType="VARCHAR" property="detailName"/>
</resultMap>

<select id="listAll2" resultMap="DictionaryResultMap2">
    select a.id, a.type_code, a.type_name
    from bas_dict_header a
    where a.deleted = 0 and a.enabled = 1
</select>

<select id="listDetail" resultMap="DictionaryDetailResultMap">
    select b.detail_code, b.detail_name
    from bas_dict_detail b
    where b.deleted = 0 and b.enabled = 1 and b.type_code = #{typeCode}
</select>

这种方式其实就是1个sql先查出 一级N条数据，再通过N个sql查出每个一级数据type_code关联的二级数据，因此，当N较大时，N+1的查询效率问题就非常明显。此方法只能使用在查询条数较少，或者分页单页条数较少的情况。且这种方式不支持当筛选条件出现在二级数据时的查询。

3、Java map-reduce
要达到分页和效率要求，当然可以使用2条sql先按照一级分页查出一级数据，再查出二级数据，通过java map-reduce组装成想要的数据结构。但如果筛选条件出现在二级数据中，这样分两次查询的方式就行不通了。

4、GROUP_CONCAT
group_concat可以满足一对多级联查询分页、效率和二级筛选条件，查询的二级结果是以逗号连接的字符串，需要用java处理成对应的集合。
当二级筛选条件为空时

SELECT
	a.id,
	a.type_code,
	a.type_name,
	t.detail_code,
	t.detail_name
FROM
	bas_dict_header a
LEFT JOIN (
	SELECT
		group_concat(b.detail_code) AS detail_code,
		group_concat(b.detail_name) AS detail_name,
		b.type_code
	FROM
		bas_dict_detail b
	WHERE
		b.deleted = 0
	AND b.enabled = 1
	GROUP BY
		b.type_code
) t ON a.type_code = t.type_code
WHERE
	a.deleted = 0
AND a.enabled = 1

当二级筛选条件不为空时

SELECT
	a.id,
	a.type_code,
	a.type_name,
	t.detail_code,
	t.detail_name
FROM
	bas_dict_header a
INNER JOIN (
	SELECT
		group_concat(b.detail_code) AS detail_code,
		group_concat(b.detail_name) AS detail_name,
		b.type_code
	FROM
		bas_dict_detail b
	WHERE
		b.deleted = 0
	AND b.enabled = 1
  AND b.type_code = #{typeCode}
	GROUP BY
		b.type_code
) t ON a.type_code = t.type_code
WHERE
	a.deleted = 0
AND a.enabled = 1