MyBatis系列之高级结果映射之一对多查询

1. 一对多关系

教师可以教一个或多个的课程，这就意味着教师与课程之间的关系是一对多的。我们可以运用元素<collection>来获取一对多的关系。

教师的表信息如下：

STUD_ID	NAME	E-MAil	PHONE	ADDR_ID

课程表信息如下：

COURSE_ID	NAME	DESCRIPTION	START_DATA	END_DATA	TUTOR_ID

public class Course {  
    private Integer courseId;  
    private String name;  
    private String description;  
    private Date startDate;  
    private Date endDate;  
    private Integer tutorId;  
    //setters & getters  
}  
public class Tutor {  
    private Integer tutorId;  
    private String name;  
    private String email;  
    private Address address;  
    private List<Course> courses;  
    //setters & getters  
}  

现在让我们来看一下如何难过教师的信息来获取更多的对应的课程信息。我们可以应用<collection>元素来映射多个课程的行的信息。与one-to-one的映射一样，我们可能应用Nested ResultMap和Nested Select来处理one-to-many。

2. 使用Nested ResultMap

我们可以应用下面的例子，来获取教师的信息连带着他所关联的课程信息。

<resultMap type="Course" id="CourseResult">  
    <id column="course_id" property="courseId"/>  
    <result column="name" property="name"/>  
    <result column="description" property="description"/>  
    <result column="start_date" property="startDate"/>  
    <result column="end_date" property="endDate"/>  
</resultMap>  

<resultMap type="Tutor" id="TutorResult">  
    <id column="tutor_id" property="tutorId"/>  
    <result column="tutor_name" property="name"/>  
    <result column="email" property="email"/>  
    <association property="address" resultMap="AddressResult"/> 
    <collection property="courses" resultMap="CourseResult"/>  
</resultMap>  

<select id="findTutorById" parameterType="int" resultMap="TutorResult">  
SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL, C.COURSE_ID,  
C.NAME, DESCRIPTION, START_DATE, END_DATE  
FROM TUTORS T LEFT OUTER JOIN ADDRESSES A ON T.ADDR_ID=A.ADDR_ID  
LEFT OUTER JOIN COURSES C ON T.TUTOR_ID=C.TUTOR_ID  
WHERE T.TUTOR_ID=#{tutorId}  
</select> 

3. 使用 Nested Select

我们可能使用select的查询，获取教师的信息关联他的课程信息。

<resultMap type="Course" id="CourseResult">  
    <id column="course_id" property="courseId"/>  
    <result column="name" property="name"/>  
    <result column="description" property="description"/>  
    <result column="start_date" property="startDate"/>  
    <result column="end_date" property="endDate"/>  
</resultMap>  

<resultMap type="Tutor" id="TutorResult">  
    <id column="tutor_id" property="tutorId"/>  
    <result column="tutor_name" property="name"/>  
    <result column="email" property="email"/>  
    <association property="address" resultMap="AddressResult"/>  
    <collection property="courses" column="tutor_id" select="findCoursesByTutor"/>  
</resultMap>  

<select id="findTutorById" parameterType="int" resultMap="TutorResult">  
SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL  
FROM TUTORS T WHERE T.TUTOR_ID=#{tutorId}  
</select>  

<select id="findCoursesByTutor" parameterType="int" resultMap="CourseResult">  
SELECT * FROM COURSES WHERE TUTOR_ID=#{tutorId}  
</select>  

在这个方法中，这个<association>元素的select有名称放到了<select>的id中。所以执行上面的语句将会执行两条SQL语句，一个是findCourseByTutor,另一个是findCourseByTutor.

public interface TutorMapper {  
    Tutor findTutorById(int tutorId);  
}  

TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);  
Tutor tutor = mapper.findTutorById(tutorId);  
System.out.println(tutor);  
List<Course> courses = tutor.getCourses();  
for (Course course : courses) {  
    System.out.println(course);  
}