RBAC与连表查询

0. 前言

连表学习、练习经典：RBAC权限模型、5表联查。

本文采用的持久层实现方式：Mybatis/Mybatis-Plus。

学习基础: SQL基础。

学习目标：

• 了解 RBAC 权限模型，建表
• 学习并掌握连表操作

1. RBAC模型

1.1 什么是RBAC模型

RBAC（Role-BasedAccess Control），基于角色的访问控制。

简单来说，就是什么样的用户有什么样的权利，RBAC的一种经典的实现方式为：
用户-角色-权限，都是 N:N 的关系。本文的 Mybatis 联查练习就以RBAC模型为例。

详细概念可自行百度或者参阅下方的参考资料：
参考资料：
简书参考文章
知乎参考文章

1.2 准备工作

准备表：
表结构与即将进行的关联关系：

建表语句：

建库：

CTEATE database rbac_sys;
USE rbac_sys;

建菜单表、插入测试数据：

DROP TABLE IF EXISTS `sys_menus`;
CREATE TABLE `sys_menus`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源名称',
  `url` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源URL',
  `type` int(11) NULL DEFAULT NULL COMMENT '类型     1：菜单   2：按钮',
  `sort` int(11) NULL DEFAULT NULL COMMENT '排序',
  `note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `parentId` int(11) NULL DEFAULT NULL COMMENT '父菜单ID，一级菜单为0',
  `permission` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '授权(如：user:create)',
  `createdTime` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `modifiedTime` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  `createdUser` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建用户',
  `modifiedUser` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 144 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '资源管理' ROW_FORMAT = Dynamic;

测试数据：

INSERT INTO `sys_menus` VALUES (8, '系统管理', '请求路径', 1, 8, NULL, NULL, '', '2017-07-12 15:15:59', '2020-01-12 14:11:15', 'admin', NULL);
INSERT INTO `sys_menus` VALUES (25, '日志管理', 'log/log_list', 1, 25, NULL, 8, 'sys:log:view', '2017-07-12 15:15:59', '2020-01-12 14:09:44', 'admin', NULL);
INSERT INTO `sys_menus` VALUES (45, '用户管理', 'user/user_list', 1, 45, NULL, 8, 'sys:user:view', '2017-07-12 15:15:59', '2020-01-12 14:10:10', 'admin', NULL);
INSERT INTO `sys_menus` VALUES (46, '菜单管理', 'menu/menu_list', 1, 46, NULL, 8, 'sys:menu:view', '2017-07-12 15:15:59', '2020-01-12 14:10:28', 'admin', NULL);
INSERT INTO `sys_menus` VALUES (47, '角色管理', 'role/role_list', 1, 47, NULL, 8, 'sys:role:view', '2017-07-12 15:15:59', '2020-01-12 14:10:48', 'admin', NULL);
INSERT INTO `sys_menus` VALUES (115, '查询', 'menu/doFindObjects', 2, 1, NULL, 46, 'sys:menu:view', '2017-07-13 16:33:41', '2020-01-12 14:21:29', NULL, NULL);
INSERT INTO `sys_menus` VALUES (116, '添加', 'menu/doSaveObject', 2, 2, NULL, 46, 'sys:menu:add', '2017-07-13 16:34:02', '2020-01-12 14:21:56', NULL, NULL);
INSERT INTO `sys_menus` VALUES (117, '修改', 'menu/doUpdateObject', 2, 3, NULL, 46, 'sys:menu:update', '2017-07-13 16:34:25', '2020-01-12 14:22:12', NULL, NULL);
INSERT INTO `sys_menus` VALUES (118, '删除', 'role/doDeleteObject', 2, 4, NULL, 46, 'sys:menu:delete', '2017-07-13 16:34:46', '2020-01-12 14:22:41', NULL, NULL);
INSERT INTO `sys_menus` VALUES (119, '查询', 'user/doFindPageObjects', 2, 1, NULL, 45, 'sys:user:view', '2017-07-13 16:35:05', '2020-01-12 14:20:28', NULL, NULL);
INSERT INTO `sys_menus` VALUES (120, '查询', 'role/doFindPageObjects', 2, 1, NULL, 47, 'sys:role:view', '2017-07-13 16:35:26', '2020-01-12 14:23:05', NULL, NULL);
INSERT INTO `sys_menus` VALUES (126, '新增', 'user/doSaveObject', 2, 2, NULL, 45, 'sys:user:add', '2017-07-21 11:11:34', '2020-01-12 14:20:45', NULL, NULL);
INSERT INTO `sys_menus` VALUES (127, '修改', 'user/doUpdateObject', 2, 3, NULL, 45, 'sys:user:update', '2017-07-21 11:11:56', '2020-01-12 14:21:05', NULL, NULL);
INSERT INTO `sys_menus` VALUES (128, '添加', 'role/doSaveObject', 2, 2, NULL, 47, 'sys:role:add', '2017-07-21 11:14:24', '2020-01-12 14:23:29', NULL, NULL);
INSERT INTO `sys_menus` VALUES (129, '修改', 'role/doUpdateObject', 2, 3, NULL, 47, 'sys:role:update', '2017-07-21 11:14:48', '2020-01-12 14:23:44', NULL, NULL);
INSERT INTO `sys_menus` VALUES (130, '删除', 'role/doDeleteObject', 2, 4, NULL, 47, 'sys:role:delete', '2017-07-21 11:15:09', '2020-01-12 14:24:05', NULL, NULL);
INSERT INTO `sys_menus` VALUES (131, '删除', 'log/doDeleteObjects', 2, 27, NULL, 25, 'sys:log:delete', '2020-01-10 17:34:31', '2020-01-10 17:34:31', NULL, NULL);
INSERT INTO `sys_menus` VALUES (137, '禁用启用', 'user/doValidById', 2, 123, NULL, 45, 'sys:user:update', '2020-01-12 09:34:58', '2020-01-12 09:34:58', NULL, NULL);
INSERT INTO `sys_menus` VALUES (138, '部门管理', 'dept/dept_list', 1, 100, NULL, 8, 'sys:dept:view', '2020-01-12 14:15:45', '2020-01-12 14:15:59', NULL, NULL);
INSERT INTO `sys_menus` VALUES (139, '添加', 'dept/doSaveObject', 2, 123, NULL, 138, 'sys:dept:add', '2020-01-12 14:16:33', '2020-01-12 14:16:33', NULL, NULL);
INSERT INTO `sys_menus` VALUES (140, '修改', 'dept/dept_edit', 1, 121, NULL, 138, 'sys:dept:update', '2020-01-12 14:17:14', '2020-01-12 14:17:14', NULL, NULL);
INSERT INTO `sys_menus` VALUES (141, '删除', 'dept/doDeleteObject', 1, 120, NULL, 138, 'sys:dept:delete', '2020-01-12 14:18:20', '2020-01-12 14:18:20', NULL, NULL);
INSERT INTO `sys_menus` VALUES (142, '查询', 'dept/doFindObjects', 1, 124, NULL, 138, 'sys:dept:view', '2020-01-12 14:19:01', '2020-01-12 14:19:01', NULL, NULL);
INSERT INTO `sys_menus` VALUES (143, '查询', 'log/doFindPageObjects', 2, 210, NULL, 25, 'sys:log:view', '2020-01-12 14:20:02', '2020-01-12 14:20:02', NULL, NULL);

建角色-菜单关联表并插入测试数据：

DROP TABLE IF EXISTS `sys_role_menus`;
CREATE TABLE `sys_role_menus`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NULL DEFAULT NULL COMMENT '角色ID',
  `menu_id` int(11) NULL DEFAULT NULL COMMENT 'ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1308 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色与菜单对应关系' ROW_FORMAT = Dynamic;

测试数据：

INSERT INTO `sys_role_menus` VALUES (1250, 46, 8);
INSERT INTO `sys_role_menus` VALUES (1252, 46, 47);
INSERT INTO `sys_role_menus` VALUES (1253, 46, 120);
INSERT INTO `sys_role_menus` VALUES (1254, 46, 128);
INSERT INTO `sys_role_menus` VALUES (1255, 46, 129);
INSERT INTO `sys_role_menus` VALUES (1256, 46, 130);
INSERT INTO `sys_role_menus` VALUES (1294, 1, 8);
INSERT INTO `sys_role_menus` VALUES (1296, 1, 25);
INSERT INTO `sys_role_menus` VALUES (1297, 1, 131);
INSERT INTO `sys_role_menus` VALUES (1298, 1, 46);
INSERT INTO `sys_role_menus` VALUES (1299, 1, 115);
INSERT INTO `sys_role_menus` VALUES (1300, 1, 116);
INSERT INTO `sys_role_menus` VALUES (1301, 1, 117);
INSERT INTO `sys_role_menus` VALUES (1302, 1, 118);
INSERT INTO `sys_role_menus` VALUES (1303, 1, 47);
INSERT INTO `sys_role_menus` VALUES (1304, 1, 120);
INSERT INTO `sys_role_menus` VALUES (1305, 1, 128);
INSERT INTO `sys_role_menus` VALUES (1306, 1, 129);
INSERT INTO `sys_role_menus` VALUES (1307, 1, 130);

建角色表并插入测试数据：

DROP TABLE IF EXISTS `sys_roles`;
CREATE TABLE `sys_roles`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色名称',
  `note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `createdTime` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `modifiedTime` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  `createdUser` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建用户',
  `modifiedUser` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 47 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色' ROW_FORMAT = Dynamic;

测试数据：

INSERT INTO `sys_roles` VALUES (1, '系统管理员', '系统管理员', '2017-07-13 17:44:11', '2020-01-12 09:54:51', 'admin', NULL);
INSERT INTO `sys_roles` VALUES (46, '软件工程师', '负责软件设计及研发', '2020-01-11 15:52:00', '2020-01-11 15:52:00', NULL, NULL);

建角色-用户关联表并插入测试数据：

DROP TABLE IF EXISTS `sys_user_roles`;
CREATE TABLE `sys_user_roles`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL COMMENT '用户ID',
  `role_id` int(11) NULL DEFAULT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 70 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户与角色对应关系' ROW_FORMAT = Dynamic;

测试数据：

INSERT INTO `sys_user_roles` VALUES (68, 1, 1);
INSERT INTO `sys_user_roles` VALUES (69, 17, 46);

建用户表并插入测试数据：

DROP TABLE IF EXISTS `sys_users`;
CREATE TABLE `sys_users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `salt` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '盐  密码加密时前缀，使加密后的值不同',
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `mobile` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `valid` tinyint(4) NULL DEFAULT NULL COMMENT '状态  0：禁用   1：正常  默认值 ：1',
  `deptId` int(11) NULL DEFAULT NULL,
  `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户头像',
  `createdTime` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `modifiedTime` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  `createdUser` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建用户',
  `modifiedUser` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统用户' ROW_FORMAT = Dynamic;

测试数据：

INSERT INTO `sys_users` VALUES (1, 'admin', 'c4c33035c5d8e840616c128db9f87b25', '016a0948-b581-43aa-8a5f-9bb76a80e737', 'admin@t.cn', '13624356789', 1, 3, NULL, NULL, '2020-01-12 09:35:43', NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

准备工程：

依赖：

       <!--    SpringBoot 相关    -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--    MybatisPlus    -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>

        <!--   Mysql     -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--    Lombok    -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency> 

application.yml配置：

spring:
  # 配置数据源
  datasource:
    url: jdbc:mysql://localhost:3306/rbac_sys?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
    username: root
    password: root

实体类：

/**
 * 用户菜单实体类VO
 * @author: Sharry
 * @createTime: 2022/12/16 14:06
 * @version: Version-1.0
 */
@Data
public class SysUserMenuVO implements Serializable {

    private static final long serialVersionUID = -5812179473590409216L;
    
    /**
     * 用户名
     */
    private String name;

    /**
     * 菜单列表
     */
    private List<SysMenuVO> menus;
}

/**
 * 联查：用户权限菜单VO
 * @author: Sharry
 * @createTime: 2022/12/16 13:42
 * @version: Version-1.0
 */
@Data
public class SysMenuVO implements Serializable {

    private static final long serialVersionUID = 8510310099317612636L;

    /**
     * 资源名称
     */
    private String name;

    /**
     * 资源路径
     */
    private String url;

    /**
     * 类型  1：菜单   2：按钮
     */
    private Integer type;

    /**
     * 备注
     */
    private String note;

    /**
     * 授权(如：user:create)
     */
    private String permission;


}

配置类：

/**
 * @author: Sharry
 * @createTime: 2022/12/16 16:15
 * @version: Version-1.0
 */
@Configuration
@MapperScan("cn.sharry.rbaclearning.mapper")
public class MybatisPlusConfig {
}

2. 连表操作

2.1 常用连表图

我们常用 INNER JOIN, RIGHT JOIN, LEFT JOIN 来进行连表。其中，最常用的是 INNER JOIN 、 LEFT JOIN。至于还有其它连表方式，请读者自行查阅。

接下来，我们参考下图进行SQL语句的准备。

注：图片来源于网络，侵删。

2.2 准备SQL语句

根据VO需要的信息，5表联查：

SELECT su.username, sm.`name`, sm.url, sm.type, sm.note, sm.permission

FROM sys_users su JOIN sys_user_roles sur

ON su.id = sur.user_id

JOIN sys_roles sr 

ON sur.role_id = sr.id 

JOIN sys_role_menus srm

ON sr.id = srm.role_id

JOIN sys_menus sm 

ON srm.menu_id = sm.id

WHERE su.id = 1

根据VO需要的信息，4表联查：

SELECT su.username, sm.`name`, sm.url, sm.type, sm.note, sm.permission

FROM sys_users su 

JOIN sys_user_roles sur

ON su.id = sur.user_id 

JOIN sys_role_menus srm

ON sur.role_id = srm.role_id

JOIN sys_menus sm

ON srm.menu_id = sm.id

WHERE su.id = 1

2.3 Mybatis 连表

根据上面我们提前测试好的连表SQL语句，在Mybatis 中进行测试：

编写Mapper接口

/**
 * @author: Sharry
 * @createTime: 2022/12/16 14:57
 * @version: Version-1.0
 */
@Repository
public interface SysUserMenuMapper {

    /**
     * 根据用户 id 查询用户权限
     * @param id 用户id
     * @return 权限菜单VO
     */
    SysUserMenuVO findUserMenuById(Integer id);

}

编辑Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.sharry.rbaclearning.mapper.SysUserMenuMapper">

    <resultMap id="userMenuResultMap" type="cn.sharry.rbaclearning.pojo.vo.SysUserMenuVO">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <collection property="menus" ofType="cn.sharry.rbaclearning.pojo.vo.SysMenuVO">
            <result column="name" property="name"></result>
            <result column="note" property="note"></result>
            <result column="url" property="url"></result>
            <result column="type" property="type"></result>
            <result column="permission" property="permission"></result>
        </collection>
    </resultMap>

    <!--  根据用户 id 查询用户权限  -->
    <select id="findUserMenuById" resultMap="userMenuResultMap">
        SELECT su.id, su.username, sm.name, sm.url, 
               sm.type, sm.note, sm.permission

        FROM sys_users su

                 JOIN sys_user_roles sur
                 ON su.id = sur.user_id

                 JOIN sys_role_menus srm
                 ON sur.role_id = srm.role_id

                 JOIN sys_menus sm
                 ON srm.menu_id = sm.id

        WHERE su.id = #{id}
    </select>
</mapper>

编写测试类

/**
 * 连表查询测试类
 * @author: Sharry
 * @createTime: 2022/12/16 16:03
 * @version: Version-1.0
 */
@SpringBootTest
public class SysUserMenuTests {

    @Autowired
    private SysUserMenuMapper userMenuMapper;

    /**
     * 测试根据id 获取权限菜单
     */
    @Test
    public void testGetMenu(){
        System.out.println(userMenuMapper.findUserMenuById(1));
    }
}

3. 总结与补充

补充
读者在按照上面列举的例子进行练习时，可能会遇到联查时，返回的POJO VO（此处以VO为例，实际的命名遵循各自参考的规范）有属性是集合，但实际返回只有1条数据，甚至异常/报错的情况。

解决方案：如果VO里的集合id不是必要的返回值，可以选择修改VO结构，和小编举的例子一样，xml及VO将id属性去掉，避免主表与从表字段名一致而产生冲突。当然，我们也可以采取取别名（与查询的别名要保持一致）、修改表字段等手段去解决。

总结
本文先简单介绍了一下RBAC权限模型，工作中可能会用到。然后我们使用一个基于RBAC权限模型的例子，举例并练习了多对多连表查询，以Mybatis/Mybatis-Plus 为例。然后多加练习吧！