在项目中我们很多时候会遇到需要用到多表连接查询的时候,通常这种时候我们的mybatis的
<select>
中并没有一个合适的resultType去接收查询得到的结果,这个时候就需要用到
<resultMap>
示例:
JavaBean(此处省略getter/setter方法)
public class ActivityUserTask {
private Long id;
private Long activityId;
private Long taskId;
private Long matchId;
private Long bUserId;
private Long gUserId;
private Date createTime;
private String bUserName;
private String gUserName;
private Integer type;
private List<ActivityUserTaskPhoto> photos;
}
public class ActivityUserTaskPhoto {
private Long taskId;
private String picId;
private Integer status;
}
XML
<sql id="select_column_sql">
select
a.id as id,
a.task_id as taskId,
a.activity_id as activityId,
a.match_id as matchId,
a.b_user_id as bUserId,
a.g_user_id as gUserId,
a.create_time as createTime,
a.update_time as updateTime,
b1.name as bUserName,
b2.name as gUserName,
c.type as type,
d.pic_id as picId
from m_activity_user_task a
left join m_user_info b1 on a.b_user_id = b1.id
left join m_user_info b2 on a.g_user_id = b2.id
left join m_activity_user_match c on a.match_id = c.id
left join m_activity_user_task_photo d on a.id = d.task_id
</sql>
<resultMap id="userTask" type="ActivityUserTask">
<id property="id" column="id"/>
<result property="taskId" column="taskId"/>
<result property="bUserId" column="bUserId"/>
<result property="gUserId" column="gUserId"/>
<result property="createTime" column="createTime"/>
<result property="bUserName" column="bUserName"/>
<result property="gUserName" column="gUserName"/>
<result property="type" column="type"/>
<collection property="photos" ofType="ActivityUserTaskPhoto">
<result property="picId" column="picId"/>
</collection>
</resultMap>
<select id="findList" parameterType="ActivityUserTask"
resultMap="userTask">
<include refid="select_column_sql" />
order by id desc
</select>
这里有几点需要注意:
1.由于没有一个合适的resultType所以我只有自己定义一个resultMap去接收select到的结果。
2.由于JAVA类中的属性名和数据库中的字段名不一样需要用到别名去与数据库中的表进行映射
3.由于用到了别名所以resultMap中的<result property="taskId" column="taskId"/>
中column也必须等于别名。
4.<collection property="photos" ofType="ActivityUserTaskPhoto">
用于一对多映射,property属性名,ofType为属性所属类。
其中还有id表示主键,result表示非主键字段。
5.<association>
用于一对一映射。