前言
使用了 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