mybaits ResultMap 使用collection属性的select参数方式实现嵌套List查询

---

前言

使用了 collection 元素来定义了 select 属性，实际上这个属性会执行一个独立的查询，而不再需要在主查询中使用 LEFT JOIN 来获取关联的数据。

示例

<resultMap id="ResultMap" type="com.laoye.touchFish.entity.User">
   <id column="id" jdbcType="INTEGER" property="id"/>
   <result column="name" jdbcType="VARCHAR" property="name"/>
   <result column="user_name" jdbcType="VARCHAR" property="userName"/>
   <result column="path" jdbcType="VARCHAR" property="path"/>
   <result column="description" jdbcType="VARCHAR" property="description"/>
   <result column="status" jdbcType="TINYINT" property="status"/>
   <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
   <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
   <collection property="posts" ofType="com.laoye.touchFish.entity.Post" column="id" select="selectByUserRecordId"/>
</resultMap>


<resultMap id="postMap" type="com.laoye.touchFish.entity.Post">
   <id column="id" jdbcType="INTEGER" property="id" />
   <result column="user_id" jdbcType="INTEGER" property="userId" />
   <result column="post_name" jdbcType="VARCHAR" property="postName" />
   <result column="path" jdbcType="VARCHAR" property="path" />
   <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
</resultMap>

<select id="selectByUserRecordId" parameterType="java.lang.Integer" resultMap="postMap">
    select
    p.id, p.user_id, p.post_name, p.`path`
    from post p
    where p.user_id = #{id,jdbcType=INTEGER}
</select>

<select id="selectList" resultMap="ResultMap">
    select
    u.id, u.name, u.user_name, u.`path`, u.description, u.`status`, u.update_time, u.create_time
    from user u
    <where>
        <if test="status != null and status != ''">
            and u.`status` = #{status}
        </if>
    </where>
    ORDER BY create_time desc
</select>

public class User {
    private Long id;
    private String name;
    private String username;
    private String path;
    private String description;
    private Integer status;
    private Date createTime;
    private Date updateTime;
    private List<Post> posts;
    // getters and setters
}



public class Post {
    private Long id;
    private Long userId;
    private String postName;
    private String path;
    private Date createTime;
    // getters and setters
}

selectList返回的User数据里面自动包含Post属性的List

总结

• 简化复杂查询，更加清晰
• 优化sql，减少查询时间
• 少量手敲避免报错
• mybatis YYDS