MyBatis series (12) : Use collection tag to implement nested query

This blog post focuses on implementing nested queries using the Collection tag.

1. Upgrade requirements

In the last blog post, we implemented the requirement to retrieve the roles owned by the user while querying the user’s information based on the user ID.

Because a role can have multiple permissions, we need to upgrade this blog to query the user information according to the user ID and obtain the role owned by the user and the permissions contained by the role.

2. Implementation method

Since we need to use the mapping to the permission table, we need to add the following mapping to sysprivilegemapper.xml first:

<resultMap id="sysPrivilegeMap" type="com.zwwhnly.mybatisaction.model.SysPrivilege">
    <id property="id" column="id"/>
    <result property="privilegeName" column="privilege_name"/>
    <result property="privilegeUrl" column="privilege_url"/>
</resultMap>
Copy the code

It is generally not recommended to modify the entity class corresponding to the database table, so we create a new class SysRoleExtend that extends from SysRole and add the following fields:

package com.zwwhnly.mybatisaction.model;

import java.util.List;

public class SysRoleExtend extends SysRole {
    /** * List of permissions contained in roles */
    private List<SysPrivilege> sysPrivilegeList;

    public List<SysPrivilege> getSysPrivilegeList(a) {
        return sysPrivilegeList;
    }

    public void setSysPrivilegeList(List<SysPrivilege> sysPrivilegeList) {
        this.sysPrivilegeList = sysPrivilegeList; }}Copy the code

Then create the following mapping in sysrolemapper.xml:

<resultMap id="rolePrivilegeListMap" extends="roleMap"
           type="com.zwwhnly.mybatisaction.model.SysRoleExtend">
    <collection property="sysPrivilegeList" columnPrefix="privilege_"
                resultMap="com.zwwhnly.mybatisaction.mapper.SysPrivilegeMapper.sysPrivilegeMap"/>
</resultMap>
Copy the code

The roleMap we defined in a previous blog is as follows:

<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

. Com. Zwwhnly. Mybatisaction mapper. SysPrivilegeMapper. SysPrivilegeMap is. We just in SysPrivilegeMapper sysPrivilegeMap new mapping XML.

Then, you need to change the userRoleListMap from the previous blog to:

<resultMap id="userRoleListMap" type="com.zwwhnly.mybatisaction.model.SysUserExtend" extends="sysUserMap">
    <collection property="sysRoleList" columnPrefix="role_"
                resultMap="com.zwwhnly.mybatisaction.mapper.SysRoleMapper.rolePrivilegeListMap">
    </collection>
</resultMap>
Copy the code

And change the select tag code from the previous blog with the id selectAllUserAndRoles, because you want to associate the role permission relationship table with the permission table:

<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, p.id role_privilege_id, p.privilege_name role_privilege_privilege_name, p.privilege_url role_privilege_privilege_url 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 INNER JOIN sys_role_privilege rp ON rp.role_id = r.id INNER JOIN sys_privilege p ON p.id = rp.privilege_id</select>
Copy the code

Matters needing attention:

Here, the column names of the sys_privilege table are aliases prefixed with ROle_privilege_ because the columnPrefix attribute of the Collection in the userRoleListMap is role_. And the specified com. Zwwhnly. Mybatisaction. Mapper. SysRoleMapper. Collection columnPrefix attributes for privilege_ rolePrivilegeListMap, So the prefix here needs to be superimposed, so it becomes role_privilege_.

Unit testing

Modify the last blog build test method testSelectAllUserAndRoles () code is:

@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 (SysRoleExtend sysRoleExtend : sysUser.getSysRoleList()) {
                System.out.println("Character name:" + sysRoleExtend.getRoleName());
                for (SysPrivilege sysPrivilege : sysRoleExtend.getSysPrivilegeList()) {
                    System.out.println("Permission name:"+ sysPrivilege.getPrivilegeName()); }}}}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, p.id role_privilege_id, p.privilege_name role_privilege_privilege_name, p.privilege_url role_privilege_privilege_url 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 INNER JOIN sys_role_privilege rp ON rp.role_id = r.id INNER JOIN sys_privilege p ON p.id = rp.privilege_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, role_privilege_id, role_privilege_privilege_name, role_privilege_privilege_url

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

TRACE [main] – <== Row: 1, admin, 123456, [email protected], 2019-06-27 18:21:07.00, 1, administrator, 1, 1, 2019-06-27 18:21:12.00, 2, Roles management, /

TRACE [main] – <== Row: 1, admin, 123456, [email protected], 2019-06-27 18:21:07.0, 1, administrator, 1, 2019-06-27 18:21:12.0, 3, system log, /logs

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

TRACE [main] – <== Row: 1, admin, 123456, [email protected], 2019-06-27 18:21:07.0, 2, general user, 1, 1, 2019-06-27 18:21:12.0, 5, unit maintenance, /companies

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

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, 5, unit maintenance, /companies

DEBUG [main] – <== Total: 7

Number of users: 2

User name: admin

Role name: Administrator

Permission name: User Management

Permission name: Role management

Permission name: system log

Role name: Common user

Permission Name: Personnel maintenance

Permission name: Unit maintenance

User name: test

Role name: Common user

Permission Name: Personnel maintenance

Permission name: Unit maintenance

The logs show that not only the roles owned by users but also the permissions of roles are queried.

4. Lazy loading

If I use the sysRoleList attribute, I can query the database only when I use the sysRoleList attribute. If I use the sysRoleList attribute, I can query the database again. The answer is yes, of course, but how?

Lazy loading requires the fetchType attribute of the Collection tag, which has lazy and eager values for lazy and active loading, respectively.

To obtain all permissions of the role based on the role Id, define the following query in sysprivilegemapper. XML:

<select id="selectPrivilegeByRoleId" resultMap="sysPrivilegeMap">
    SELECT p.*
    FROM sys_privilege p
    INNER JOIN sys_role_privilege rp ON rp.privilege_id = p.id
    WHERE rp.role_id = #{roleId}
</select>
Copy the code

Then add the following query to sysrolemapper.xml:

<resultMap id="rolePrivilegeListMapSelect" extends="roleMap"
           type="com.zwwhnly.mybatisaction.model.SysRoleExtend">
    <collection property="sysPrivilegeList" fetchType="lazy"
                column="{roleId=id}"
                select="com.zwwhnly.mybatisaction.mapper.SysPrivilegeMapper.selectPrivilegeByRoleId"/>
</resultMap>
Copy the code

<select id="selectRoleByUserId" resultMap="rolePrivilegeListMapSelect">
    SELECT
          r.id,
          r.role_name,
          r.enabled,
          r.create_by,
          r.create_time
    FROM sys_role r
    INNER JOIN sys_user_role ur ON ur.role_id = r.id
    WHERE ur.user_id = #{userId}
</select>
Copy the code

In the column=”{roleId=id}” column above, roleId refers to parameters of the select privilegebyroleid method. Id refers to the roleId queried in the selectRoleByUserId query.

Then add the following query to sysusermapper.xml:

<resultMap id="userRoleListMapSelect" extends="sysUserMap"
           type="com.zwwhnly.mybatisaction.model.SysUserExtend">
    <collection property="sysRoleList" fetchType="lazy"
                select="com.zwwhnly.mybatisaction.mapper.SysRoleMapper.selectRoleByUserId"
                column="{userId=id}"/>
</resultMap>
Copy the code

<select id="selectAllUserAndRolesSelect" resultMap="userRoleListMapSelect">
    SELECT
          u.id,
          u.user_name,
          u.user_password,
          u.user_email,
          u.create_time
    FROM sys_user u
    WHERE u.id = #{id}
</select>
Copy the code

The above column = “= {userId id}”, userId refers to the select the methods specified selectRoleByUserId parameters, id refers to the query in the query selectAllUserAndRolesSelect user id.

Then add the following method to the SysUserMapper interface:

/** * Obtain information about the specified user and the user's role and permission through nested query **@param id
 * @return* /
SysUserExtend selectAllUserAndRolesSelect(Long id);
Copy the code

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

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

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

        SysUserExtend sysUserExtend = sysUserMapper.selectAllUserAndRolesSelect(1L);
        System.out.println("Username:" + sysUserExtend.getUserName());
        for (SysRoleExtend sysRoleExtend : sysUserExtend.getSysRoleList()) {
            System.out.println("Character name:" + sysRoleExtend.getRoleName());
            for (SysPrivilege sysPrivilege : sysRoleExtend.getSysPrivilegeList()) {
                System.out.println("Permission name:"+ sysPrivilege.getPrivilegeName()); }}}finally{ sqlSession.close(); }}Copy the code

Run the test method and output the following log:

DEBUG [main] – ==> Preparing: SELECT u.id, u.user_name, u.user_password, u.user_email, u.create_time FROM sys_user u WHERE u.id = ?

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

TRACE [main] – <== Columns: id, user_name, user_password, user_email, create_time

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

DEBUG [main] – <== Total: 1

User name: admin

DEBUG [main] – ==> Preparing: SELECT r.id, r.role_name, r.enabled, r.create_by, r.create_time FROM sys_role r INNER JOIN sys_user_role ur ON ur.role_id = r.id WHERE ur.user_id = ?

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

TRACE [main] – <== Columns: id, role_name, enabled, create_by, create_time

TRACE [main] – <== Row: 1, 1, 1, 2019-06-27 18:21:12.0

TRACE [main] – <== Row: 2, 1, 1, 2019-06-27 18:21:12.0

DEBUG [main] – <== Total: 2

Role name: Administrator

DEBUG [main] – ==> Preparing: SELECT p.* FROM sys_privilege p INNER JOIN sys_role_privilege rp ON rp.privilege_id = p.id WHERE rp.role_id = ?

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

TRACE [main] – <== Columns: id, privilege_name, privilege_url

TRACE [main] – <== Row: 1, admin, /users

TRACE [main] – <== Row: 2, Roles, /roles

TRACE [main] – <== Row: 3, system logs, /logs

DEBUG [main] – <== Total: 3

Permission name: User Management

Permission name: Role management

Permission name: system log

Role name: Common user

DEBUG [main] – ==> Preparing: SELECT p.* FROM sys_privilege p INNER JOIN sys_role_privilege rp ON rp.privilege_id = p.id WHERE rp.role_id = ?

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

TRACE [main] – <== Columns: id, privilege_name, privilege_url

TRACE [main] – <== Row: 4, /persons

TRACE [main] – <== Row: 5, unit maintenance, /companies

DEBUG [main] – <== Total: 2

Permission Name: Personnel maintenance

Permission name: Unit maintenance

Careful analysis of the above logs shows that the database query was executed only when role and permission information was used.

Note that lazy loading relies on the introduction to the Association tag in MyBatis global configuration, which we configured to false in our previous blog about the Introduction to the Association tag, so this does exactly what we expected:

<settings>
    <! -- Other configuration -->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>
Copy the code

For a detailed explanation of this parameter, please see MyBatis from Getting started to Mastering (10) : Implementing nested queries with association tags.

5. To summarize

Using the Collection tag to implement nested queries, the attributes used are summarized as follows:

1) SELECT: the ID of another mapping query that MyBatis will execute to get the result of the nested object.

2)column: Use the results of the columns in the main query as parameters of the nested query, as column=”{prop1=col1,prop2=col2}”, and prop1 and prop2 as parameters of the nested query.

3)fetchType: data loading mode. The optional values are lazy and eager, which are lazy and active loading respectively.

4) In addition to setting fetchType to lazy, note that the slogs in the slogs program globally should have a value of false. This parameter defaults to true prior to version 3.4.5, and to false starting with version 3.4.5.

5) The lazyLoadTriggerMethods argument provided by MyBatis can trigger lazy-loaded attribute queries directly when a method is triggered, such as equals().

6. Source code and reference

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

MyBatis from Entry to Mastery by Liu Zenghui

MyBatis series (12) : Use collection tag to implement nested query

1. Upgrade requirements

2. Implementation method

Unit testing

4. Lazy loading

5. To summarize

6. Source code and reference

Related Posts

Mybatis architecture principle of common usage

Walk into AssertJ, the streaming assertion library for Java interface testing

Design and Implementation of Authentication and API Authority Control in Microservice Architecture (PART II)