1、创建数据库和数据表(以Mysql数据库为例) 设计一个权限管理系统。 表设计如下:用户表、角色表、菜单表 使用Mybaits构建用户、角色、菜单的映射关系 1、用户表(UserInfo):Id、UserName、UserPwd,userSex,userSchool,userAddress 2、角色表(RoleInfo):Id、RoleName 3、菜单表(MenuInfo):Id、MenuName 4、用户角色表(UserRole):Id、UserId、RoleId; 5、角色菜单表(RoleMenu):Id、RoleId、MenuId 2、1.用户和角色之间的关系 2.角色和菜单之间的关系查询编写单元测试用例。 3.使用动态sql拼接的方式实现在一个接口内进行用户名、用户地址、用户性别的筛选。
时间: 2023-06-11 07:08:05 浏览: 319
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):
```java
public class UserInfo {
private int id;
private String userName;
private String userPwd;
private String userSex;
private String userSchool;
private String userAddress;
// 省略 getter 和 setter 方法
}
```
角色实体类(RoleInfo):
```java
public class RoleInfo {
private int id;
private String roleName;
// 省略 getter 和 setter 方法
}
```
菜单实体类(MenuInfo):
```java
public class MenuInfo {
private int id;
private String menuName;
// 省略 getter 和 setter 方法
}
```
用户角色映射实体类(UserRole):
```java
public class UserRole {
private int id;
private int userId;
private int roleId;
// 省略 getter 和 setter 方法
}
```
角色菜单映射实体类(RoleMenu):
```java
public class RoleMenu {
private int id;
private int roleId;
private int menuId;
// 省略 getter 和 setter 方法
}
```
用户映射文件(UserInfoMapper.xml):
```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
<?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
<?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
<?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
<?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. 单元测试用例
接下来我们编写单元测试用例,测试用户和角色之间的关系,角色和菜单之间的关系。
测试用户和角色之间的关系:
```java
@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);
}
}
```
测试角色和菜单之间的关系:
```java
@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 拼接实现筛选
接口定义:
```java
public interface UserInfoMapper {
List<UserInfo> getUserByCondition(@Param("userName") String userName, @Param("userAddress") String userAddress, @Param("userSex") String userSex);
}
```
映射文件实现:
```xml
<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>
```
这样,我们就完成了权限管理系统的设计和实现。
阅读全文