整合mybatis plus，实现数据库动态认证
- 简介
用户表 ( sys_user )：保存用户信息
角色表 ( sys_role )：保存角色信息
权限表 ( sys_permission )：保存系统资源信息。如：菜单、按钮 和对应 URL它们的关系 ：用户表与角色表是 多对多关系 ，角色表与资源表是多对多关系。
用户角色关系表（sys_user_role）：用于维护用户和角色的关系
角色资源关系表（sys_role_permission）：用于维护角色与资源的关系
-
导入数据库
-
步骤
# core模块导入依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
# web模块导入依赖
<!--mybatis-plus启动器-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<!--druid连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
</dependency>
# web模块配置yml
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://127.0.0.1:3306/demo?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8
#mysql8版本以上驱动包指定新的驱动类
driver-class-name: com.mysql.cj.jdbc.Driver
# 数据源其他配置, 在 DruidConfig配置类中手动绑定
initialSize: 8
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
mybatis-plus:
# 指定实体类所有包
type-aliases-package: com.ychen.security.model
# 日志级别，会打印sql语句
logging:
level:
com.ychen.security.mapper: debug
# 数据库连接池配置类
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource dataSource() {
return new DruidDataSource();
}
}
# mybatis plus配置类
@EnableTransactionManagement // 开启事务管理
@MapperScan("com.ychen.security.mapper") // 扫描Mapper接口
@Configuration
public class MybatisPlusConfig {
/**
* 分页插件
* @return
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
}
# 测试是否配置成功，启动未报错
- 编写实体类
@Data
public class SysUser implements UserDetails {
@TableId(type = IdType.AUTO) // 表示主键自增长
private Long id;
private String username;
/**
* 密码需要通过加密后存储
*/
private String password;
/**
* 帐户是否有效：1 未过期，0已过期
* 1 true
* 0 false
*/
private boolean isAccountNonExpired = true;
private boolean isAccountNonLocked = true;
private boolean isCredentialsNonExpired = true;
private boolean isEnabled = true;
/**
* 它不是sys_user表中的属性，所以要进行标识，不然mybatis-plus会报错
*/
@TableField(exist = false)
private Collection<? extends GrantedAuthority> authorities;
private String nickName;
private String mobile;
private String email;
private String createDate;
private String updateDate;
/**
* 拥有角色集合
*/
@TableField(exist = false)
private List<com.ychen.security.model.SysRole> roleList = Lists.newArrayList();
/**
* 获取所有角色id
*/
@TableField(exist = false)
private List<Long> roleIds = Lists.newArrayList();
public List<Long> getRoleIds() {
if(CollectionUtils.isNotEmpty(roleList)) {
roleIds = Lists.newArrayList();
for(com.ychen.security.model.SysRole role : roleList) {
roleIds.add(role.getId());
}
}
return roleIds;
}
/**
* 封装当前用户拥有的权限资源对象
*/
@TableField(exist = false)
private List<com.ychen.security.model.SysPermission> permissions = Lists.newArrayList();
}
@Data
public class SysRole implements Serializable {
@TableId(type = IdType.AUTO)
private Long id;
/**
* 角色名称
*/
private String name;
/**
* 角色描述
*/
private String remark;
private Date createDate;
private Date updateDate;
/**
* 存储当前角色的权限资源对象集合
* 修改角色时用到
*/
@TableField(exist = false)
private List<com.ychen.security.model.SysPermission> perList = Lists.newArrayList();
/**
* 存储当前角色的权限资源ID集合
* 修改角色时用到
*/
@TableField(exist = false)
private List<Long> perIds = Lists.newArrayList();
public List<Long> getPerIds() {
if(CollectionUtils.isNotEmpty(perList)) {
perIds = Lists.newArrayList();
for(com.ychen.security.model.SysPermission per : perList) {
perIds.add(per.getId());
}
}
return perIds;
}
}
@Data
public class SysPermission implements Serializable {
@TableId(type = IdType.AUTO)
private Long id;
/**
* 父资源id,给它初始值 0
* 新增和修改页面上默认的父资源id
*/
private Long parentId = 0L;
/**
* 用于新增和修改页面上默认的根菜单名称
*/
@TableField(exist = false)
private String parentName = "根菜单";
private String name;
private String code;
private String url;
/**
* 菜单：1，按钮：2
*/
private Integer type;
private String icon;
private String remark;
private Date createDate;
private Date updateDate;
/**
* 所有子权限对象集合
* 左侧菜单渲染时要用
*/
@TableField(exist = false)
private List<SysPermission> children;
/**
* 所有子权限 URL 集合
* 左侧菜单渲染时要用
*/
@TableField(exist = false)
private List<String> childrenUrl;
}
- 测试mybatis plus是否可以使用
# mapper
public interface SysUserMapper extends BaseMapper<SysUser> {
}
# service
public interface SysUserService extends IService<SysUser> {
}
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements SysUserService {
}
# controller测试
@RestController
public class TestController2 {
@Autowired
SysUserService sysUserService;
@GetMapping("/getTest")
public String getTest(){
List<SysUser> list = sysUserService.list();
System.out.println(list);
return "success";
}
}
- 编写用户管理
# 业务层接口
public interface SysUserService extends IService<SysUser> {
/**
* 通过用户名查询用户信息
* @param username 用户名
* @return
*/
SysUser findByUsername(String username) ;
}
# 业务层实现类
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements SysUserService {
@Override
public SysUser findByUsername(String username) {
if(StringUtils.isEmpty(username)) {
return null;
}
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("username", username);
// baseMapper 对应的是就是 SysUserMapper
return baseMapper.selectOne(queryWrapper);
}
}
# 控制层
@RestController
@RequestMapping("/test")
public class TestController2 {
@Autowired
SysUserService sysUserService;
// 查询单个
@GetMapping("/test2")
public String test2(){
SysUser user = sysUserService.findByUsername("admin");
System.out.println("user: " + user);
return "success";
}
}
# 控制台
11:24:13.599 DEBUG 20080 --- [p-nio-80-exec-1] c.y.s.mapper.SysUserMapper.selectOne : ==> Preparing: SELECT id,update_date,nick_name,mobile,is_account_non_locked,password,is_account_non_expired,is_credentials_non_expired,is_enabled,email,username,create_date FROM sys_user WHERE (username = ?)
11:24:13.618 DEBUG 20080 --- [p-nio-80-exec-1] c.y.s.mapper.SysUserMapper.selectOne : ==> Parameters: admin(String)
11:24:13.651 DEBUG 20080 --- [p-nio-80-exec-1] c.y.s.mapper.SysUserMapper.selectOne : <== Total: 1
user: SysUser(id=9, username=admin, password=$2a$10$rDkPvvAFV8kqwvKJzwlRv.i.q.wz1w1pz0SFsHn/55jNeZFQv/eCm, isAccountNonExpired=true, isAccountNonLocked=true, isCredentialsNonExpired=true, isEnabled=true, authorities=null, nickName=梦学谷, mobile=16888888888, email=mengxu
- 编写角色管理
# mapper
public interface SysRoleMapper extends BaseMapper<SysRole> {
}
# 业务层接口
public interface SysRoleService extends IService<SysRole> {
}
# 业务层实现
@Service
public class SysRoleServiceImpl extends ServiceImpl<SysRoleMapper, SysRole> implements SysRoleService {
}
# 接口
@Autowired
SysRoleService sysRoleService;
/**
* 查询角色表
*/
@GetMapping("/test3")
public String test3(){
SysRole role = sysRoleService.getById(9);
System.out.println("role:" + role);
return "success";
}
# 控制台
11:52:35.391 DEBUG 11852 --- [p-nio-80-exec-3] c.y.s.mapper.SysRoleMapper.selectById : ==> Preparing: SELECT id,update_date,name,remark,create_date FROM sys_role WHERE id=?
11:52:35.392 DEBUG 11852 --- [p-nio-80-exec-3] c.y.s.mapper.SysRoleMapper.selectById : ==> Parameters: 9(Integer)
11:52:35.414 DEBUG 11852 --- [p-nio-80-exec-3] c.y.s.mapper.SysRoleMapper.selectById : <== Total: 1
role:SysRole(id=9, name=超级管理员, remark=拥有所有的权限, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Tue Aug 08 11:11:11 GMT+08:00 2023, perList=[], perIds=[])
- 编写权限管理
# mapper
public interface SysPermissionMapper extends BaseMapper<SysPermission> {
}
# 业务层接口
public interface SysPermissionService extends IService<SysPermission> {
}
# 业务层实现
@Service
public class SysPermissionServiceImpl extends ServiceImpl<SysPermissionMapper, SysPermission> implements SysPermissionService {
}
# 控制层接口
@Autowired
SysPermissionService sysPermissionService;
@GetMapping("/test4")
public String test4(){
SysPermission permissions = sysPermissionService.getById(29);
System.out.println("permissions:" + permissions);
return "success";
}
# 控制台
11:50:03.198 DEBUG 11852 --- [p-nio-80-exec-1] c.y.s.m.SysPermissionMapper.selectById : ==> Preparing: SELECT id,update_date,code,icon,remark,type,parent_id,url,name,create_date FROM sys_permission WHERE id=?
11:50:03.210 DEBUG 11852 --- [p-nio-80-exec-1] c.y.s.m.SysPermissionMapper.selectById : ==> Parameters: 29(Integer)
11:50:03.266 DEBUG 11852 --- [p-nio-80-exec-1] c.y.s.m.SysPermissionMapper.selectById : <== Total: 1
permissions:SysPermission(id=29, parentId=28, parentName=根菜单, name=列表, code=sys:permission:list, url=null, type=2, icon=null, remark=权限列表, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Tue Aug 08 11:11:11 GMT+08:00 2023,
- 根据用户id查询该用户所拥有的所有权限
# mapper
public interface SysPermissionMapper extends BaseMapper<SysPermission> {
// 根据用户id查询用户所拥有的权限
List<SysPermission> selectPermissionByUserId(@Param("userId") Long userId);
}
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ychen.security.mapper.SysPermissionMapper">
<select id="selectPermissionByUserId" resultType="SysPermission">
SELECT DISTINCT
p.id,
p.parent_id,
p. NAME,
p. CODE,
p.url,
p.type,
p.icon,
p.remark,
p.create_date,
p.update_date
FROM
sys_user AS u
LEFT JOIN sys_user_role AS ur ON u.id = ur.user_id
LEFT JOIN sys_role AS r ON ur.role_id = r.id
LEFT JOIN sys_role_permission AS rp ON rp.role_id = r.id
LEFT JOIN sys_permission AS p ON rp.permission_id = p.id
WHERE
u.id = #{userId}
</select>
</mapper>
# 业务层接口
public interface SysPermissionService extends IService<SysPermission> {
/**
* 通过用户id查询所拥有权限
* @param userId
* @return
*/
List<SysPermission> findByUserId(Long userId);
}
# 业务层实现
@Service
public class SysPermissionServiceImpl extends ServiceImpl<SysPermissionMapper, SysPermission> implements SysPermissionService {
@Override
public List<SysPermission> findByUserId(Long userId) {
if(userId == null) {
return null;
}
List<SysPermission> permissionList = baseMapper.selectPermissionByUserId(userId);
// 如果没有权限，则将集合中的数据null移除
permissionList.remove(null);
return permissionList;
}
}
# 控制层接口
@RestController
@RequestMapping("/test")
public class TestController2 {
@Autowired
SysPermissionService sysPermissionService;
@GetMapping("/test5")
public String test5(){
List<SysPermission> permissions = sysPermissionService.findByUserId(9L);
System.out.println(permissions);
System.out.println("permissions:" + permissions.size());
return "success";
}
}
# 控制台
13:23:56.820 DEBUG 19664 --- [p-nio-80-exec-1] c.y.s.m.S.selectPermissionByUserId : ==> Preparing: SELECT DISTINCT p.id, p.parent_id, p. NAME, p. CODE, p.url, p.type, p.icon, p.remark, p.create_date, p.update_date FROM sys_user AS u LEFT JOIN sys_user_role AS ur ON u.id = ur.user_id LEFT JOIN sys_role AS r ON ur.role_id = r.id LEFT JOIN sys_role_permission AS rp ON rp.role_id = r.id LEFT JOIN sys_permission AS p ON rp.permission_id = p.id WHERE u.id = ?
13:23:56.831 DEBUG 19664 --- [p-nio-80-exec-1] c.y.s.m.S.selectPermissionByUserId : ==> Parameters: 9(Long)
13:23:56.867 DEBUG 19664 --- [p-nio-80-exec-1] c.y.s.m.S.selectPermissionByUserId : <== Total: 17
[SysPermission(id=11, parentId=0, parentName=根菜单, name=首页, code=sys:index, url=/, type=1, icon=fa fa-dashboard, remark=, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=17, parentId=0, parentName=根菜单, name=系统管理, code=sys:manage, url=null, type=1, icon=fa fa-cogs, remark=null, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=18, parentId=17, parentName=根菜单, name=用户管理, code=sys:user, url=/user, type=1, icon=fa fa-users, remark=null, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=19, parentId=18, parentName=根菜单, name=列表, code=sys:user:list, url=, type=2, icon=, remark=员工列表, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Tue Aug 08 11:11:11 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=20, parentId=18, parentName=根菜单, name=新增, code=sys:user:add, url=, type=2, icon=, remark=新增用户, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=21, parentId=18, parentName=根菜单, name=修改, code=sys:user:edit, url=, type=2, icon=, remark=修改用户, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=22, parentId=18, parentName=根菜单, name=删除, code=sys:user:delete, url=, type=2, icon=, remark=删除用户, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=23, parentId=17, parentName=根菜单, name=角色管理, code=sys:role, url=/role, type=1, icon=fa fa-user-secret, remark=null, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=24, parentId=23, parentName=根菜单, name=列表, code=sys:role:list, url=null, type=2, icon=null, remark=角色列表, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Tue Aug 08 11:11:11 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=25, parentId=23, parentName=根菜单, name=新增, code=sys:role:add, url=, type=2, icon=, remark=新增角色, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=26, parentId=23, parentName=根菜单, name=修改, code=sys:role:edit, url=, type=2, icon=, remark=修改角色, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=27, parentId=23, parentName=根菜单, name=删除, code=sys:role:delete, url=, type=2, icon=, remark=删除角色, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=28, parentId=17, parentName=根菜单, name=权限管理, code=sys:permission, url=/permission, type=1, icon=fa fa-cog, remark=null, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=29, parentId=28, parentName=根菜单, name=列表, code=sys:permission:list, url=null, type=2, icon=null, remark=权限列表, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Tue Aug 08 11:11:11 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=30, parentId=28, parentName=根菜单, name=新增, code=sys:permission:add, url=, type=2, icon=null, remark=新增权限, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=31, parentId=28, parentName=根菜单, name=修改, code=sys:permission:edit, url=, type=2, icon=null, remark=修改权限, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null), SysPermission(id=32, parentId=28, parentName=根菜单, name=删除, code=sys:permission:delete, url=, type=2, icon=, remark=删除权限, createDate=Tue Aug 08 11:11:11 GMT+08:00 2023, updateDate=Wed Aug 09 15:26:28 GMT+08:00 2023, children=null, childrenUrl=null)]
permissions:17
- 用户名密码动态认证
@Component("customUserDetailsService")
public class CustomUserDetailsService implements UserDetailsService {
// 日志
Logger logger = LoggerFactory.getLogger(getClass());
// 加密方式
@Autowired
PasswordEncoder passwordEncoder;
// 用户业务对象
@Autowired
SysUserService sysUserService;
// 权限对象
@Autowired
SysPermissionService sysPermissionService;
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
logger.info("请求认证的用户名: " + username);
// 1. 通过请求的用户名去数据库中查询用户信息
SysUser sysUser = sysUserService.findByUsername(username);
// 如果没有查询到，则抛出异常
if(sysUser == null) {
throw new UsernameNotFoundException("用户名或密码错误");
}
// 2. 查询该用户有哪一些权限
List<SysPermission> permissions = sysPermissionService.findByUserId(sysUser.getId());
// 如果该用户没有权限，则直接返回该对象
if(CollectionUtils.isEmpty(permissions)) {
return sysUser;
}
/**
* 如果有权限，则封装到该对象中
* 在左侧菜单 动态渲染会使用，目前先把它都传入
*/
sysUser.setPermissions(permissions);
// 3. 封装权限信息
List<GrantedAuthority> authorities = Lists.newArrayList();
for(SysPermission sp: permissions) {
// 获取到权限标识
String code = sp.getCode();
authorities.add(new SimpleGrantedAuthority(code));
}
// 将封装的权限信息添加到该对象中
sysUser.setAuthorities(authorities);
// 4. 返回该对象，springsecurity自动进行身份认证
return sysUser;
}
}
- 手机号+短信动态认证
# 业务层接口
public interface SysUserService extends IService<SysUser> {
/**
* 通过手机号查询用户信息
* @param mobile 手机号
* @return
*/
SysUser findByMobile(String mobile);
}
# 业务层实现
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements SysUserService {
@Override
public SysUser findByMobile(String mobile) {
if(StringUtils.isEmpty(mobile)) {
return null;
}
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("mobile", mobile);
// baseMapper 对应的是就是 SysUserMapper
return baseMapper.selectOne(queryWrapper);
}
}
# 实现UserDetailsService接口
@Component("mobileUserDetailsService")
public class MobileUserDetailsService implements UserDetailsService {
// 日志
Logger logger = LoggerFactory.getLogger(getClass());
// 用户业务对象
@Autowired
SysUserService sysUserService;
// 权限对象
@Autowired
SysPermissionService sysPermissionService;
@Override
public UserDetails loadUserByUsername(String mobile) throws UsernameNotFoundException {
logger.info("请求的手机号是：" + mobile);
// 1. 通过手机号查询用户信息
SysUser sysUser = sysUserService.findByMobile(mobile);
// 判断查询到的用户信息是否未空，为空则抛出异常
if(sysUser == null) {
throw new UsernameNotFoundException("该手机号未注册");
}
// 2. 查询该用户所拥有的权限
List<SysPermission> permissions = sysPermissionService.findByUserId(sysUser.getId());
// 判断查询到的权限是否为空
if(CollectionUtils.isEmpty(permissions)) {
return sysUser;
}
/**
* 将查询到的权限封装到该对象中
* 在左侧菜单 动态渲染会使用，目前先把它都传入
*/
sysUser.setPermissions(permissions);
// 3. 封装权限信息
List<GrantedAuthority> authorities = Lists.newArrayList();
for(SysPermission sp: permissions) {
// 获取到权限标识
String code = sp.getCode();
authorities.add(new SimpleGrantedAuthority(code));
}
// 将权限设置给该用户
sysUser.setAuthorities(authorities);
// 4. 返回用户对象，springsecurity自动进行身份认证
return sysUser;
}
}
