Mybatis一对多，分页问题及映射问题

在分页查询结果集中返回List objects;的时候，附加查询其关联表的List ids;

就像这样，查出主表的实体Xxx并加上关联表的ids

被关联的表table3，本文以下内容称为“子表”

@Data
public class XxxVO extends Xxx {
    /** 关联表的rIds */
    private List<String> rIds;
    /** 关联表的rIds数据名称 */
    private List<String> names;
}

用mybatis直接实现映射Object实体类的List rIds，不用分步多查询并代码循环处理

<resultMap id="Result" type="com.xx.entity.xx.Type">
    <id property="id" column="id"></id>
    <result property="name" column="name"></result>
    <collection property="rIds" ofType="String">
        <result property="rId" column="rIds"></result>
    </collection>
</resultMap>
<select id="getList" resultMap="Result">
    SELECT
        t1.id AS id,
        t1.name AS name,
        t.r_id AS rIds
    FROM table1 AS t1
    LEFT JOIN
        (SELECT t2.id, t2.r_id
            FROM table2 AS t2 
            LEFT JOIN table3 AS t3 
            ON t2.r_id = t3.id) AS t
    ON t1.id = t.id
    <where>
        <if test="xxx != null">
            ...
        </if>
    </where>
</select>

表之间的关系： table1为主表，table2是中间表（table1关联table3），table3是要查询的关联数据表《子表》。
当前SQL中，是先查询 子查询，关联表的数据。因为主表与子表是1对多的关系，所以中间表table2会有多条数据，再被主表左连接。

<collection property="rIds" ofType="String">
    <result property="rId" column="rIds"></result>
</collection>

这里，即就是对子表查询出来的字段，映射到实体类中的集合rIds（“rIds”: [“1”,“2”]）;接口返回的结果就是这样的，部分省略

"records": [
                {
                    "id": "1277860509421953026",
                    "name": "name1",
                    "rIds": ["1","2"]
                },
                {
                    "id": "2277860509421953026",
                    "name": "name2",
                    "rIds": ["3","4"]
                }
            ]

然后你会发现，这样的处理（LEFT JOIN关联分页）会有问题！虽然能实现想要的一个效果，但在分页的时候会产生问题，如下图返回的结果集。

其实mybatis在做分页的时候，是对这一整个sql做的分页处理，limit在最后面。

SELECT t1.id AS id, t1.name AS name, t.r_id AS rIds FROM table1 AS t1
    LEFT JOIN
        (SELECT t2.id, t2.r_id
            FROM table2 AS t2 
            LEFT JOIN table3 AS t3 
            ON t2.r_id = t3.id) AS t
    ON t1.id = t.id LIMIT ?,? 

解决方案一、先查询主表，再子查询

<resultMap id="Result" type="com.xx.entity.xx.Type">
    <id property="id" column="id"></id>
    <result property="name" column="name"></result>
    <!-- <collection property="rIds" ofType="String">
        <result property="rId" column="rIds"></result>
    </collection> -->
    <collection property="rIds" ofType="String" select="getRelation" column="id"></collection>
</resultMap>
<!-- 先查询主表 -->
<select id="getList" resultMap="Result">
    SELECT
        t1.id AS id,
        t1.name AS name
    FROM table1 AS t1
    <where>
        <if test="xxx != null">
            ...
        </if>
    </where>
</select>
<!-- 通过主表的Id，查找关联表 -->
<select id="getRelation" resultType="String">
    SELECT t3.id, t3.name
    FROM table2 AS t2
    LEFT JOIN table3 AS t3 
    ON t2.id = t3.id WHERE t2.t1_id = #{id}
</select>

解析：
Service调Mapper的getList，先执行了查主表table1的操作。映射resultMap=“Result”

<select id="getList" resultMap="Result">
    SELECT
        t1.id AS id,
        t1.name AS name
    FROM table1 AS t1
    <where>
        <if test="xxx != null">
            ...
        </if>
    </where>
</select>

resultMap中包含子查询，再去查询

<collection property="rIds" ofType="String" select="getRelation" column="id"></collection>

property 对应实体类中的变量名
ofType集合类型
select子查询对应定义的id=getRelation
column是主表的id传过来的（重要），不然WHERE后面的#{id}，是取不到的

SQL查询简洁log：

c.s.c.s.o.d.G.getList          : ==>  Preparing: select t1.id as id, t1.name as name from table1 as t1 LIMIT ?,? 
c.s.c.s.o.dao.XxxMapper.getRelation    : ====>  Preparing: select t2.id from table2 as t2 LEFT JOIN table3 as t3 ON t2.id = t3.id WHERE t2.t1_id = ?

返回的分页结果是正确的了

结果集也是这样的

"records": [
                {
                    "id": "1277860509421953026",
                    "name": "name1",
                    "rIds": ["1","2"]
                },
                {
                    "id": "2277860509421953026",
                    "name": "name2",
                    "rIds": ["3","4"]
                }
            ]

解决二？、乖乖的分多次SQL查询

1. …
2. …

最后前端需要的是，子表中所有的id和name，so调整返回实体类集合

<resultMap id="Result" type="com.xx.entity.xx.Type">
    <id property="id" column="id"></id>
    <result property="name" column="name"></result>
    <collection property="roles" javaType="java.util.ArrayList" select="getRelation" column="id" ></collection>
</resultMap>
<!-- 先查询主表 -->
<select id="getList" resultMap="Result">
    SELECT
        t1.id AS id,
        t1.name AS name
    FROM table1 AS t1
    <where>
        <if test="xxx != null">
            ...
        </if>
    </where>
</select>
<!-- 通过主表的Id，查找关联表 -->
<select id="getRelation" resultType="com.xx.entity.xx.Type">
    SELECT t3.id, t3.name
    FROM table2 AS t2
    LEFT JOIN table3 AS t3 
    ON t2.id = t3.id WHERE t2.t1_id = #{id}
</select>

前端循环一下处理

data.records.forEach(group => {
  group.roleIds = group.roles.map(role => role.id);
  group.roleNames = group.roles.map(role => role.name);
})

遗留问题：如何能映射子表多个字段的集合，例如：再加一个private List names; 而不返回实体集合，省去（前端/后端）循环提取的操作？？

collection中的property好像就只能绑定一个，查询了半天，暂不晓得能不能直接这么实现

有大佬可以给我指点一下嘛，能不能这样/??用mybatis把id跟name集合一起查询出来并返回，哈哈