Master MyBatis Result Mapping: From Alias to Nested Associations

Introduction

The previous article covered MyBatis basic CRUD operations; this article continues with result mapping, a crucial feature for flexible enterprise development.

What Is Result Mapping?

Result mapping connects columns returned by a SQL query to properties of a Java bean.

<select id="selectPatientInfos" resultType="com.xxx.domain.PatientInfo">
    select user_id,dept_id,doc_id from patient_info;
</select>

@Data
public class PatientInfo {
    private String userId;
    private String deptId;
    private String docId;
}

The SQL columns user_id, dept_id, and doc_id are mapped to the bean properties userId, deptId, and docId respectively.

Mapping Techniques

Alias Mapping

Use column aliases to make the column names match the bean property names.

<select id="selectPatientInfos" resultType="com.xxx.domain.PatientInfo">
    select user_id as userId,
           dept_id as deptId,
           doc_id as docId
    from patient_info;
</select>

CamelCase Mapping

MyBatis can automatically map snake_case columns to camelCase properties.

Enable it via the configuration file:

mybatis.configuration.map-underscore-to-camel-case=true

Or enable it programmatically in a Spring Boot configuration class:

@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
    factory.setDataSource(dataSource);
    factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATOIN));
    org.apache.ibatis.session.Configuration cfg = new org.apache.ibatis.session.Configuration();
    cfg.setMapUnderscoreToCamelCase(true);
    cfg.setDefaultFetchSize(100);
    cfg.setDefaultStatementTimeout(30);
    factory.setConfiguration(cfg);
    factory.setTypeHandlers(typeHandlers());
    return factory.getObject();
}

ResultMap Mapping

Define an explicit resultMap to map columns to bean properties when alias or camelCase mapping is insufficient.

<!-- create resultMap -->
<resultMap id="patResultMap" type="com.xxx.domain.PatientInfo">
    <id property="userId" column="user_id"/>
    <result property="deptId" column="dept_id"/>
    <result property="docId" column="doc_id"/>
</resultMap>

<select id="selectPatientInfos" resultMap="patResultMap">
    select user_id,dept_id,doc_id from patient_info;
</select>

Advanced Mappings

Association (One‑to‑One)

Map a nested object using association. Two loading strategies exist: nested select and nested result mapping. Nested select can cause the N+1 query problem.

<!-- association example -->
<resultMap id="UserDepartment" type="com.xxx.domain.User">
    <id column="user_id" property="id"/>
    <result column="password" property="password"/>
    <result column="age" property="age"/>
    <result column="username" property="username"/>
    <association property="department" javaType="com.xxx.domain.Department">
        <id column="id" property="id"/>
        <result column="dept_name" property="name"/>
    </association>
</resultMap>

<select id="findUserAndDepartment" resultMap="UserDepartment">
    select u.id as user_id, u.dept_id, u.username, u.password, u.age,
           d.id, d.name as dept_name
    from user u left join department d on u.department_id = d.id;
</select>

Collection (One‑to‑Many)

Map a list of child objects using collection. Example: a Department containing many Users.

<!-- department resultMap -->
<resultMap id="deptResult" type="com.xxx.domain.Department">
    <id column="dept_id" property="id"/>
    <result column="dept_name" property="name"/>
    <collection property="users" ofType="com.xxx.domain.User" resultMap="userResult"/>
</resultMap>

<!-- user resultMap -->
<resultMap id="userResult" type="com.xxx.domain.User">
    <id column="user_id" property="id"/>
    <result column="password" property="password"/>
    <result column="age" property="age"/>
    <result column="username" property="username"/>
</resultMap>

<select id="selectDeptById" resultType="com.xxx.domain.Department">
    select d.id as dept_id, d.name as dept_name,
           u.id as user_id, u.password, u.name, u.age
    from department d left join user u on u.department_id = d.id
    where d.id = #{id};
</select>

Key Takeaways

Three main mapping approaches: alias mapping, camelCase automatic mapping, and explicit resultMap mapping.

Use association for one‑to‑one relationships and collection for one‑to‑many relationships.

Avoid nested select queries in production because they trigger the N+1 query problem; prefer nested result mappings for better performance.