Relational database interactions often involve navigating relationships between tables. In MyBatis, the <resultMap> element provides a robust mechanism to map these relationships—One-to-One, One-to-Many, and Many-to-Many—directly to Java object graphs. This process relies heavily on the <association> and <collection> tags within the XML configuration.

Database Schema Setup

To demonstrate these mappings, consider a schema involving four tables: sys_user (the central entity), id_card (One-to-One), training_course (One-to-Many), and sys_role linked via a user_role_link junction table (Many-to-Many).

CREATE TABLE `sys_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `status` varchar(20) DEFAULT 'ACTIVE',
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `id_card` (
  `card_id` int(11) NOT NULL AUTO_INCREMENT,
  `serial_no` varchar(30) NOT NULL,
  `city` varchar(50) NOT NULL,
  `address` varchar(100) NOT NULL,
  `owner_id` int(11) NOT NULL,
  PRIMARY KEY (`card_id`)
);

CREATE TABLE `training_course` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(50) NOT NULL,
  `student_id` int(11) NOT NULL,
  PRIMARY KEY (`course_id`)
);

CREATE TABLE `sys_role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `description` varchar(100) NOT NULL,
  PRIMARY KEY (`role_id`)
);

CREATE TABLE `user_role_link` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`)
);

Java Entity Models

The corresponding Java objects must define fields for the related entities. The SysUser class will contain references to IdCard, lists of Course, and lists of SysRole.

public class SysUser {
    private Integer userId;
    private String username;
    private Integer age;
    private String status;
    
    // One-to-One
    private IdCard idCard;
    
    // One-to-Many
    private List<Course> courseList;
    
    // Many-to-Many
    private List<SysRole> roleList;

    // Standard getters and setters omitted for brevity
}

public class IdCard {
    private Integer cardId;
    private String serialNo;
    private String city;
    private String address;
    private Integer ownerId;
    // getters and setters
}

public class Course {
    private Integer courseId;
    private String courseName;
    private Integer studentId;
    // getters and setters
}

public class SysRole {
    private Integer roleId;
    private String roleName;
    private String description;
    // getters and setters
}

Defining the Result Map

MyBatis handles relationship mapping through nested elements in the <resultMap>. The SQL query performs a series of JOINs to fetch all data in a single request.

<resultMap id="cardMap" type="IdCard">
    <id property="cardId" column="card_id"/>
    <result property="serialNo" column="serial_no"/>
    <result property="city" column="city"/>
    <result property="address" column="address"/>
</resultMap>

<resultMap id="courseMap" type="Course">
    <id property="courseId" column="course_id"/>
    <result property="courseName" column="course_name"/>
</resultMap>

<resultMap id="roleMap" type="SysRole">
    <id property="roleId" column="role_id"/>
    <result property="roleName" column="role_name"/>
    <result property="description" column="description"/>
</resultMap>

For the main user mapping, we use <association> for the single object reference and <collection> for the list references. To simplify column mapping and avoid ambiguity, column aliases are used in the SQL query, and the columnPrefix attribute isolates the mapping scope.

<resultMap id="userResultMap" type="SysUser">
    <id property="userId" column="user_id"/>
    <result property="username" column="username"/>
    <result property="age" column="age"/>
    <result property="status" column="status"/>
    
    
    <association property="idCard" resultMap="cardMap" columnPrefix="card_"/>
    
    
    <collection property="courseList" resultMap="courseMap" columnPrefix="course_"/>
    
    
    <collection property="roleList" resultMap="roleMap" columnPrefix="role_"/>
</resultMap>

The Query Statement

The SQL statement must alias columns correctly to match the columnPrefix definitions. For instance, columns for IdCard are prefixed with card_, which matches the prefix defined in the <association> tag.

<select id="findUserWithDetails" resultMap="userResultMap">
    SELECT 
        u.user_id,
        u.username,
        u.age,
        u.status,
        c.card_id AS card_card_id,
        c.serial_no AS card_serial_no,
        c.city AS card_city,
        c.address AS card_address,
        cr.course_id AS course_course_id,
        cr.course_name AS course_course_name,
        r.role_id AS role_role_id,
        r.role_name AS role_role_name,
        r.description AS role_description
    FROM sys_user u
    LEFT JOIN id_card c ON u.user_id = c.owner_id
    LEFT JOIN training_course cr ON u.user_id = cr.student_id
    LEFT JOIN user_role_link url ON u.user_id = url.user_id
    LEFT JOIN sys_role r ON url.role_id = r.role_id
    WHERE u.user_id = #{userId}
</select>

Key Mapping Concepts

• association: Used for mapping a single associated object (One-to-One). It maps the javaType of the property.
• collection: Used for mapping a list of associated objects (One-to-Many, Many-to-Many). It requires ofType to specify the class contained within the javaType List.
• resultMap reuse: By defining separate <resultMap> definitions for IdCard, Course, and SysRole, we can reference them directly in the main map using the resultMap attribute, ensuring cleaner XML and better maintainability.