iBatis Mapping for One-to-Many Relationship

Perhaps you had a problem, like I did, understanding the iBatis writeup on loading complex collection properties, i.e. populating a list-based field in a domain object where that list is the many-side of a one-to-many relationship. I'll admit it took me a few iterations to totally grasp what was being said, so for my future reference and yours, I'll write down my notes here.

Here's the problem - you have an object which has some scalar fields, and additionally has a complex field (in this case, a list) with zero or more objects. This is the object representation of a one-to-many relationship - let's take for example a student attending several courses:

public class Student {
    private String name;
    private String email; // primary key, for sake of argument
    private List courses;

And the courses have various other scalar fields, for example:

public class Course {
    private String name;
    private String id;  // primary key

I omit a list in the Course class identifying the students in that course, since that's in fact a many-to-many relationship - the subject of a different post.

Interestingly, the problem I solved that provoked this post was (in the database) a many-to-many relationship - but in terms of what the application needed (i.e., what queries needed to be answered), I only needed to support the one-to-many aspect. I saved a lot of trouble here by looking at the problem to be solved - instead of looking only at the database structure and myopically trying to provide general support at the iBatis level. In either event, the mechanism I provide here is used when the database provides an association table (in this case, student_to_course) between the two entities (student and course). That table takes the primary key from each entity (the student's email and the course's ID) to specify the associations.

The goal is simply to produce a student and the courses that student is taking. Here's my SQL map file:

<sqlMap namespace="Students">

    <!-- declare aliases for convenience -->
    <typeAlias alias="Student" type="com.mybiz.Student"/>
    <typeAlias alias="Course" type="com.mybiz.Course"/>

    <!-- reusable as an include statement in various statements -->
    <sql id="all-course-fields">
        name, id from COURSE
    </sql>

    <!-- get courses for a given student email -->
    <select
        id="get-courses-for-student"
        parameterClass="string"
        resultClass="Course">
        select
        <include refid="all-course-fields"/>
        where course_id in
        (select course_id from student_to_course where student_email in
        (select student_email from student where student_email = #value#)
        )
    </select>

    <!-- populate student object including its list of courses -->
    <resultMap id="student-with-courses" class="Student">
        <result property="name" column="student_name"/>
        <result property="email" column="student_email"/>
        <result property="courses" column="student_email"" select="get-courses-for-student"/>
    </resultMap>

    <!-- get a student by email, including its list of courses -->
    <select
        id="select-by-email-with-courses"
        parameterClass="string"
        resultMap="student-with-courses">
            select * from STUDENT where email = #value#
    </select>

</sqlMap>

Simple enough. What I needed to understand in the above was that the student_email column in the result map is fed to the get-courses-for-student SELECT statement as the #value#. Before my "a-ha" moment on that, I'll admit it was a longer time than I wanted to finally "get" this mapping idiom.

You might have noticed that I used an includable SQL snippet to specify the COURSE fields, but I did not do this for the STUDENT fields - and that was intentional. What I found was that the subselect approach simply didn't work out when I used an included SQL snippet in the select-by-email-with-courses SELECT statement.

You might have also realized that yes, this approach does involve the N+1 Select problem - and if I was running queries that yielded all students and all the courses for each student, I'd be concerned. However, I've simply adjusted my application behavior to fetch the courses only on demand - e.g. when the user drills down for details on a given student. This is especially wise since, as mentioned in the iBatis docs, the 2.x version does not provide a solution for the N+1 Select problem in a one-to-many context.

In either event, the Java-level call is simply this:

String email = "foo@bar.com";
return (Student)sqlMapClient.queryForObject("select-by-email-with-courses", email);

Since the courses property in the Student class is of type java.util.List, the iBatis framework populates that field with a list of results from the get-courses-for-student subselect.