In relational database design, a many-to-many relationship occurs when multiple records in one table correspond to multiple records in another. A classic example involves students enrolling in various courses, where each student can take many classes, and each class has many participants. To implement this in MyBatis, a junction (link) table is typically required to bridge the two entities.

Entity Definitions

We begin by defining the persistent objects (POJOs). Care must be taken to avoid infinite recursion during serialization or string representation, particularly when entities reference each other.

Below is the Student entity. It holds basic identification data and maintains a collection of associated subjects:

import java.util.HashSet;
import java.util.Set;

public class Student {
    private Long id;
    private String name;
    private Set<Course> enrolledCourses;

    public Student() {
        this.enrolledCourses = new HashSet<>();
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<Course> getEnrolledCourses() {
        return enrolledCourses;
    }

    public void setEnrolledCourses(Set<Course> enrolledCourses) {
        this.enrolledCourses = enrolledCourses;
    }

    @Override
    public String toString() {
        // Avoid looping back to courses containing students to prevent StackOverflowError
        return "Student{id=" + id + ", name='" + name + "'}";
    }
}

The corresponding Course entity represents the subject matter:

import java.util.HashSet;
import java.util.Set;

public class Course {
    private Long id;
    private String title;
    private Set<Student> registeredStudents;

    public Course() {
        this.registeredStudents = new HashSet<>();
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Set<Student> getRegisteredStudents() {
        return registeredStudents;
    }

    public void setRegisteredStudents(Set<Student> registeredStudents) {
        this.registeredStudents = registeredStudents;
    }

    @Override
    public String toString() {
        // Excluding students here prevents deep nesting loops
        return "Course{id=" + id + ", title='" + title + "'}";
    }
}

Mapper Interface

The Data Access Object (DAO) layer defines the contract for querying. Here we define a method to fetch a student profile along with their loaded curriculum history.

package com.example.mapper;

import com.example.model.Student;

public interface StudentMapper {
    Student loadStudentDetailsById(Long studentId);
}

XML Mapping Configuraton

The core logic resides in the XML configuration file. We utilize a composite resultMap to handle the collection mapping. The query performs an inner join across the primary student table, the linking table, and the course table.

<?xml version="1.0" encoding="UTF-8" ?>

<mapper namespace="com.example.mapper.StudentMapper">

    <resultMap id="StudentWithCoursesMap" type="com.example.model.Student">
        <id column="sid" property="id"/>
        <result column="sname" property="name"/>
        
        <!-- Collection handles the one-to-many relationship -->
        <collection property="enrolledCourses" ofType="com.example.model.Course">
            <id column="cid" property="id"/>
            <result column="cname" property="title"/>
        </collection>
    </resultMap>

    <select id="loadStudentDetailsById" resultMap="StudentWithCoursesMap" parameterType="Long">
        SELECT 
            st.student_id AS sid,
            st.name AS sname,
            cr.course_id AS cid,
            cr.title AS cname
        FROM 
            student st
        JOIN 
            enrollment_links el ON st.student_id = el.student_id
        JOIN 
            course cr ON cr.course_id = el.course_id
        WHERE 
            st.student_id = #{studentId}
    </select>

</mapper>

In this configuration, the <collection> tag specifies how to map the repeated rows resulting from the join into the enrolledCourses set. Using ofType="..." ensures that nested results are treated as individual Course objects.

Testing Execution

To verify the mapping behavior, a standard unit test constructs a session and executes the selected query.

import com.example.utils.MyBatisSessionFactory;
import com.example.mapper.StudentMapper;
import com.example.model.Student;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import javax.sql.DataSource;

public class StudentIntegrationTest {

    private StudentMapper studentMapper;

    @Before
    public void init() {
        DataSource dataSource = MyBatisSessionFactory.getDataSource();
        // In production, dependency injection is preferred over manual instantiation
        studentMapper = MyBatisSessionFactory.createSession().getMapper(StudentMapper.class);
    }

    @Test
    public void shouldRetrieveStudentWithCourses() {
        Long targetId = 1L;
        Student student = studentMapper.loadStudentDetailsById(targetId);
        
        Assert.assertNotNull(student);
        Assert.assertEquals(2, student.getEnrolledCourses().size());
        
        System.out.println("Loaded Student: " + student);
        for (var course : student.getEnrolledCourses()) {
            System.out.println("  - " + course.getTitle());
        }
    }
}

Observation

The resulting debug logs confirm the constructed SQL statement and the number of rows returned by the driver.

DEBUG - Preparing: 
    SELECT st.student_id AS sid, st.name AS sname, 
           cr.course_id AS cid, cr.title AS cname 
    FROM student st 
    JOIN enrollment_links el ON st.student_id = el.student_id 
    JOIN course cr ON cr.course_id = el.course_id 
    WHERE st.student_id = ?
DEBUG - Parameters: 1(Long)
DEBUG - Total: 2
Loaded Student: Student{id=1, name='Alice'}
  - Mathematics
  - Physics