MyBatis(3.2.3)

In the sample domain model, a tutor can teach one or more courses. This means that there is a one-to-many relationship between the tutor and course.

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
}

Now let us see how we can get the tutor's details along with the list of courses he/she teaches.
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.

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.

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.

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);
}

A nested select approach may result in N+1 select problems. First, the main query will be executed (1), and for every row returned by the first query, another select query will be executed (N queries for N rows). For large datasets, this could result in poor performance.