This guide demonstrates how to implement one-to-many and many-to-one relationship queries in MyBatis 3.5.19 using MySQL 8.0.33, focusing on the classic Department-Employee model.

Entity Design

Department Entity (Dept)

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
    private Long id;
    private String name;
    private String location;

    // One department has many employees
    private List<Emp> employees;
}

Employee Entity (Emp)

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private Long id;
    private String name;
    private String position;
    private Long managerId;
    private Date hireDate;
    private Double salary;
    private Double bonus;
    private Long departmentId;

    // Many employees belong to one department
    private Dept department;
}

Many-to-One Query (Employee → Department)

Using <association> Tag

Use Case: Retrieve employee details along with their associated department information.

Implementation: Nested query approach (results in N+1 query problem)

<resultMap id="employeeDetailMap" type="Emp">
    <id column="emp_id" property="id"/>
    <result column="emp_name" property="name"/>
    <result column="position" property="position"/>
    <result column="manager_id" property="managerId"/>
    <result column="hire_date" property="hireDate"/>
    <result column="salary" property="salary"/>
    <result column="bonus" property="bonus"/>
    <result column="dept_id" property="departmentId"/>
    
    <!-- Association for department lookup -->
    <association property="department" 
                 javaType="cn.wolfcode.domain.Dept"
                 select="cn.wolfcode.mapper.DeptMapper.getById" 
                 column="dept_id">
    </association>
</resultMap>

<select id="getAllEmployees" resultMap="employeeDetailMap">
    SELECT e.id as emp_id, e.name as emp_name, e.position, e.manager_id,
           e.hire_date, e.salary, e.bonus, e.department_id as dept_id,
           d.id as dept_id_ref, d.name as dept_name, d.location
    FROM employee e 
    LEFT JOIN department d ON e.department_id = d.id
</select>

Key Attributes:

• property: Field name in the Emp class (department)
• javaType: Type of the associated object
• select: Reefrence to another mapper's query method
• column: Column value passed as parameter to nested query

Note: This approach causes N+1 query issue (1 query for employees + N queries for departments).

One-to-Many Query (Department → Employees)

Using <collection> Tag

Use Case: Retrieve department information along with all associated employees.

Implementation: Left join with resultMap reuse

<resultMap id="departmentDetailMap" type="Dept">
    <id column="dept_id" property="id"/>
    <result column="dept_name" property="name"/>
    <result column="location" property="location"/>
    
    <!-- Collection mapping for employees -->
    <collection property="employees" 
                ofType="cn.wolfcode.domain.Emp"
                resultMap="cn.wolfcode.mapper.EmpMapper.basicEmployeeMap">
    </collection>
</resultMap>

<!-- Reusable employee mapping -->
<resultMap id="basicEmployeeMap" type="Emp">
    <id column="emp_id" property="id"/>
    <result column="emp_name" property="name"/>
    <result column="position" property="position"/>
    <result column="manager_id" property="managerId"/>
    <result column="hire_date" property="hireDate"/>
    <result column="salary" property="salary"/>
    <result column="bonus" property="bonus"/>
    <result column="department_id" property="departmentId"/>
</resultMap>

<select id="getAllDepartments" resultMap="departmentDetailMap">
    SELECT d.id as dept_id, d.name as dept_name, d.location,
           e.id as emp_id, e.name as emp_name, e.position, 
           e.manager_id, e.hire_date, e.salary, e.bonus, e.department_id
    FROM department d 
    LEFT JOIN employee e ON d.id = e.department_id
</select>

Key Attributes:

• property: Collection field in Dept class (employees)
• ofType: Element type within the collection
• resultMap: Reference to reusable mapping configuration
• Left join ensures departments without emploeyes are still retrieved

Test Implementation

Many-to-One Test

@Test
public void testEmployeeWithDepartment() {
    List<Emp> employees = employeeMapper.getAllEmployees();
    employees.forEach(System.out::println);
}

Sample Output:

Emp(id=7369, name=SMITH, position=CLERK, managerId=7902, hireDate=..., 
    salary=800.0, bonus=null, departmentId=20, 
    department=Dept(id=20, name=RESEARCH, location=DALLAS, employees=null))

Summary

<association> Configuration (Many-to-One)

• property: Target field in entity class
• javaType: Fully qualified class name of associated object
• select: Statement ID for nested query execution
• column: Database column passed as parameter
• Suitable for retrieving related parent data during child entity queries

<collection> Configuration (One-to-Many)

• property: Collection field in parent entity
• ofType: Type of elements in the collection
• resultMap: Reference to existing mapping definitions
• Ideal for fetching child entities when querying parent records

ResultMap Reusability

Mapping configurations can be shared across mappers using namespace references like resultMap="namespace.resultMapId". In this example, DeptMapper.xml reuses the basicEmployeeMap defined in EmpMapper.xml.

The many-to-one relationship is handled through association tags with nested queries, while one-to-many relationships use collection tags combined with left joins and resultMap reuse for efficient data retrieval.