实现关联表查询

一、 一对一关联（一个班级对应一个老师）

1. 按照结果嵌套查询：使用嵌套结果映射来处理重复的联合结果的子集封装联表查询的数据(去除重复的数据)

<select id="getClass" parameterType="int" resultMap="ClassResultMap">
select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>

<resultMap type="Classess" id="ClassResultMap">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>

result嵌套result

2. 嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型

<select id="getClass2" parameterType="int" resultMap="getClass2Map">
select * from class where c_id=#{id}
</select>

<select id="getTeacher" parameterType="int" resultType="Teacher">
select t_id id,t_name name from teacher where t_id=#{id}
</select>

<resultMap type="Classess" id="getClass2Map" >
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" select="getTeacher">
</association>
</resultMap>

对象用association，集合用collection

二、 一对多关联（一个班级对应多个学生）

<select id="getClass" parameterType="int" resultMap="ClassResultMap">
select * from class c,teacher t,student s where c.teacher_id=t.t_id and s.class_id=c.c_id and c.c_id=#{id}
</select>

<resultMap type="Classess" id="ClassResultMap">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="Teacher"><!-- 关联 -->
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>

<collection property="students" ofType="Student"><!-- 一对多 -->
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>

对象用association，集合用collection