This blog will explain how to use the collection tag to implement one-to-many mapping of query results in MyBatis.

1. Use the Collection label

Requirement: A user can have one or more roles when the user information is queried based on the user ID.

In general, it is not recommended to directly modify the entity class corresponding to a database table.

So we’ll stick with our new SysUserExtend class from the previous blog and add the following code, as follows:

/** * User role set */
private List<SysRole> sysRoleList;

public List<SysRole> getSysRoleList(a) {
    return sysRoleList;
}

public void setSysRoleList(List<SysRole> sysRoleList) {
    this.sysRoleList = sysRoleList;
}
Copy the code

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

/** * Get all users and all roles **@return* /
List<SysUserExtend> selectAllUserAndRoles(a);
Copy the code

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

<resultMap id="userRoleListMap" type="com.zwwhnly.mybatisaction.model.SysUserExtend" extends="sysUserMap">
    <collection property="sysRoleList" columnPrefix="role_"
                ofType="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"/>
    </collection>
</resultMap>
Copy the code

Because we’ve already built a roleMap of the character table in the previous blog:

<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

So the collection tag above can be simplified as:

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

Create the query code for the interface using the new userRoleListMap as shown below:

<select id="selectAllUserAndRoles" resultMap="userRoleListMap">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</select>
Copy the code

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

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

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

        List<SysUserExtend> sysUserList = sysUserMapper.selectAllUserAndRoles();
        System.out.println("User number:" + sysUserList.size());
        for (SysUserExtend sysUser : sysUserList) {
            System.out.println("Username:" + sysUser.getUserName());
            for (SysRole sysRole : sysUser.getSysRoleList()) {
                System.out.println("Character name:"+ sysRole.getRoleName()); }}}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, 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

DEBUG [main] – ==> Parameters:

TRACE [main] – <== Columns: id, user_name, user_password, user_email, create_time, role_id, role_role_name, role_enabled, role_create_by, role_create_time

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

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

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: 3

Number of users: 2

User name: admin

Role name: Administrator

Role name: Common user

User name: test

Role name: Common user

2. MyBatis merge rule

If you look at the log above, our Sql statement returns 3 items of data. If you look at the database, it returns the following:

However, after the mapping of MyBatis configuration, it is finally merged into 2 users, wherein the first user contains 2 roles, and the second user contains 1 role. Then, according to what rules is MyBatis merged?

When processing the results, MyBatis will judge whether the results are the same. If the results are the same, only the first result will be retained. Therefore, the key point is how MyBatis judges whether the results are the same.

The simplest case for checking whether the results are the same is if there is at least one ID tag in the mapping configuration. The sysUserMap used above has id tags:

<id property="id" column="id"/>
Copy the code

Generally, the field configured with the ID label is the primary key of the table. If the field is a joint primary key, you can configure multiple ID labels.

The function of ID tags is to determine whether the data is the same during the configuration of nested mappings. When configuring ID tags, MyBatis only needs to compare whether the field values configured by ID tags in all data are the same.

Alternatively, the code above can be modified to read:

<result property="id" column="id"/>
Copy the code

If result is used, the query result will not affect the query result, but MyBatis will compare all fields. Therefore, when the number of fields is M, if there are N query results, the comparison is M*N times. If ID tags are configured, the comparison is only N times.

Combined with the above example, in the Sql query result, the user IDS in the first two entries are the same, so they are merged into one user, so the final result is two users.

To better understand the function of id tags, temporarily change sysUserMap to:

<resultMap id="sysUserMap" type="com.zwwhnly.mybatisaction.model.SysUser">
    <id property="userPassword" column="user_password"/>
    <result property="id" column="id"/>
    <result property="userName" column="user_name"/>
    <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

Run the test method and output some of the following logs:

Users: 1

User name: admin

Role name: Administrator

Role name: Common user

Because the passwords of the three users are 123456, only the first user admin and two roles are retained in the three queried data.

Some of you might ask, why not have three characters?

This is because MyBatis will compare the attributes of each level of the nested query. MyBatis will compare the top-level objects first, and if the SysUser part is the same, it will continue to compare the SysRole part, if the SysRole part is different, it will add a SysRole, if the previous one is the same.

If the SysRole has a next level, follow the rules.

The “normal user” roles above are duplicated, so only the first one is retained, resulting in the final result containing only 2 roles instead of 3.

3. Source code and reference

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

MyBatis from Entry to Mastery by Liu Zenghui