This blog mainly explains three ways to achieve one-to-one mapping of query results in MyBatis:

  1. Use aliases for automatic mapping
  2. Use resultMap for configuration
  3. Use the Association label of the resultMap

1. Use aliases to implement automatic mapping

Suppose you have a requirement to retrieve roles owned by the user while querying user information based on the user ID. For example, let’s assume that a user can only have one role (which is definitely not the case).

In general, it is not recommended to modify the entity class of the database table directly, so we create a new class SysUserExtend from SysUser and add a field of type SysRole as follows:

package com.zwwhnly.mybatisaction.model; import java.util.List; Public class SysUserExtend extends SysUser {private SysRole SysRole; public SysRolegetSysRole() {
        return sysRole;
    }

    public void setSysRole(SysRole sysRole) { this.sysRole = sysRole; }}Copy the code

Then, we add the following method to the interface SysUserMapper:

/** * Obtain user information and user role information based on the user ID **@param id
 * @return* /
SysUserExtend selectUserAndRoleById(Long id);
Copy the code

Next, add the following code to the corresponding sysusermapper.xml:

<select id="selectUserAndRoleById" resultType="com.zwwhnly.mybatisaction.model.SysUserExtend">
    SELECT u.id,
           u.user_name userName,
           u.user_password userPassword,
           u.user_email userEmail,
           u.create_time createTime,
           r.id "sysRole.id",
           r.role_name   "sysRole.roleName",
           r.enabled "sysRole.enabled",
           r.create_by   "sysRole.createBy",
           r.create_time "sysRole.createTime"
    FROM sys_user u
    INNER JOIN sys_user_role ur ON u.id = ur.user_id
    INNER JOIN sys_role r ON ur.role_id = r.id
    WHERE u.id = #{id}
</select>
Copy the code

The first is that the resultType should be set to the SysUserExtend class you just created. The second is that the column alias should be set to something like “sysRole. Id “, where the sysRole should be the same as the field name in the SysUserExtend class. The ID must be the same as the field name in the SysRole class.

Finally, add the following test methods to the SysUserMapperTest test class:

@Test
public void testSelectUserAndRoleById(a) {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        // Note that user 1001 is used here, as this user has only one role
        SysUserExtend sysUserExtend = sysUserMapper.selectUserAndRoleById(1001L);

        Assert.assertNotNull(sysUserExtend);
        Assert.assertNotNull(sysUserExtend.getSysRole());
    } finally{ sqlSession.close(); }}Copy the code

Run the test code, the test passes, the output log is as follows:

DEBUG [main] – ==> Preparing: SELECT u.id, u.user_name userName, u.user_password userPassword, u.user_email userEmail, u.create_time createTime, r.id “sysRole.id”, r.role_name “sysRole.roleName”, r.enabled “sysRole.enabled”, r.create_by “sysRole.createBy”, r.create_time “sysRole.createTime” FROM sys_user u INNER JOIN sys_user_role ur ON u.id = ur.user_id INNER JOIN sys_role r ON ur.role_id = r.id WHERE u.id = ?

DEBUG [main] – ==> Parameters: 1001(Long)

TRACE [main] – <== Columns: id, userName, userPassword, userEmail, createTime, sysRole.id, sysRole.roleName, sysRole.enabled, sysRole.createBy, sysRole.createTime

TRACE [main] – <== Row: 1001, test, 123456, [email protected], 2019-06-27 18:21:07.0, 2, 1, 1, 2019-06-27 18:21:12.0

DEBUG [main] – <== Total: 1

2. Use resultMap for configuration

Continue with the above requirements, but use a resultMap to configure the mapping.

First, we add the following methods to the interface SysUserMapper:

/** * Obtain user information and user role information based on the user ID **@param id
 * @return* /
SysUserExtend selectUserAndRoleByIdResultMap(Long id);
Copy the code

Add the following resultMap to the corresponding sysusermapper. XML file:

<resultMap id="userRoleMap" type="com.zwwhnly.mybatisaction.model.SysUserExtend">
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="userPassword" column="user_password"/>
        <result property="userEmail" column="user_email"/>
        <result property="userInfo" column="user_info"/>
        <result property="headImg" column="head_img" jdbcType="BLOB"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <! -- Role related attributes -->
        <result property="sysRole.id" column="role_id"/>
        <result property="sysRole.roleName" column="role_role_name"/>
        <result property="sysRole.enabled" column="role_enabled"/>
        <result property="sysRole.createBy" column="role_create_by"/>
        <result property="sysRole.createTime" column="role_create_time" jdbcType="TIMESTAMP"/>
</resultMap>
Copy the code

To prevent multiple tables from having the same column name, prefix “role_” is used when configuring role-related attributes. This prefix must be consistent with the column alias set in the following query statement:

<select id="selectUserAndRoleByIdResultMap" resultMap="userRoleMap">SELECT u.id, u.user_name, u.user_password, u.user_email, u.create_time, r.id role_id, r.role_name role_role_name, r.enabled role_enabled, r.create_by role_create_by, r.create_time role_create_time FROM sys_user u INNER JOIN sys_user_role ur ON u.id = ur.user_id INNER JOIN sys_role r ON  ur.role_id = r.id WHERE u.id = #{id}</select>
Copy the code

Note: In this query statement, we use a resultMap instead of a resultType.

Since both the test code and the output log are almost identical to those in 1, they are not listed here.

Some of you may remember that we used sysusermapper. XML to configure a resultMap like this:

<resultMap id="sysUserMap" type="com.zwwhnly.mybatisaction.model.SysUser">
    <id property="id" column="id"/>
    <result property="userName" column="user_name"/>
    <result property="userPassword" column="user_password"/>
    <result property="userEmail" column="user_email"/>
    <result property="userInfo" column="user_info"/>
    <result property="headImg" column="head_img" jdbcType="BLOB"/>
    <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
Copy the code

The first half of the userRoleMap we just defined is the same as this configuration. Can we reuse the sysUserMap configuration?

The answer is yes, MyBatis supports resultMap inheritance.

Therefore, userRoleMap can inherit from sysUserMap and omit the repeated configuration. The optimized configuration is as follows:

<resultMap id="userRoleMap" type="com.zwwhnly.mybatisaction.model.SysUserExtend" extends="sysUserMap">
    <! -- Role related attributes -->
    <result property="sysRole.id" column="role_id"/>
    <result property="sysRole.roleName" column="role_role_name"/>
    <result property="sysRole.enabled" column="role_enabled"/>
    <result property="sysRole.createBy" column="role_create_by"/>
    <result property="sysRole.createTime" column="role_create_time" jdbcType="TIMESTAMP"/>
</resultMap>
Copy the code

3. Use the Association label of resultMap

The above configuration can also be configured using the Association tag as follows (to achieve the same effect) :

<resultMap id="userRoleMap" type="com.zwwhnly.mybatisaction.model.SysUserExtend" extends="sysUserMap">
    <association property="sysRole" columnPrefix="role_" javaType="com.zwwhnly.mybatisaction.model.SysRole">
        <result property="id" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="enabled" column="enabled"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    </association>
</resultMap>
Copy the code

If you have an idea, you may feel that the mapping configuration of the role table can be configured independently, for example:

<resultMap id="roleMap" type="com.zwwhnly.mybatisaction.model.SysRole">
    <id property="id" column="id"/>
    <result property="roleName" column="role_name"/>
    <result property="enabled" column="enabled"/>
    <result property="createBy" column="create_by"/>
    <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
Copy the code

Then powerful MyBatis can make association tag to use roleMap directly, so the above association tag configuration can be optimized as:

<resultMap id="userRoleMap" type="com.zwwhnly.mybatisaction.model.SysUserExtend" extends="sysUserMap">
    <association property="sysRole" columnPrefix="role_"
                     resultMap="roleMap"/>
</resultMap>
Copy the code

At this point, roleMap is built in sysuserMapper.xml, but in practice it makes more sense to put this roleMap in sysrolemapper.xml.

Note, however, that after moving roleMap to sysrolemapper.xml, you reference roleMap using its full name, as shown below:

<association property="sysRole" columnPrefix="role_"
             resultMap="com.zwwhnly.mybatisaction.mapper.SysRoleMapper.roleMap"/>
Copy the code

Otherwise, the following error is reported.

4. Source code and reference

Source code address: github.com/zwwhnly/myb… Welcome to download.

MyBatis from Entry to Mastery by Liu Zenghui