Mybatis One-to-many mapping

We can map one-to-many types of results to a collection of objects using the <collection> element.The JavaBeans for Course and Tutor are as follows:

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
}

The <collection> element can be used to map multiple course rows to a list of course objects. Similar to one-to-one mapping, we can map one-to-many relationships using a nested ResultMap and nested Select approaches.

1. One-to-many mapping with nested ResultMap
We can get the tutor along with the courses' details using a nested ResultMap as follows:

<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"/>
 <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>

Here we are fetching the tutor along with the courses' details using a single Select query with JOINS. The <collection> element's resultMap is set to the resultMap ID CourseResult that contains the mapping for the Course object's properties.

2. One-to-many mapping with nested select
We can get the tutor along with the courses' details using a nested select query as follows:

<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>

In this approach, the <association> element's select attribute is set to the statement ID findCoursesByTutor that triggers a separate SQL query to load the courses' details. The tutor_id column value will be passed as input to the findCoursesByTutor statement.