/*
Navicat MySQL Data Transfer
Source Server : docker_mysql
Source Server Version : 50732
Source Host : 192.168.30.132:3306
Source Database : jwtandsercurity
Target Server Type : MYSQL
Target Server Version : 50732
File Encoding : 65001
Date: 2021-01-09 14:11:09
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL COMMENT '账号',
`password` varchar(255) NOT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES ('1', 'admin', '$10$b2PI77rj1ch52aE85.Og7OW5xUfjOppJVw8WdqgfLlCTw7atXb3Lu');
INSERT INTO `sys_user` VALUES ('2', 'abel', '$10$b2PI77rj1ch52aE85.Og7OW5xUfjOppJVw8WdqgfLlCTw7atXb3Lu');
INSERT INTO `sys_user` VALUES ('4', 'oldbai', '$2a$10$/MJkaIDZ0hnZo3imTGAIWOSifiJLbzQL0zrzkJVNvzufK80j43BPK');
INSERT INTO `sys_user` VALUES ('5', 'xiaobai', '$2a$10$NhsRxTeXVFZNotZPSripqOgoOGvkgVcOqg2gwyvReO4JhFNhoMSJu');
字段分析
username:用户名
password:密码
其他字段可以自行添加
(2).角色表
/*
Navicat MySQL Data Transfer
Source Server : docker_mysql
Source Server Version : 50732
Source Host : 192.168.30.132:3306
Source Database : jwtandsercurity
Target Server Type : MYSQL
Target Server Version : 50732
File Encoding : 65001
Date: 2021-01-09 14:10:53
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL COMMENT '角色名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'ROLE_ADMIN');
INSERT INTO `sys_role` VALUES ('2', 'ROLE_USER');
字段分析
name:角色名
其他字段可以自行添加
(3).权限表:sys_permission.sql
/*
Navicat MySQL Data Transfer
Source Server : docker_mysql
Source Server Version : 50732
Source Host : 192.168.30.132:3306
Source Database : jwtandsercurity
Target Server Type : MYSQL
Target Server Version : 50732
File Encoding : 65001
Date: 2021-01-09 14:10:08
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for sys_permission
-- ----------------------------
DROP TABLE IF EXISTS `sys_permission`;
CREATE TABLE `sys_permission` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL COMMENT '权限名',
`descritpion` varchar(255) DEFAULT NULL COMMENT '描述',
`url` varchar(255) DEFAULT NULL COMMENT '前端资源路径,比如/user/**',
`pid` int(11) DEFAULT NULL COMMENT '父级权限id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of sys_permission
-- ----------------------------
INSERT INTO `sys_permission` VALUES ('1', 'ROLE_HOME', 'home', '/sysUser/', null);
INSERT INTO `sys_permission` VALUES ('2', 'ROLE_ADMIN', 'Abel', '/sysPermission/', null);
字段分析
name : 权限名称,具体是根据该注解进行的。
descritpion : 描述该权限的功能
url : 描述该权限访问的路径
pid : 父级权限的节点(一级权限、二级权限、三级权限,此处不使用)
其他字段可以自行添加
(4).用户-角色表
/*
Navicat MySQL Data Transfer
Source Server : docker_mysql
Source Server Version : 50732
Source Host : 192.168.30.132:3306
Source Database : jwtandsercurity
Target Server Type : MYSQL
Target Server Version : 50732
File Encoding : 65001
Date: 2021-01-09 14:22:54
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for sys_role_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_user`;
CREATE TABLE `sys_role_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sys_user_id` int(11) DEFAULT NULL,
`sys_role_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of sys_role_user
-- ----------------------------
INSERT INTO `sys_role_user` VALUES ('1', '1', '1');
INSERT INTO `sys_role_user` VALUES ('2', '2', '2');
INSERT INTO `sys_role_user` VALUES ('3', '4', '1');
INSERT INTO `sys_role_user` VALUES ('4', '5', '2');
字段分析
sys_user_id : 用户ID
sys_role_id : 角色ID
(5).角色-用户表:sys_permission_role.sql
/*
Navicat MySQL Data Transfer
Source Server : docker_mysql
Source Server Version : 50732
Source Host : 192.168.30.132:3306
Source Database : jwtandsercurity
Target Server Type : MYSQL
Target Server Version : 50732
File Encoding : 65001
Date: 2021-01-09 14:21:53
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for sys_permission_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_permission_role`;
CREATE TABLE `sys_permission_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_id` int(11) DEFAULT NULL,
`permission_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of sys_permission_role
-- ----------------------------
INSERT INTO `sys_permission_role` VALUES ('1', '1', '1');
INSERT INTO `sys_permission_role` VALUES ('2', '2', '1');
INSERT INTO `sys_permission_role` VALUES ('3', '1', '2');
字段分析
role_id : 角色ID
permission_id : 权限ID
(6).表分析
通过用户可以查询该用户所属角色,通过该角色可以得到所拥有的权限。
5. 统一返回结果工具包
com.oldbai.sercuityandjwt.util.resultdata
CommonResult类
package com.oldbai.sercuityandjwt.util.resultdata;/**
* 通用返回对象
* Created by macro on 2019/4/19.
*/publicclassCommonResult<T>{
privatelong code;private String message;private T data;protectedCommonResult(){
}protectedCommonResult(long code, String message, T data){
this.code = code;this.message = message;this.data = data;}/**
* 成功返回结果
*
* @param data 获取的数据
*/publicstatic<T> CommonResult<T>success(T data){
returnnewCommonResult<T>(ResultCode.SUCCESS.getCode(), ResultCode.SUCCESS.getMessage(), data