准备工作
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sg_security` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `sg_security`;
/*Table structure for table `sys_menu` */
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`menu_name` varchar(64) NOT NULL DEFAULT 'NULL' COMMENT '菜单名',
`path` varchar(200) DEFAULT NULL COMMENT '路由地址',
`component` varchar(255) DEFAULT NULL COMMENT '组件路径',
`visible` char(1) DEFAULT '0' COMMENT '菜单状态(0显示 1隐藏)',
`status` char(1) DEFAULT '0' COMMENT '菜单状态(0正常 1停用)',
`perms` varchar(100) DEFAULT NULL COMMENT '权限标识',
`icon` varchar(100) DEFAULT '#' COMMENT '菜单图标',
`create_by` bigint(20) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_by` bigint(20) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`del_flag` int(11) DEFAULT '0' COMMENT '是否删除(0未删除 1已删除)',
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='菜单表';
/*Table structure for table `sys_role` */
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`role_key` varchar(100) DEFAULT NULL COMMENT '角色权限字符串',
`status` char(1) DEFAULT '0' COMMENT '角色状态(0正常 1停用)',
`del_flag` int(1) DEFAULT '0' COMMENT 'del_flag',
`create_by` bigint(200) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_by` bigint(200) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='角色表';
/*Table structure for table `sys_role_menu` */
DROP TABLE IF EXISTS `sys_role_menu`;
CREATE TABLE `sys_role_menu` (
`role_id` bigint(200) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
`menu_id` bigint(200) NOT NULL DEFAULT '0' COMMENT '菜单id',
PRIMARY KEY (`role_id`,`menu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
/*Table structure for table `sys_user` */
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_name` varchar(64) NOT NULL DEFAULT 'NULL' COMMENT '用户名',
`nick_name` varchar(64) NOT NULL DEFAULT 'NULL' COMMENT '昵称',
`password` varchar(64) NOT NULL DEFAULT 'NULL' COMMENT '密码',
`status` char(1) DEFAULT '0' COMMENT '账号状态(0正常 1停用)',
`email` varchar(64) DEFAULT NULL COMMENT '邮箱',
`phonenumber` varchar(32) DEFAULT NULL COMMENT '手机号',
`sex` char(1) DEFAULT NULL COMMENT '用户性别(0男,1女,2未知)',
`avatar` varchar(128) DEFAULT NULL COMMENT '头像',
`user_type` char(1) NOT NULL DEFAULT '1' COMMENT '用户类型(0管理员,1普通用户)',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建人的用户id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`del_flag` int(11) DEFAULT '0' COMMENT '删除标志(0代表未删除,1代表已删除)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
/*Table structure for table `sys_user_role` */
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`user_id` bigint(200) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`role_id` bigint(200) NOT NULL DEFAULT '0' COMMENT '角色id',
PRIMARY KEY (`user_id`,`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SELECT
DISTINCT m.`perms`
FROM
sys_user_role ur
LEFT JOIN `sys_role` r ON ur.`role_id` = r.`id`
LEFT JOIN `sys_role_menu` rm ON ur.`role_id` = rm.`role_id`
LEFT JOIN `sys_menu` m ON m.`id` = rm.`menu_id`
WHERE
user_id = 2
AND r.`status` = 0
AND m.`status` = 0
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
@TableName(value="sys_menu")
@Data
@AllArgsConstructor
@NoArgsConstructor
@JsonInclude(JsonInclude.Include.NON_NULL)
public class Menu implements Serializable {
private static final long serialVersionUID = -54979041104113736L;
@TableId
private Long id;
/**
* 菜单名
*/
private String menuName;
/**
* 路由地址
*/
private String path;
/**
* 组件路径
*/
private String component;
/**
* 菜单状态(0显示 1隐藏)
*/
private String visible;
/**
* 菜单状态(0正常 1停用)
*/
private String status;
/**
* 权限标识
*/
private String perms;
/**
* 菜单图标
*/
private String icon;
private Long createBy;
private Date createTime;
private Long updateBy;
private Date updateTime;
/**
* 是否删除(0未删除 1已删除)
*/
private Integer delFlag;
/**
* 备注
*/
private String remark;
}
代码实现
我们只需要根据用户id去查询到其所对应的权限信息即可。
所以我们可以先定义个mapper,其中提供一个方法可以根据userid查询权限信息。
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sangeng.domain.Menu;
import java.util.List;
public interface MenuMapper extends BaseMapper<Menu> {
List<String> selectPermsByUserId(Long id);
}
尤其是自定义方法,所以需要创建对应的mapper文件,定义对应的sql语句
<?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.sangeng.mapper.MenuMapper">
<select id="selectPermsByUserId" resultType="java.lang.String">
SELECT
DISTINCT m.`perms`
FROM
sys_user_role ur
LEFT JOIN `sys_role` r ON ur.`role_id` = r.`id`
LEFT JOIN `sys_role_menu` rm ON ur.`role_id` = rm.`role_id`
LEFT JOIN `sys_menu` m ON m.`id` = rm.`menu_id`
WHERE
user_id = #{userid}
AND r.`status` = 0
AND m.`status` = 0
</select>
</mapper>
在application.yml中配置mapperXML文件的位置
spring:
datasource:
url: jdbc:mysql://localhost:3306/sg_security?characterEncoding=utf-8&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
redis:
host: localhost
port: 6379
mybatis-plus:
mapper-locations: classpath*:/mapper/**/*.xml
然后我们可以在UserDetailsServiceImpl中去调用该mapper的方法查询权限信息封装到LoginUser对象中即可。
@Service
public class UserDetailsServiceImpl implements UserDetailsService {
@Autowired
private UserMapper userMapper;
@Autowired
private MenuMapper menuMapper;
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getUserName,username);
User user = userMapper.selectOne(wrapper);
if(Objects.isNull(user)){
throw new RuntimeException("用户名或密码错误");
}
List<String> permissionKeyList = menuMapper.selectPermsByUserId(user.getId());
// //测试写法
// List<String> list = new ArrayList<>(Arrays.asList("test"));
return new LoginUser(user,permissionKeyList);
}
}