In data-driven applications, it's common to deal with data spread across multiple related tables. Object-Relational Mapping (ORM) frameworks like MyBatis provide powerful features to map these relational structures into object models. This article focuses specificlaly on implementing one-to-many associations in MyBatis, where a single entity (the "one" side) is linked to a collection of other entities (the "many" side). For instance, a Department can have multiple Employees.
MyBatis offers two primary strategies for handling one-to-many queries:
- **Nested Select (Separate Queries):** This approach issues a primary query for the "one" side and then a subsequent query for the "many" side, often for each record of the "one" side. It's suitable for lazy loading scenarios, fetching the associated collection only when it's accessed.
- **Nested Results (Joined Query):** This strategy uses a single SQL query with a JOIN operation to fetch data from both the "one" and "many" tables. MyBatis then processes this combined result set to build the complex object graph. This approach typically eager-loads the associated collection.
Let's illustrate these concepts using a Department-Employee example.
Database Schema Setup
We'll start by defining our database tables for departments and emploeyes. Each employee will belong to one department, and each department can have multiple employees.
CREATE DATABASE `my_company_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `my_company_db`;
CREATE TABLE `departments` (
`department_id` INT NOT NULL AUTO_INCREMENT,
`department_name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`department_id`)
) ENGINE = InnoDB;
CREATE TABLE `employees` (
`employee_id` INT NOT NULL AUTO_INCREMENT,
`employee_name` VARCHAR(50) NOT NULL,
`department_ref_id` INT NOT NULL,
PRIMARY KEY (`employee_id`),
FOREIGN KEY (`department_ref_id`) REFERENCES `departments`(`department_id`)
) ENGINE = InnoDB;
-- Sample Data
INSERT INTO `departments` (`department_id`, `department_name`) VALUES (101, 'Sales');
INSERT INTO `departments` (`department_id`, `department_name`) VALUES (102, 'Marketing');
INSERT INTO `employees` (`employee_id`, `employee_name`, `department_ref_id`) VALUES (1, 'Alice Johnson', 101);
INSERT INTO `employees` (`employee_id`, `employee_name`, `department_ref_id`) VALUES (2, 'Bob Smith', 101);
INSERT INTO `employees` (`employee_id`, `employee_name`, `department_ref_id`) VALUES (3, 'Charlie Brown', 102);
INSERT INTO `employees` (`employee_id`, `employee_name`, `department_ref_id`) VALUES (4, 'Diana Prince', 102);
INSERT INTO `employees` (`employee_id`, `employee_name`, `department_ref_id`) VALUES (5, 'Eve Adams', 102);
Entity Models
Next, we create the Java POJO (Plain Old Java Object) models that represent our database entities. The Department class will contain a collection of Employee objects to reflect the one-to-many relationship.
Department Entity
package com.example.model;
import java.util.Set;
import java.util.Objects;
public class Department {
private Integer departmentId;
private String departmentName;
private Set<Employee> staffMembers; // One-to-many association
// Constructors
public Department() {}
public Department(Integer departmentId, String departmentName) {
this.departmentId = departmentId;
this.departmentName = departmentName;
}
// Getters and Setters
public Integer getDepartmentId() { return departmentId; }
public void setDepartmentId(Integer departmentId) { this.departmentId = departmentId; }
public String getDepartmentName() { return departmentName; }
public void setDepartmentName(String departmentName) { this.departmentName = departmentName; }
public Set<Employee> getStaffMembers() { return staffMembers; }
public void setStaffMembers(Set<Employee> staffMembers) { this.staffMembers = staffMembers; }
@Override
public String toString() {
return "Department{" +
"departmentId=" + departmentId +
", departmentName='" + departmentName + '\'' +
", staffMembers=" + staffMembers +
'}';
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Department that = (Department) o;
return Objects.equals(departmentId, that.departmentId);
}
@Override
public int hashCode() {
return Objects.hash(departmentId);
}
}
Employee Entity
package com.example.model;
import java.util.Objects;
public class Employee {
private Integer employeeId;
private String employeeName;
// Constructors
public Employee() {}
public Employee(Integer employeeId, String employeeName) {
this.employeeId = employeeId;
this.employeeName = employeeName;
}
// Getters and Setters
public Integer getEmployeeId() { return employeeId; }
public void setEmployeeId(Integer employeeId) { this.employeeId = employeeId; }
public String getEmployeeName() { return employeeName; }
public void setEmployeeName(String employeeName) { this.employeeName = employeeName; }
@Override
public String toString() {
return "Employee{" +
"employeeId=" + employeeId +
", employeeName='" + employeeName + '\'' +
'}';
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Employee employee = (Employee) o;
return Objects.equals(employeeId, employee.employeeId);
}
@Override
public int hashCode() {
return Objects.hash(employeeId);
}
}
MyBatis Configuration
The mybatis.xml file configures the database connection, transaction manager, and registers our mappers. It's crucial for MyBatis to know where to find the SQL mapping files and entity classes.
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<!-- Environment setup (dataSource, transactionManager) -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/my_company_db?useSSL=false&serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="your_password"/> <!-- Replace with your actual password -->
</dataSource>
</environment>
</environments>
<!-- Type aliases for shorter class names in mapper XML -->
<typeAliases>
<package name="com.example.model"/>
</typeAliases>
<!-- Mapper registration -->
<mappers>
<mapper resource="mappers/DepartmentMapper.xml"/>
</mappers>
</configuration>
Data Access Interface (Mapper)
This interface defines the methods for interacting with our database, which MyBatis will implement based on the corresponding XML mapper file.
package com.example.mapper;
import com.example.model.Department;
import com.example.model.Employee;
import org.apache.ibatis.annotations.Param;
import java.util.Set;
public interface IDepartmentMapper {
/**
* Retrieves a department along with its employees using a nested select strategy.
* This approach performs two separate queries: one for the department, then one for its employees.
* @param deptId The ID of the department.
* @return The Department object populated with its associated employees.
*/
Department findDepartmentWithStaff(@Param("deptId") int deptId);
/**
* Retrieves a department and its employees using a nested result strategy.
* This approach performs a single join query and maps the results directly.
* @param deptId The ID of the department.
* @return The Department object populated with its associated employees.
*/
Department getDepartmentAndStaffInline(@Param("deptId") int deptId);
/**
* Helper method to select employees for a given department ID.
* This is typically used internally by the nested select strategy.
* @param deptId The ID of the department.
* @return A Set of Employee objects for the given department.
*/
Set<Employee> selectEmployeesForDepartment(@Param("deptId") int deptId);
}
SQL Mapper XML
The DepartmentMapper.xml file contains the SQL statements and result mappings for our IDepartmentMapper interface methods. We'll demonstrate both nested select and nested result strategies here.
<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.example.mapper.IDepartmentMapper">
<!-- Nested Select Strategy -->
<!-- This resultMap defines how to map a Department and then fetch its employees with a separate query. -->
<resultMap id="departmentWithStaffMap" type="Department">
<id property="departmentId" column="department_id"/>
<result property="departmentName" column="department_name"/>
<!-- The <collection> element is used for one-to-many associations. -->
<!-- 'property' refers to the collection field in the Department class (staffMembers). -->
<!-- 'ofType' specifies the generic type of the collection (Employee). -->
<!-- 'select' points to another SQL statement (selectEmployeesForDepartment) to fetch the collection members. -->
<!-- 'column' passes the 'department_id' from the parent query result as a parameter to the 'select' query. -->
<collection property="staffMembers"
ofType="Employee"
select="selectEmployeesForDepartment"
column="department_id">
</collection>
</resultMap>
<!-- Primary query for the Department using the nested select resultMap. -->
<select id="findDepartmentWithStaff" resultMap="departmentWithStaffMap">
SELECT department_id, department_name
FROM departments
WHERE department_id = #{deptId}
</select>
<!-- Sub-query to fetch employees for a specific department. -->
<!-- This query is called by the <collection> element in 'departmentWithStaffMap'. -->
<select id="selectEmployeesForDepartment" resultType="Employee">
SELECT employee_id, employee_name
FROM employees
WHERE department_ref_id = #{deptId}
</select>
<!-- Nested Result Strategy -->
<!-- This resultMap maps results from a joined query directly into the Department and Employee objects. -->
<resultMap id="departmentAndStaffInlineMap" type="Department">
<id property="departmentId" column="department_id"/>
<result property="departmentName" column="department_name"/>
<!-- For nested results, the <collection> element defines how to map columns from the joined result. -->
<!-- 'property' and 'ofType' are similar to nested select. -->
<!-- <id> and <result> tags directly map columns from the joined result set to the Employee properties. -->
<collection property="staffMembers" ofType="Employee">
<id property="employeeId" column="employee_id"/>
<result property="employeeName" column="employee_name"/>
</collection>
</resultMap>
<!-- Query to fetch Department and its employees using a single JOIN operation. -->
<!-- The 'department_id', 'department_name', 'employee_id', 'employee_name' columns are all returned in one go. -->
<select id="getDepartmentAndStaffInline" resultMap="departmentAndStaffInlineMap">
SELECT d.department_id, d.department_name, e.employee_id, e.employee_name
FROM departments d
JOIN employees e ON d.department_id = e.department_ref_id
WHERE d.department_id = #{deptId}
</select>
</mapper>
MyBatis Utility Class
A simple utility to manage the SqlSession lifecycle, providing and closing sessions as needed.
package com.example.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
* Utility class for managing MyBatis SqlSession instances.
* Initializes SqlSessionFactory once and provides SqlSession instances.
*/
public class SqlSessionManager {
private static SqlSessionFactory sqlSessionFactoryInstance;
private static final String MYBATIS_CONFIG_PATH = "mybatis.xml";
static {
try (InputStream inputStream = Resources.getResourceAsStream(MYBATIS_CONFIG_PATH)) {
sqlSessionFactoryInstance = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
System.err.println("Error initializing SqlSessionFactory: " + e.getMessage());
throw new ExceptionInInitializerError("Failed to load MyBatis configuration: " + e.getMessage());
}
}
/**
* Provides an open SqlSession.
* @return A new SqlSession instance.
*/
public static SqlSession getSession() {
return sqlSessionFactoryInstance.openSession();
}
/**
* Closes the provided SqlSession.
* @param session The SqlSession to close.
*/
public static void closeSession(SqlSession session) {
if (session != null) {
session.close();
}
}
}
Testing the One-to-Many Mappings
We'll use JUnit tests to verify that both mapping strategies correctly retrieve departments along with their associated employees.
package com.example.test;
import com.example.mapper.IDepartmentMapper;
import com.example.model.Department;
import com.example.model.Employee;
import com.example.util.SqlSessionManager;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertEquals;
public class DepartmentAssociationTests {
private IDepartmentMapper departmentMapper;
private SqlSession currentSession;
@BeforeEach
public void setup() {
currentSession = SqlSessionManager.getSession();
departmentMapper = currentSession.getMapper(IDepartmentMapper.class);
}
@Test
void testFindDepartmentWithNestedSelect() {
System.out.println("--- Testing Nested Select Strategy ---");
Department department = departmentMapper.findDepartmentWithStaff(101); // Assuming ID 101 for 'Sales'
assertNotNull(department, "Department should not be null");
System.out.println("Retrieved Department (Nested Select): " + department.getDepartmentName());
assertNotNull(department.getStaffMembers(), "Staff members collection should not be null");
assertFalse(department.getStaffMembers().isEmpty(), "Staff members collection should not be empty");
System.out.println("Staff count: " + department.getStaffMembers().size());
department.getStaffMembers().forEach(System.out::println);
assertEquals(2, department.getStaffMembers().size()); // Sales department has 2 employees
System.out.println("Full Department Object: " + department);
}
@Test
void testGetDepartmentAndStaffInline() {
System.out.println("\n--- Testing Nested Result Strategy ---");
Department department = departmentMapper.getDepartmentAndStaffInline(102); // Assuming ID 102 for 'Marketing'
assertNotNull(department, "Department should not be null");
System.out.println("Retrieved Department (Nested Result): " + department.getDepartmentName());
assertNotNull(department.getStaffMembers(), "Staff members collection should not be null");
assertFalse(department.getStaffMembers().isEmpty(), "Staff members collection should not be empty");
System.out.println("Staff count: " + department.getStaffMembers().size());
department.getStaffMembers().forEach(System.out::println);
assertEquals(3, department.getStaffMembers().size()); // Marketing department has 3 employees
System.out.println("Full Department Object: " + department);
}
@AfterEach
public void tearDown() {
SqlSessionManager.closeSession(currentSession);
}
}