权限管理数据表分析以及resultMap的写法

权限管理

1.准备数据库

1.1 employee表：用户信息

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `headImage` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=274 DEFAULT CHARSET=utf8;

1.2 role表：角色信息

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

1.3 employee_role表：联系用户和角色

DROP TABLE IF EXISTS `employee_role`;
CREATE TABLE `employee_role` (
  `employee_id` bigint(20) NOT NULL,
  `role_id` bigint(20) NOT NULL,
  PRIMARY KEY (`employee_id`,`role_id`),
  KEY `FK87184F674D26E00F` (`role_id`),
  KEY `FK87184F6710B1828F` (`employee_id`),
  CONSTRAINT `FK87184F6710B1828F` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`),
  CONSTRAINT `FK87184F674D26E00F` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.4 menu表：菜单管理

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK24897F76799044` (`parent_id`),
  CONSTRAINT `FK24897F76799044` FOREIGN KEY (`parent_id`) REFERENCES `menu` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

1.5 role_menu表：联系角色和菜单

DROP TABLE IF EXISTS `role_menu`;
CREATE TABLE `role_menu` (
  `role_id` bigint(20) NOT NULL,
  `menu_id` bigint(20) NOT NULL,
  PRIMARY KEY (`role_id`,`menu_id`),
  KEY `FK1404278833B84B6F` (`menu_id`),
  KEY `FK140427884D26E00F` (`role_id`),
  CONSTRAINT `FK1404278833B84B6F` FOREIGN KEY (`menu_id`) REFERENCES `menu` (`id`),
  CONSTRAINT `FK140427884D26E00F` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.6 permission表：权限表

DROP TABLE IF EXISTS `permission`;
CREATE TABLE `permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `menu_id` bigint(20) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `menu_id` (`menu_id`),
  CONSTRAINT `permission_ibfk_1` FOREIGN KEY (`menu_id`) REFERENCES `menu` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

1.7 role_permission表：联系角色和权限

DROP TABLE IF EXISTS `role_permission`;
CREATE TABLE `role_permission` (
  `role_id` bigint(20) NOT NULL,
  `permission_id` bigint(20) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `FKAEE599B74D26E00F` (`role_id`),
  KEY `FKAEE599B7C854068F` (`permission_id`),
  CONSTRAINT `FKAEE599B74D26E00F` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
  CONSTRAINT `FKAEE599B7C854068F` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.8 总结表间关系

2. 项目搭建

2.1 基础配置

application.yaml

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/springboot
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  mapper-locations: classpath:com/whirl/mapper/*.xml
  type-aliases-package: com.whirl.entity
logging:
  level:
    com:
      whirl: trace
  root: error

对mybatis进行xml文件路径和实体别名的配置

在resourses下面创建多级目录，com.whirl.mapper存放xml文件

在spring boot启动类上加@MapperScan(“com.whirl.mapper”)扫描mapper接口

2.2 存放菜单信息

2.2.1 sql

<resultMap id="resultMenu" type="Menu">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="url" property="url"/>
    <association property="parent" javaType="Menu">
        <id column="pid" property="id"/>
        <result column="pname" property="name"/>
    </association>
</resultMap>

<select id="selectMenuByEmployeeId" resultMap="resultMenu" >
    SELECT DISTINCT m.*,par.id pid,par.name pname FROM employee e
    JOIN employee_role er ON e.id=er.employee_id
    JOIN role r ON er.role_id=r.id
    JOIN role_permission rp ON r.id=rp.role_id
    JOIN permission p ON rp.permission_id=p.id
    JOIN menu m ON p.menu_id=m.id
    JOIN menu par ON m.parent_id=par.id
    WHERE e.id=#{empId}
</select>

2.2.2 association

这个是一对一或者多对一

这个例子是一人一张身份证，是一对一的情况

public class User {
    private Integer userId;
    private String userName;
    private Integer age;
    private Card card;//一个人一张身份证,1对1
}

public class Card {
    private Integer cardId;
    private String cardNum;//身份证号
    private String address;//地址
}

嵌套resultMap

通过javaType来指定是哪个类

<resultMap type="User" id="userMap">
   <result property="userName" column="user_name"/>
   <result property="age" column="age"/>
   <association property="card" column="card_id" javaType="Card">
      <id property="cardId" column="card_id"/>
      <result property="cardNum" column="card_num"/>
      <result property="address"  column="address"/>
    </association>
</resultMap> 

2.2.3 collection

一对多的关系

一个人多个手机，是一个集合

public class User{
  private Integer userId;
    private String userName;
    private Integer age;
    private List<MobilePhone> mobilePhone;//土豪,多个手机,1对多
}
public class MobilePhone {
    private Integer mobilePhoneId;
    private String brand;//品牌
    private double price;//价格
    private User user;//主人
}

嵌套resultMap

collection 装的元素类型是啥ofType的值就是啥

<resultMap type="User" id="userMap">
     <result property="userName" column="user_name"/>
     <result property="age" column="age"/>
     <collection property="mobilePhone" column="user_id" ofType="MobilePhone">
       <id column="mobile_phone_id" property="mobilePhoneId" />
       <result column="brand" property="brand" />
       <result column="price" property="price" />
     </collection>
</resultMap>

3. 拦截器

3.1 登陆拦截器

@Component

@Component
public class LoginInterceptor implements HandlerInterceptor {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        HttpSession session = request.getSession();
        //在session中获取登录用户
        Object loginUser = session.getAttribute("USER_IN_SESSION");
        if(loginUser == null){
            response.sendRedirect("/login");
            return false;//不放行
        }
        return true;//放行
    }
}

3.2 权限资源拦截器

@Component

@Component
public class PermissionInterceptor implements HandlerInterceptor {
    @Autowired
    private PermissionMapper permissionMapper;

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        //1.获取当前请求的uri
        String uri = request.getRequestURI();
        //2.获取所有的权限(资源)
        List<String> urls = permissionMapper.selectAllPermissionUrl();
        //3.判断当前权限是不是公共权限，也就是判断是不是在权限集合中，不在就放行
        if(urls.contains(uri)){
            //3.1获取当前登录人
            Employee employee = (Employee) request.getSession().getAttribute("USER_IN_SESSION");
            //3.2获取当前用户拥有的权限
            List<String> permissions = permissionMapper.selectAllPermissionUrlByEmployeeId(employee.getId());
            //3.3判断当前用户的权限集合中是否有uri
            if(permissions.contains(uri)){
                return true;
            }else{
                //3.4跳转到没有权限的页面
                response.sendRedirect("/nopermission");
                return false;
            }
        }
        return true;
    }
}

3.2.1 相关sql

<!-- 查询所有的权限==url-->
<select id="selectAllPermissionUrl" resultType="string">
  SELECT url FROM permission WHERE url is not null
</select>
<!--查询当前员工拥有的权限url-->
<select id="selectAllPermissionUrlByEmployeeId" resultType="string">
  SELECT p.url FROM employee e
  JOIN employee_role er ON e.id=er.employee_id
  JOIN role r ON er.role_id=r.id
  JOIN role_permission rp ON r.id=rp.role_id
  JOIN permission p ON rp.permission_id=p.id
  where e.id=#{empId}
</select>

3.3 配置类

@Configuration

@Configuration//配置类  类似于以前的xml文件
public class MyWebMvcConfigurer implements WebMvcConfigurer {
    @Autowired
    private LoginInterceptor loginInterceptor;
    @Autowired
    private PermissionInterceptor permissionInterceptor;

    /**
     * 注册拦截器
     * @param registry
     */
    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        //1.添加登录拦截器
        registry.addInterceptor(loginInterceptor)
          			.addPathPatterns("/**")
                .excludePathPatterns("/login")
          			.excludePathPatterns("/assets/**");

        //2.添加权限拦截器
        registry.addInterceptor(permissionInterceptor)
         				.addPathPatterns("/**")
               	.excludePathPatterns("/login")
          			.excludePathPatterns("/assets/**");
    }

    /**
     * 避免单独写个controller去转发请求
     */
    @Override
    public void addViewControllers(ViewControllerRegistry registry) {
        // 相当于写了个Controller，有个RequestMapping
        //如果你请求的资源是/nopermission，就跳转到nopermission.html的界面
        registry.addViewController("/nopermission").setViewName("nopermission");
    }