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.