参考文献

多表关联+多表多条件查询+分页的问题

表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
mysql> desc sys_user;
+--------------------+--------------+------+-----+-------------------+-------------------+
| Field              | Type         | Null | Key | Default           | Extra             |
+--------------------+--------------+------+-----+-------------------+-------------------+
| id                 | bigint       | NO   | PRI | NULL              | auto_increment    |
| user_id            | varchar(50)  | NO   | UNI | NULL              |                   |
| username           | varchar(50)  | NO   | MUL |                   |                   |
| password           | varchar(100) | NO   |     | NULL              |                   |
| telephone          | varchar(50)  | NO   | UNI | NULL              |                   |
| account_non_locked | tinyint      | YES  |     | 1                 |                   |
| deleted            | tinyint      | YES  |     | 1                 |                   |
| created_time       | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_time       | datetime     | NO   | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------------+--------------+------+-----+-------------------+-------------------+
9 rows in set (0.02 sec)
 
mysql> desc sys_role;
+--------------+-------------+------+-----+-------------------+-------------------+
| Field        | Type        | Null | Key | Default           | Extra             |
+--------------+-------------+------+-----+-------------------+-------------------+
| id           | bigint      | NO   | PRI | NULL              | auto_increment    |
| name         | varchar(50) | NO   |     | NULL              |                   |
| flag         | varchar(50) | NO   | UNI | NULL              |                   |
| default_role | tinyint     | YES  |     | 0                 |                   |
| sort         | int         | NO   |     | NULL              |                   |
| deleted      | tinyint     | NO   |     | 1                 |                   |
| created_time | datetime    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_time | datetime    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+-------------+------+-----+-------------------+-------------------+
8 rows in set (0.01 sec)
 
mysql> desc sys_user_role;
+--------------+-------------+------+-----+-------------------+-------------------+
| Field        | Type        | Null | Key | Default           | Extra             |
+--------------+-------------+------+-----+-------------------+-------------------+
| id           | bigint      | NO   | PRI | NULL              | auto_increment    |
| user_id      | varchar(50) | NO   | MUL | NULL              |                   |
| flag         | varchar(50) | NO   |     |                   |                   |
| created_time | datetime    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_time | datetime    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+-------------+------+-----+-------------------+-------------------+
5 rows in set (0.02 sec)
  • 分别是用户表/角色表/用户角色关联表,用户角色关联表是通过用户ID(user_id)和角色标识(flag)进行关联的

需求

  • 现在需要支持两个查询条件: 用户名称(username)模糊匹配和角色标识(flag)精确匹配,查询结果以用户信息为主并带有该用户所有的角色信息

  • 输出结果如下所示

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    {
        "message": null,
        "result": {
            "records": [
                {
                    "userId": "1245319516477337600",
                    "username": "10",
                    "telephone": "10181",
                    "accountNonLocked": true,
                    "roles": [
                        {
                            "id": 2,
                            "name": "管理员",
                            "flag": "ROLE_ADMIN"
                        },
                        {
                            "id": 1,
                            "name": "普通用户",
                            "flag": "ROLE_USER"
                        }
                    ]
                },
                {
                    "userId": "1245066120025374720",
                    "username": "GG",
                    "telephone": "101",
                    "accountNonLocked": false,
                    "roles": [
                        {
                            "id": 2,
                            "name": "管理员",
                            "flag": "ROLE_ADMIN"
                        },
                        {
                            "id": 1,
                            "name": "普通用户",
                            "flag": "ROLE_USER"
                        }
                    ]
                },
                {
                    "userId": "1245314652795863040",
                    "username": "10",
                    "telephone": "1018",
                    "accountNonLocked": false,
                    "roles": [
                        {
                            "id": 2,
                            "name": "管理员",
                            "flag": "ROLE_ADMIN"
                        },
                        {
                            "id": 1,
                            "name": "普通用户",
                            "flag": "ROLE_USER"
                        }
                    ]
                }
            ],
            "total": 3,
            "size": 10,
            "current": 1,
            "pages": 1
        },
        "status": 200,
        "code": "0",
        "timestamp": 1716949079220
    }

错误的方式

  • 对应SysUserMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 <resultMap id="sysUserRoleInfo" type="cn.holelin.test.domain.SysUserRoleInfo">
     <id column="id" property="id"/>
     <result column="user_id" property="userId"/>
     <result column="username" property="username"/>
     <result column="account_non_locked" property="accountNonLocked"/>
     <result column="telephone" property="telephone"/>
     <collection property="roles" ofType="cn.holelin.test.domain.SimpleSysRoleInfo">
         <id column="role_id" property="id"/>
         <result column="name" property="name"/>
         <result column="flag" property="flag"/>
     </collection>
</resultMap>
 
<select id="queryUserList" resultMap="sysUserRoleInfo">
     SELECT su.id,
     su.`username`,
     su.`business_id`,
     su.`user_id`,
     su.`telephone`,
     su.account_non_locked,
     sr.id AS role_id,
     sr.name,
     sr.flag,
     sr.sort
     FROM sys_user su
     LEFT JOIN sys_user_role sur ON su.business_id = sur.user_id
     LEFT JOIN sys_user_role sur ON su.user_id = sur.user_id
     LEFT JOIN sys_role sr ON sur.flag = sr.flag
     <where>
         su.`deleted` = 1 AND sr.`deleted` = 1
         <if test="request.username!=null and request.username!=''">
             AND su.username LIKE concat('%',#{request.username},'%')
         </if>
         <if test="request.roleFlag!=null and request.roleFlag!=''">
             AND sr.flag = #{request.roleFlag}
         </if>
     </where>
     ORDER BY su.created_time DESC
 </select>

  • 对应接口方法

    1
    2
    Page<SysUserRoleInfo> queryUserList(@Param("request") QueryUserRequest request,
                                        Page<QueryUserRequest> page);

  • 这样会导致最终查询出来的结果总条数错误,假设sys_user表中有三个用户分别是user1,user2,user3,这三个用户所有拥有的角色是user1有role1和role2,user2有role2和role3,user3有role1和role3

    • 期望最终查询的结果总条数为3(即用户数量),实际上总条数为6(即user和role关系的条数)

解决方法

方法一 (存在N+1问题)

  • 需要采用父子查询的方式

  • 对应SysUserMapper.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    <resultMap id="sysUserRoleInfo" type="cn.holelin.test.domain.SysUserRoleInfo">
           <id column="id" property="id"/>
           <result column="user_id" property="userId"/>
           <result column="username" property="username"/>
           <result column="account_non_locked" property="accountNonLocked"/>
           <result column="telephone" property="telephone"/>
           <collection property="roles" ofType="cn.holelin.test.domain.SimpleSysRoleInfo"
                       column="user_id" select="queryRoleInfo">
           </collection>
       </resultMap>
       <resultMap id="roleInfo" type="cn.holelin.test.domain.SimpleSysRoleInfo">
           <id column="id" property="id"/>
           <result column="name" property="name"/>
           <result column="flag" property="flag"/>
       </resultMap>
           <select id="queryUserList" resultMap="sysUserRoleInfo">
           SELECT DISTINCT su.id,
           su.`username`,
           su.`user_id`,
           su.`telephone`,
           su.account_non_locked,
           su.updated_time
           FROM sys_user su
           <if test="request.roleFlag!=null and request.roleFlag!=''">
               JOIN sys_user_role sur on su.user_id = sur.user_id
               JOIN sys_role sr on sur.flag=sr.flag
           </if>
           <where>
               su.`deleted` = 1
               <if test="request.username!=null and request.username!=''">
                   AND su.username LIKE concat(#{request.username},'%')
               </if>
               <if test="request.roleFlag!=null and request.roleFlag!=''">
                   AND sr.flag = #{request.roleFlag}
               </if>
           </where>
           ORDER BY su.updated_time DESC
       </select>
       <select id="queryRoleInfo" resultMap="roleInfo">
           SELECT sr.id,
                  sr.name,
                  sr.flag
           FROM sys_role sr
                    JOIN sys_user_role sur on sr.flag = sur.flag
           WHERE sur.user_id = ${user_id}
       </select>

方法二

实现步骤
  1. 先根据过滤条件查询user表,将符合条件的用户数据查询出来,查询出来之后,数据已经分页(且分页正确)
  2. 提取符合条件的用户ID列表,通过该列表去查询对应用户的角色信息
  3. 查询完毕后,通过用户ID将角色信息分组
  4. 最后通过用户ID,将角色信息组装到用户信息中
具体修改

  1. 修改SysUserMapper.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    <resultMap id="sysUserRoleInfo" type="com.med3d.athena.entity.SysUser">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="username" property="username"/>
        <result column="account_non_locked" property="accountNonLocked"/>
        <result column="telephone" property="telephone"/>
    </resultMap>
     <select id="queryUserList" resultMap="sysUserRoleInfo">
        SELECT DISTINCT su.id,
        su.`username`,
        su.`user_id`,
        su.`telephone`,
        su.account_non_locked,
        su.updated_time
        FROM sys_user su
        <if test="request.roleFlag!=null and request.roleFlag!=''">
            JOIN sys_user_role sur on su.user_id = sur.user_id
            JOIN sys_role sr on sur.flag=sr.flag
        </if>
        <where>
            su.`deleted` = 1
            <if test="request.username!=null and request.username!=''">
                AND su.username LIKE concat(#{request.username},'%')
            </if>
            <if test="request.roleFlag!=null and request.roleFlag!=''">
                AND sr.flag = #{request.roleFlag}
            </if>
        </where>
        ORDER BY su.updated_time DESC
    </select>

    SysUserRoleMapper.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    <select id="queryByUserIdList" resultType="com.med3d.athena.domain.SysRoleWithUserIdInfo">
        SELECT r.flag,r.name,sur.user_id
        FROM sys_user_role sur
        LEFT JOIN sys_role r ON sur.flag = r.flag
        WHERE sur.user_id IN
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
    </select>

  2. SysUserService.java

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    public Page<UserInfoResponse> queryUserList(QueryUserRequest request) {
        final Page<QueryUserRequest> page = Page.of(request.getPageNo(), request.getPageSize());
        Page<UserInfoResponse> result = Page.of(request.getPageNo(), request.getPageSize());
        // 查询符合条件的用户数据
        final Page<SysUser> sysUserPage = sysUserMapper.queryUserList(request, page);
        // 拷贝分页信息
        BeanUtil.copyProperties(sysUserPage, result);
        final List<SysUser> records = sysUserPage.getRecords();
        if (CollUtil.isNotEmpty(records)) {
            // 根据查询出来的用户ID列表查询对应的角色信息
            final List<String> userIds = records.stream().map(SysUser::getUserId).collect(Collectors.toList());
            final List<SysRoleWithUserIdInfo> sysRoleInfos = sysUserRoleMapper.queryByUserIdList(userIds);
            Map<String, List<SysRoleWithUserIdInfo>> roleInfoGroup = Maps.newHashMap();
            if (CollUtil.isNotEmpty(sysRoleInfos)) {
                roleInfoGroup = sysRoleInfos.stream().collect(Collectors.groupingBy(SysRoleWithUserIdInfo::getUserId));
            }
            Map<String, List<SysRoleWithUserIdInfo>> finalRoleInfoGroup = roleInfoGroup;
            final List<UserInfoResponse> responseList = records.stream().map(item -> {
                final UserInfoResponse userInfoResponse = new UserInfoResponse();
                final String userId = item.getUserId();
                BeanUtil.copyProperties(item, userInfoResponse);
                if (finalRoleInfoGroup.containsKey(userId)) {
                    final List<SysRoleWithUserIdInfo> roleInfos = finalRoleInfoGroup.get(userId);
                    final List<SimpleSysRoleInfo> roles = roleInfos.stream().map(role -> {
                        final SimpleSysRoleInfo simpleSysRoleInfo = new SimpleSysRoleInfo();
                        BeanUtil.copyProperties(role, simpleSysRoleInfo);
                        return simpleSysRoleInfo;
                    }).collect(Collectors.toList());
                    userInfoResponse.setRoles(roles);
                }
                return userInfoResponse;
            }).collect(Collectors.toList());
            result.setRecords(responseList);
        }
        return result;
    }