springboot之shiro学习六

Apache Shiro整合SpringBoot2.x综合实战和技术栈
技术选型：前后端分离的权限检验 + SpringBoot2.x + Mysql + Mybatis + Shiro + Redis + IDEA + JDK8
设计案例实战数据库 用户-角色-权限 及关联表
用户
角色
权限
idea快速生成get与set的方法
右键Generate找到get与set就行；
部分依赖信息

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--阿里巴巴druid数据源-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
<!--spring整合shiro-->
<dependency>
<groupId>org.apache.shiro</groupId>
<artifactId>shiro-spring</artifactId>
<version>1.4.0</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
<repositories>
<repository>
<id>spring-snapshots</id>
<name>Spring Snapshots</name>
<url>https://repo.spring.io/snapshot</url>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
<repository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https://repo.spring.io/milestone</url>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>spring-snapshots</id>
<name>Spring Snapshots</name>
<url>https://repo.spring.io/snapshot</url>
<snapshots>
<enabled>true</enabled>
</snapshots>
</pluginRepository>
<pluginRepository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https://repo.spring.io/milestone</url>
</pluginRepository>
</pluginRepositories>

数据库配置

数据库配置
#==============================数据库相关配置========================================
spring.datasource.driver-class-name =com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://120.76.62.13:3606/xdclass_shiro?
useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username =test
spring.datasource.password =root
#使用阿里巴巴druid数据源，默认使用自带的
#spring.datasource.type =com.alibaba.druid.pool.DruidDataSource
#开启控制台打印sql
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# mybatis 下划线转驼峰配置,两者都可以
#mybatis.configuration.mapUnderscoreToCamelCase=true
mybatis.configuration.map-underscore-to-camel-case=true

多表查询权限信息
第一步 查询用户对应的角色映射关系

select * from user u
left join user_role ur on u.id=ur.user_id
where u.id=3

第二步 查询用户对应的角色信息

select * from user u
left join user_role ur on u.id=ur.user_id
left join role r on ur.role_id = r.id
where u.id=3

第三步 查询角色和权限的关系

select * from user u
left join user_role ur on u.id=ur.user_id
left join role r on ur.role_id = r.id
left join role_permission rp on r.id=rp.role_id
where u.id=1

第四步 查询角色对应的权限信息（某个用户具备的角色和权限集合）

select * from user u
left join user_role ur on u.id=ur.user_id
left join role r on ur.role_id = r.id
left join role_permission rp on r.id=rp.role_id
left join permission p on rp.permission_id=p.id
where u.id=1

通过Role_permission查询permission

select p.id as id,p.name as name,p.url as url from role_permission rp 
left join permission p on rp.permission_id=p.id
where rp.role_id=2

结果为

Dao层配置如下

package com.example.demo.dao;

import com.example.demo.model.Role;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import org.springframework.beans.factory.annotation.Value;

import java.util.List;

public interface RoleMapper {


    //查询两个表第一个是**ROLE**表第二个是**user_role** 查询到的结果返回过来通过ID执行下面引入的宁外的Sql语句
    @Select("select r.id as id,r.name as name,r.description as description from  user_role ur left join role r on ur.role_id = r.id where ur.user_id=#{userId}")
//    @Results{
//        value= {
//        @Result(id = true, property = "id", column = "id"),
//        @Result(property = "name", column = "name"),
//        @Result(property = "name", column = "name"),
//        @Result(property = "permissionList", column = "id",
//                many = @Many(select = "com.example.demo.dao.PermissionMapper.findPermissionListByRoleId", fetchType = FetchType.DEFAULT)
//        )
//        };
//    }
    @Results(
            value = {
                    @Result(id=true, property = "id",column = "id"),
                    @Result(property = "name",column = "name"),
                    @Result(property = "description",column = "description"),
                    @Result(property = "permissionList",column = "id",
                            many = @Many(select = "com.example.demo.dao.PermissionMapper.findPermissionListByRoleId", fetchType = FetchType.DEFAULT)
                    )
            }
    )
    List<Role> findRoleListByUserId(@Param("userId") int userId);
}

查询权限的Dao层如下
通过上面引入执行

package com.example.demo.dao;

import com.example.demo.model.Permission;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface PermissionMapper {
    @Select("select p.id as id,p.name as name,p.url as url from role_permission rp left join permission p on rp.permission_id=p.id where rp.role_id=#{roleId}")
    List<Permission> findPermissionListByRoleId(@Param("roleId")int roleId);
}

最后结果如下所示

结果所示：

{
    "id": 3,
    "username": "jack",
    "createTime": null,
    "salt": null,
    "password": "123",
    "roleList": [
        {
            "id": 1,
            "name": "admin",
            "description": "普通管理员",
            "permissionList": []
        },
        {
            "id": 2,
            "name": "root",
            "description": "超级管理员",
            "permissionList": [
                {
                    "id": 1,
                    "name": "video_update",
                    "url": "/api/video/update"
                },
                {
                    "id": 2,
                    "name": "video_delete",
                    "url": "/api/video/delete"
                },
                {
                    "id": 3,
                    "name": "video_add",
                    "url": "/api/video/add"
                },
                {
                    "id": 4,
                    "name": "order_list",
                    "url": "/api/order/list"
                }
            ]
        },
        {
            "id": 3,
            "name": "editor",
            "description": "审核人员",
            "permissionList": [
                {
                    "id": 1,
                    "name": "video_update",
                    "url": "/api/video/update"
                },
                {
                    "id": 2,
                    "name": "video_delete",
                    "url": "/api/video/delete"
                },
                {
                    "id": 3,
                    "name": "video_add",
                    "url": "/api/video/add"
                }
            ]
        }
    ]
}

我对两个Sql的理解
第一个sql讲解
查询两个表当userID也就是上面查询的jack的id为3的时候，查询到user_role 中的roleID的等于ROLE的主键ID的时候返回ROLE的ID，name,description,因为是采用的是主键，所以上面的 ur.role_id as id，也可以改变成为r.id as id

select ur.role_id as id,
r.name as name,
r.description as description 
from  user_role ur 
left join role r 
on ur.role_id = r.id 
where ur.user_id=3

第二个SQL讲解
通过上面返回的ROLE表中的id也就是roleID，对应的role_permission中的roleID是一样的,对应相同的roleID来查询他的PermissionID进而来查询他的对应的权限

select p.id as id,
p.name as name,
p.url as url 
from role_permission rp
 left join permission p 
 on rp.permission_id=p.id 
 where rp.role_id=3

根据上面ROLE的id查询如下