Handling Column-to-Property Mapping in MyBatis

When working with MyBatis, a common challenge arises when mapping database columns to Java entity properties. Database tables typicallly use snake_case naming (like emp_name), while Java POJOs follow camelCase convenitons (like empName). This article explores several approaches to resolve these naming mismatches.

Scenario: Two Tables

Consider two tables: t_emp (employees) and t_dept (departments). We'll examine various techniques for handling the mapping between these tables and their corresponding entity classes.

---

Resolving Field Name Mismatches

Method 1: SQL Column Aliases

The simplest approach involves using SQL aliases in your queries to match property names:

SELECT column_name AS propertyName FROM table_name

Mapper Interface:

List<Employee> findAllEmployees();

XML Mapping:

<select id="findAllEmployees" resultType="Employee">
    SELECT employee_id AS empId, employee_name AS empName, age, gender, email_address 
    FROM t_employee
</select>

Method 2: Global Configuration for Automatic Mapping

MyBatis provides a built-in setting to automatically convert between snake_case and camelCase:

mybatis-config.xml:

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

When enabled, MyBatis automatically maps employee_name to employeeName, created_at to createdAt, and so forth.

Method 3: ResultMap Custom Mapping

For complete control over the mapping relationship, use resultMap elements. This approach is particularly useful for complex queries.

ResultMap Structure:

• type: The fully qualified entity class name
• id: Unique identifier for this resultMap
• id tag: Maps the primary key column
• result tag: Maps regular property columns
• property: The entity property name
• column: The database column name

XML Configuration:

<resultMap id="employeeResultMap" type="com.example.entity.Employee">
    <id property="empId" column="employee_id"/>
    <result property="empName" column="employee_name"/>
    <result property="age" column="age"/>
    <result property="gender" column="gender"/>
    <result property="email" column="email_address"/>
</resultMap>

<select id="findAllEmployees" resultMap="employeeResultMap">
    SELECT employee_id, employee_name, age, gender, email_address 
    FROM t_employee
</select>

---

Many-to-One Relationship Mapping

A typical scenario involves employees belonging to departments. Multiple employees can associate with a single department, requiring many-to-one relationship handling.

Setup: Mapper Methods

Employee findEmployeeWithDepartment(@Param("empId") Integer empId);

<select id="findEmployeeWithDepartment" resultMap="empDeptResultMap">
    SELECT * 
    FROM t_employee e
    LEFT JOIN t_department d ON e.department_id = d.department_id
    WHERE e.employee_id = #{empId}
</select>

Method 1: Nested Property Assignment

Directly assign values to nested properties using dot notation:

<resultMap id="empDeptResultMap" type="Employee">
    <id property="empId" column="employee_id"/>
    <result property="empName" column="employee_name"/>
    <result property="age" column="age"/>
    <result property="gender" column="gender"/>
    <result property="email" column="email_address"/>
    <result property="department.deptId" column="department_id"/>
    <result property="department.deptName" column="department_name"/>
</resultMap>

Method 2: Association Tag

The association tag provides a cleaner approach for handling nested objects:

<resultMap id="empDeptResultMap" type="Employee">
    <id property="empId" column="employee_id"/>
    <result property="empName" column="employee_name"/>
    <result property="age" column="age"/>
    <result property="gender" column="gender"/>
    <result property="email" column="email_address"/>
    <association property="department" javaType="Department">
        <id property="deptId" column="department_id"/>
        <result property="deptName" column="department_name"/>
    </association>
</resultMap>

Method 3: Stepped Query with Association

For greater flexibility, use nested queries to fetch associated data separately. This approach supports lazy loading.

Prerequisite: Enable Camel Case Mapping

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

Step 1: Query Employee Data

<resultMap id="empDeptResultMapStep" type="Employee">
    <id property="empId" column="employee_id"/>
    <result property="empName" column="employee_name"/>
    <result property="age" column="age"/>
    <result property="gender" column="gender"/>
    <result property="email" column="email_address"/>
    <association property="department"
                 select="com.example.mapper.DepartmentMapper.findDepartmentById"
                 column="department_id"
                 fetchType="lazy"/>
</resultMap>

<select id="findEmployeeWithDepartment" resultMap="empDeptResultMapStep">
    SELECT employee_id, employee_name, age, gender, email_address, department_id
    FROM t_employee
    WHERE employee_id = #{empId}
</select>

Step 2: Query Department Data

Department findDepartmentById(@Param("deptId") Integer deptId);

<select id="findDepartmentById" resultType="Department">
    SELECT department_id AS deptId, department_name AS deptName
    FROM t_department
    WHERE department_id = #{deptId}
</select>

---

Lazy Loading Configuration

Lazy loading allows MyBatis to defer loading associated data until it's actually needed, improving performence for large datasets.

Global Settings

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <!-- Enable lazy loading globally -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- Load associated objects on demand -->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

Configuration Details

• lazyLoadingEnabled: Global switch for lazy loading. When true, all nested queries (steps 2, 3, etc.) are deferred.
• aggressiveLazyLoading: When false (recommended), associated objects load only when accessed. When true, any method call loads all properties.
• fetchType: Override the global setting per-association using lazy or eager.

Example with fetchType:

<association property="department"
             select="com.example.mapper.DepartmentMapper.findDepartmentById"
             column="department_id"
             fetchType="lazy"/>

---

One-to-Many Relationship Mapping

Conversely, a department contains multiple employees, representing a one-to-many relationship.

Collection Tag Configuration

Use the collection tag to handle sets of associated objects:

• property: The collection property name in the entity
• ofType: The type of objects stored in the collection
• select: SQL identifier for nested queries
• column: Column passed to nested query
• fetchType: lazy or eager for loading strategy

Method 1: Single Query Join

<resultMap id="deptWithEmployees" type="Department">
    <id property="deptId" column="department_id"/>
    <result property="deptName" column="department_name"/>
    <collection property="employees" ofType="Employee">
        <id property="empId" column="employee_id"/>
        <result property="empName" column="employee_name"/>
        <result property="age" column="age"/>
        <result property="gender" column="gender"/>
        <result property="email" column="email_address"/>
    </collection>
</resultMap>

<select id="findDepartmentWithEmployees" resultMap="deptWithEmployees">
    SELECT d.department_id, d.department_name, e.employee_id, e.employee_name, 
           e.age, e.gender, e.email_address
    FROM t_department d
    LEFT JOIN t_employee e ON d.department_id = e.department_id
    WHERE d.department_id = #{deptId}
</select>

Method 2: Stepped Query

Separate queries provide more flexibility and support lazy loading:

<resultMap id="deptWithEmployeesStep" type="Department">
    <id property="deptId" column="department_id"/>
    <result property="deptName" column="department_name"/>
    <collection property="employees"
                select="com.example.mapper.EmployeeMapper.findEmployeesByDept"
                column="department_id"/>
</resultMap>

<select id="findDepartmentById" resultMap="deptWithEmployeesStep">
    SELECT department_id, department_name
    FROM t_department
    WHERE department_id = #{deptId}
</select>

Second Query:

List<Employee> findEmployeesByDept(@Param("deptId") Integer deptId);

<select id="findEmployeesByDept" resultType="Employee">
    SELECT employee_id AS empId, employee_name AS empName, age, gender, email_address
    FROM t_employee
    WHERE department_id = #{deptId}
</select>

---

Summary

MyBatis offers multiple strategies for handling custom parameter mapping:

• SQL Aliases: Simple but requires explicit aliasing in each query
• Global Settings: Automatic snake_case to camelCase conversion
• ResultMap: Full control with explicit mappings
• Association/Collection Tags: Handle complex relationships
• Stepped Queries: Enable lazy loading for improved performance

Choose the approach that best fits your application's requirements based on complexity, performance needs, and coding standards.