【MyBatis】XML练习题-权限管理

小鱼儿 2024-03-22 12:35 23阅读 0赞

**目录**

1、数据库

2、需求

3、实现

## 1、数据库 ##

# 用户表
    create table sys_user(
      `user_id` int primary key auto_increment comment '用户ID',
    	`user_name` varchar(50) comment '用户名',
    	`password` varchar(32) comment '用户密码' ,
    	`desc` varchar(200) comment '个人介绍'
    );
    
    insert into sys_user(`user_id`,`user_name`,`password`,`desc`) values(1,'jack','1234','这是一个男生');
    insert into sys_user(`user_id`,`user_name`,`password`,`desc`) values(2,'rose','1234','这是一个女生');
    insert into sys_user(`user_id`,`user_name`,`password`,`desc`) values(3,'rose','1234','这是一个女生');
    
    # 角色表
    create table sys_role(
      `role_id` varchar(32) primary key comment '角色ID',
    	`role_name` varchar(50) comment '角色名',
    	`desc` varchar(200) comment '角色介绍'
    );
    
    insert into sys_role(`role_id`,`role_name`,`desc`) values('r001','管理员','拥有所有的权限');
    insert into sys_role(`role_id`,`role_name`,`desc`) values('r002','财务经理','拥有所有财务权限');
    insert into sys_role(`role_id`,`role_name`,`desc`) values('r003','开发经理','拥有所有开发权限');
    insert into sys_role(`role_id`,`role_name`,`desc`) values('r004','销售经理','拥有所有销售权限');
    
    
    CREATE TABLE `sys_permission`  (
      `perm_id` varchar(32) primary key comment '权限ID',
      `perm_name` varchar(50) comment '权限名字' ,
      `parent_id` varchar(32) comment '父权限ID',
      `path` varchar(255) comment '权限路径'
    );
    
    INSERT INTO `sys_permission` VALUES ('p001', '财务管理', '0', '/finance/');
    INSERT INTO `sys_permission` VALUES ('p002', '查询财务', 'p001', '/finance/list');
    INSERT INTO `sys_permission` VALUES ('p003', '添加财务', 'p001', '/finance/add');
    INSERT INTO `sys_permission` VALUES ('p004', '修改财务', 'p001', '/finance/update');
    INSERT INTO `sys_permission` VALUES ('p005', '删除财务', 'p001', '/finance/delete');
    INSERT INTO `sys_permission` VALUES ('p006', '开发管理', '0', '/develop/');
    INSERT INTO `sys_permission` VALUES ('p007', '查询开发', 'p006', '/develop/list');
    INSERT INTO `sys_permission` VALUES ('p008', '添加开发', 'p006', '/develop/add');
    INSERT INTO `sys_permission` VALUES ('p009', '修改开发', 'p006', '/develop/update');
    INSERT INTO `sys_permission` VALUES ('p010', '删除开发', 'p006', '/develop/delete');
    INSERT INTO `sys_permission` VALUES ('p011', '销售管理', '0', '/sale/');
    INSERT INTO `sys_permission` VALUES ('p012', '查询销售', 'p011', '/sale/list');
    INSERT INTO `sys_permission` VALUES ('p013', '添加销售', 'p011', '/sale/add');
    INSERT INTO `sys_permission` VALUES ('p014', '修改销售', 'p011', '/sale/update');
    
    
    CREATE TABLE `sys_user_role`  (
      `user_id` int,
      `role_id` varchar(32),
      CONSTRAINT `sys_ur_role` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`role_id`),
      CONSTRAINT `sys_ur_user` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`user_id`)
    ) ;
    
    INSERT INTO `sys_user_role` VALUES (1, 'r001');
    INSERT INTO `sys_user_role` VALUES (2, 'r002');
    INSERT INTO `sys_user_role` VALUES (2, 'r003');
    INSERT INTO `sys_user_role` VALUES (2, 'r004');
    INSERT INTO `sys_user_role` VALUES (3, 'r003');
    
    
    
    CREATE TABLE `sys_role_permission`  (
      `role_id` varchar(32),
      `perm_id` varchar(32),
      CONSTRAINT `sys_rp_permission` FOREIGN KEY (`perm_id`) REFERENCES `sys_permission` (`perm_id`),
      CONSTRAINT `sys_rp_role` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`role_id`)
    ) ;
    
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p001');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p002');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p003');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p004');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p005');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p006');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p007');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p008');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p009');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p010');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p011');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p012');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p013');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r001', 'p014');
    
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p001');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p002');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p003');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p004');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r002', 'p005');
    
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p006');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p007');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p008');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p009');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r003', 'p010');
    
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r004', 'p011');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r004', 'p012');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r004', 'p013');
    INSERT INTO `sys_role_permission`(`role_id`,`perm_id`) VALUES ('r004', 'p014');

## 2、需求 ##

*  查询指定若干用户，同时查询
    
     *  每个用户对应的角色，同时查询
        
         *  查询每一个角色对应的一级权限，同时查询
            
             *  每一个一级权限对应的惹急权限

## 3、实现 ##

UserMapper

@Mapper
    public interface UserMapper {
    
        /**
         * 查询所有用户
         * @return
         */
        public List<User> selectById(@Param("ulist") List<Integer> ulist);
    }

UserMapper.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.czxy.mapper.UserMapper">
    
        <resultMap id="UserResultMap" type="user">
            <result property="userId" column="user_id"></result>
    
            <association property="roleList" column="user_id" select="com.czxy.mapper.RoleMapper.selectByUserId"></association>
        </resultMap>
    
    
        <select id="selectById" resultMap="UserResultMap">
            select * from sys_user where user_id in
            <foreach collection="ulist" index="index" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </select>
    
    
    </mapper>

RoleMapper.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.czxy.mapper.RoleMapper">
    
        <resultMap id="RoleResultMap" type="role">
            <result property="roleId" column="role_id"></result>
    
            <collection property="permissionList" column="role_id" select="com.czxy.mapper.PermissionMapper.selectByRoleId"></collection>
        </resultMap>
    
        <select id="selectByUserId" resultMap="RoleResultMap">
            select r.* from sys_role r inner join sys_user_role ur on r.role_id = ur.role_id where user_id = #{userId}
        </select>
    
    </mapper>

PermissionMapper.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.czxy.mapper.PermissionMapper">
    
        <resultMap id="PermissionResultMap" type="permission">
            <result property="permId" column="perm_id"></result>
    
            <association property="permissionList" column="perm_id" select="com.czxy.mapper.PermissionMapper.selectParentId"></association>
        </resultMap>
    
        <select id="selectByRoleId" resultMap="PermissionResultMap">
            select p.* from sys_permission p inner join sys_role_permission rp on rp.perm_id = p.perm_id where rp.role_id = #{roleId} and parent_id = '0'
        </select>
    
        <select id="selectParentId" resultType="permission">
            select * from sys_permission where parent_id = #{parentId}
        </select>
    
    </mapper>