(8) 在数据库Student中使用CREATE ROLE（或sp_addrole）创建一个名为user_role数据库角色。

如果您正在使用 PostgreSQL 数据库，可以使用以下命令创建名为 user_role 的数据库角色：

CREATE ROLE user_role;

如果您正在使用 Microsoft SQL Server 数据库，可以使用以下命令创建名为 user_role 的数据库角色：

sp_addrole 'user_role';

请注意，这些命令需要在具有足够权限的用户身份下执行。

相关问题

authorizationInfo.addRole(user.getRole());中的Role是什么类型，应该对它怎么赋值

Role是一个自定义的类，它表示用户角色。可以根据具体业务需求定义Role类的属性和方法。在authorizationInfo.addRole(user.getRole())中，应该将用户的角色赋值给Role对象，可以通过user.getRole()获取用户的角色信息，然后将其赋值给Role对象。例如：

Role role = new Role();
role.setName(user.getRole());
authorizationInfo.addRole(role);

这里假设用户的角色信息被保存在User对象中的role属性中，可以通过user.getRole()获取到。然后创建一个Role对象，将角色信息设置给Role对象，最后将Role对象添加到authorizationInfo中。

1、创建数据库和数据表（以Mysql数据库为例） 设计一个权限管理系统。 表设计如下：用户表、角色表、菜单表 使用Mybaits构建用户、角色、菜单的映射关系 １、用户表（UserInfo）：Id、UserName、UserPwd，userSex，userSchool，userAddress ２、角色表（RoleInfo）：Id、RoleName ３、菜单表（MenuInfo）：Id、MenuName ４、用户角色表（UserRole）：Id、UserId、RoleId; ５、角色菜单表（RoleMenu）：Id、RoleId、MenuId 2、1.用户和角色之间的关系 2.角色和菜单之间的关系查询编写单元测试用例。 3.使用动态sql拼接的方式实现在一个接口内进行用户名、用户地址、用户性别的筛选。

1. 创建数据库和数据表

我们以 MySQL 数据库为例，先创建一个名为 permissions 的数据库，然后在该数据库中创建以下三个表：

用户表（user_info）：

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL,
  `user_pwd` varchar(50) NOT NULL,
  `user_sex` varchar(10) DEFAULT NULL,
  `user_school` varchar(100) DEFAULT NULL,
  `user_address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

角色表（role_info）：

CREATE TABLE `role_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

菜单表（menu_info）：

CREATE TABLE `menu_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `menu_name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

用户角色表（user_role）：

CREATE TABLE `user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `role_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

角色菜单表（role_menu）：

CREATE TABLE `role_menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `menu_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `role_id` (`role_id`),
  KEY `menu_id` (`menu_id`),
  CONSTRAINT `role_menu_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `role_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `role_menu_ibfk_2` FOREIGN KEY (`menu_id`) REFERENCES `menu_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 构建用户、角色、菜单的映射关系

使用 MyBatis 构建用户、角色、菜单的映射关系，需要创建对应的实体类和映射文件。

用户实体类（UserInfo）：

public class UserInfo {
    private int id;
    private String userName;
    private String userPwd;
    private String userSex;
    private String userSchool;
    private String userAddress;
    // 省略 getter 和 setter 方法
}

角色实体类（RoleInfo）：

public class RoleInfo {
    private int id;
    private String roleName;
    // 省略 getter 和 setter 方法
}

菜单实体类（MenuInfo）：

public class MenuInfo {
    private int id;
    private String menuName;
    // 省略 getter 和 setter 方法
}

用户角色映射实体类（UserRole）：

public class UserRole {
    private int id;
    private int userId;
    private int roleId;
    // 省略 getter 和 setter 方法
}

角色菜单映射实体类（RoleMenu）：

public class RoleMenu {
    private int id;
    private int roleId;
    private int menuId;
    // 省略 getter 和 setter 方法
}

用户映射文件（UserInfoMapper.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="com.example.mapper.UserInfoMapper">
  <resultMap id="userResultMap" type="com.example.entity.UserInfo">
    <id column="id" property="id" />
    <result column="user_name" property="userName" />
    <result column="user_pwd" property="userPwd" />
    <result column="user_sex" property="userSex" />
    <result column="user_school" property="userSchool" />
    <result column="user_address" property="userAddress" />
  </resultMap>
  
  <select id="getUserById" resultType="com.example.entity.UserInfo">
    SELECT * FROM user_info WHERE id = #{id}
  </select>
  
  <select id="getUserByName" resultType="com.example.entity.UserInfo">
    SELECT * FROM user_info WHERE user_name = #{userName}
  </select>
  
  <select id="getUserByAddress" resultMap="userResultMap">
    SELECT * FROM user_info WHERE user_address = #{userAddress}
  </select>
  
  <insert id="addUser" parameterType="com.example.entity.UserInfo" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user_info (user_name, user_pwd, user_sex, user_school, user_address)
    VALUES (#{userName}, #{userPwd}, #{userSex}, #{userSchool}, #{userAddress})
  </insert>
  
  <update id="updateUser" parameterType="com.example.entity.UserInfo">
    UPDATE user_info SET user_name = #{userName}, user_pwd = #{userPwd}, user_sex = #{userSex},
    user_school = #{userSchool}, user_address = #{userAddress} WHERE id = #{id}
  </update>
  
  <delete id="deleteUserById">
    DELETE FROM user_info WHERE id = #{id}
  </delete>
</mapper>

角色映射文件（RoleInfoMapper.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="com.example.mapper.RoleInfoMapper">
  <resultMap id="roleResultMap" type="com.example.entity.RoleInfo">
    <id column="id" property="id" />
    <result column="role_name" property="roleName" />
  </resultMap>
  
  <select id="getRoleById" resultType="com.example.entity.RoleInfo">
    SELECT * FROM role_info WHERE id = #{id}
  </select>
  
  <select id="getRoleByName" resultType="com.example.entity.RoleInfo">
    SELECT * FROM role_info WHERE role_name = #{roleName}
  </select>
  
  <insert id="addRole" parameterType="com.example.entity.RoleInfo" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO role_info (role_name) VALUES (#{roleName})
  </insert>
  
  <update id="updateRole" parameterType="com.example.entity.RoleInfo">
    UPDATE role_info SET role_name = #{roleName} WHERE id = #{id}
  </update>
  
  <delete id="deleteRoleById">
    DELETE FROM role_info WHERE id = #{id}
  </delete>
</mapper>

菜单映射文件（MenuInfoMapper.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="com.example.mapper.MenuInfoMapper">
  <resultMap id="menuResultMap" type="com.example.entity.MenuInfo">
    <id column="id" property="id" />
    <result column="menu_name" property="menuName" />
  </resultMap>
  
  <select id="getMenuById" resultType="com.example.entity.MenuInfo">
    SELECT * FROM menu_info WHERE id = #{id}
  </select>
  
  <select id="getMenuByName" resultType="com.example.entity.MenuInfo">
    SELECT * FROM menu_info WHERE menu_name = #{menuName}
  </select>
  
  <insert id="addMenu" parameterType="com.example.entity.MenuInfo" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO menu_info (menu_name) VALUES (#{menuName})
  </insert>
  
  <update id="updateMenu" parameterType="com.example.entity.MenuInfo">
    UPDATE menu_info SET menu_name = #{menuName} WHERE id = #{id}
  </update>
  
  <delete id="deleteMenuById">
    DELETE FROM menu_info WHERE id = #{id}
  </delete>
</mapper>

用户角色映射文件（UserRoleMapper.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="com.example.mapper.UserRoleMapper">
  <insert id="addUserRole" parameterType="com.example.entity.UserRole">
    INSERT INTO user_role (user_id, role_id) VALUES (#{userId}, #{roleId})
  </insert>
  
  <delete id="deleteUserRoleByUserId">
    DELETE FROM user_role WHERE user_id = #{userId}
  </delete>
  
  <delete id="deleteUserRoleByRoleId">
    DELETE FROM user_role WHERE role_id = #{roleId}
  </delete>
</mapper>

角色菜单映射文件（RoleMenuMapper.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="com.example.mapper.RoleMenuMapper">
  <insert id="addRoleMenu" parameterType="com.example.entity.RoleMenu">
    INSERT INTO role_menu (role_id, menu_id) VALUES (#{roleId}, #{menuId})
  </insert>
  
  <delete id="deleteRoleMenuByRoleId">
    DELETE FROM role_menu WHERE role_id = #{roleId}
  </delete>
  
  <delete id="deleteRoleMenuByMenuId">
    DELETE FROM role_menu WHERE menu_id = #{menuId}
  </delete>
</mapper>

3. 单元测试用例

接下来我们编写单元测试用例，测试用户和角色之间的关系，角色和菜单之间的关系。

测试用户和角色之间的关系：

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class UserRoleMapperTest {
    @Autowired
    private UserRoleMapper userRoleMapper;
    @Autowired
    private RoleInfoMapper roleInfoMapper;
    @Autowired
    private UserInfoMapper userInfoMapper;

    @Test
    public void testAddUserRole() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUserName("testUser");
        userInfo.setUserPwd("testPwd");
        userInfo.setUserSex("男");
        userInfo.setUserSchool("testSchool");
        userInfo.setUserAddress("testAddress");
        userInfoMapper.addUser(userInfo);
        RoleInfo roleInfo = new RoleInfo();
        roleInfo.setRoleName("testRole");
        roleInfoMapper.addRole(roleInfo);
        UserRole userRole = new UserRole();
        userRole.setUserId(userInfo.getId());
        userRole.setRoleId(roleInfo.getId());
        userRoleMapper.addUserRole(userRole);
        UserRole result = userRoleMapper.getUserRoleById(userRole.getId());
        assertNotNull(result);
    }

    @Test
    public void testGetUserByRoleId() {
        UserInfo userInfo1 = new UserInfo();
        userInfo1.setUserName("testUser1");
        userInfo1.setUserPwd("testPwd1");
        userInfo1.setUserSex("男");
        userInfo1.setUserSchool("testSchool1");
        userInfo1.setUserAddress("testAddress1");
        userInfoMapper.addUser(userInfo1);
        UserInfo userInfo2 = new UserInfo();
        userInfo2.setUserName("testUser2");
        userInfo2.setUserPwd("testPwd2");
        userInfo2.setUserSex("女");
        userInfo2.setUserSchool("testSchool2");
        userInfo2.setUserAddress("testAddress2");
        userInfoMapper.addUser(userInfo2);
        RoleInfo roleInfo = new RoleInfo();
        roleInfo.setRoleName("testRole");
        roleInfoMapper.addRole(roleInfo);
        UserRole userRole1 = new UserRole();
        userRole1.setUserId(userInfo1.getId());
        userRole1.setRoleId(roleInfo.getId());
        userRoleMapper.addUserRole(userRole1);
        UserRole userRole2 = new UserRole();
        userRole2.setUserId(userInfo2.getId());
        userRole2.setRoleId(roleInfo.getId());
        userRoleMapper.addUserRole(userRole2);
        List<UserInfo> userList = userRoleMapper.getUserByRoleId(roleInfo.getId());
        assertEquals(userList.size(), 2);
    }
}

测试角色和菜单之间的关系：

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class RoleMenuMapperTest {
    @Autowired
    private RoleMenuMapper roleMenuMapper;
    @Autowired
    private MenuInfoMapper menuInfoMapper;
    @Autowired
    private RoleInfoMapper roleInfoMapper;

    @Test
    public void testAddRoleMenu() {
        MenuInfo menuInfo = new MenuInfo();
        menuInfo.setMenuName("testMenu");
        menuInfoMapper.addMenu(menuInfo);
        RoleInfo roleInfo = new RoleInfo();
        roleInfo.setRoleName("testRole");
        roleInfoMapper.addRole(roleInfo);
        RoleMenu roleMenu = new RoleMenu();
        roleMenu.setRoleId(roleInfo.getId());
        roleMenu.setMenuId(menuInfo.getId());
        roleMenuMapper.addRoleMenu(roleMenu);
        RoleMenu result = roleMenuMapper.getRoleMenuById(roleMenu.getId());
        assertNotNull(result);
    }

    @Test
    public void testGetMenuByRoleId() {
        MenuInfo menuInfo1 = new MenuInfo();
        menuInfo1.setMenuName("testMenu1");
        menuInfoMapper.addMenu(menuInfo1);
        MenuInfo menuInfo2 = new MenuInfo();
        menuInfo2.setMenuName("testMenu2");
        menuInfoMapper.addMenu(menuInfo2);
        RoleInfo roleInfo = new RoleInfo();
        roleInfo.setRoleName("testRole");
        roleInfoMapper.addRole(roleInfo);
        RoleMenu roleMenu1 = new RoleMenu();
        roleMenu1.setRoleId(roleInfo.getId());
        roleMenu1.setMenuId(menuInfo1.getId());
        roleMenuMapper.addRoleMenu(roleMenu1);
        RoleMenu roleMenu2 = new RoleMenu();
        roleMenu2.setRoleId(roleInfo.getId());
        roleMenu2.setMenuId(menuInfo2.getId());
        roleMenuMapper.addRoleMenu(roleMenu2);
        List<MenuInfo> menuList = roleMenuMapper.getMenuByRoleId(roleInfo.getId());
        assertEquals(menuList.size(), 2);
    }
}

4. 动态 SQL 拼接实现筛选

接口定义：

public interface UserInfoMapper {
    List<UserInfo> getUserByCondition(@Param("userName") String userName, @Param("userAddress") String userAddress, @Param("userSex") String userSex);
}

映射文件实现：

<select id="getUserByCondition" resultType="com.example.entity.UserInfo">
    SELECT * FROM user_info
    <where>
        <if test="userName != null and userName != ''">
            AND user_name = #{userName}
        </if>
        <if test="userAddress != null and userAddress != ''">
            AND user_address = #{userAddress}
        </if>
        <if test="userSex != null and userSex != ''">
            AND user_sex = #{userSex}
        </if>
    </where>
</select>

这样，我们就完成了权限管理系统的设计和实现。