mybatisplus如何实现数据库动态认证(mybatisplus,数据库,编程语言)

• 简介

用户表(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模块配置ymlspring:datasource:username:rootpassword:123456url: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:8minIdle:5maxActive:20maxWait:60000timeBetweenEvictionRunsMillis:60000minEvictableIdleTimeMillis:300000validationQuery:SELECT1FROMDUALmybatis-plus:#指定实体类所有包type-aliases-package:com.ychen.security.model#日志级别，会打印sql语句logging:level:com.ychen.security.mapper:debug#数据库连接池配置类@ConfigurationpublicclassDruidConfig{@ConfigurationProperties(prefix="spring.datasource")@BeanpublicDataSourcedataSource(){returnnewDruidDataSource();}}#mybatisplus配置类@EnableTransactionManagement//开启事务管理@MapperScan("com.ychen.security.mapper")//扫描Mapper接口@ConfigurationpublicclassMybatisPlusConfig{/***分页插件*@return*/@BeanpublicPaginationInterceptorpaginationInterceptor(){returnnewPaginationInterceptor();}}#测试是否配置成功，启动未报错

• 编写实体类

@DatapublicclassSysUserimplementsUserDetails{@TableId(type=IdType.AUTO)//表示主键自增长privateLongid;privateStringusername;/***密码需要通过加密后存储*/privateStringpassword;/***帐户是否有效：1未过期，0已过期*1true*0false*/privatebooleanisAccountNonExpired=true;privatebooleanisAccountNonLocked=true;privatebooleanisCredentialsNonExpired=true;privatebooleanisEnabled=true;/***它不是sys_user表中的属性，所以要进行标识，不然mybatis-plus会报错*/@TableField(exist=false)privateCollection<?extendsGrantedAuthority>authorities;privateStringnickName;privateStringmobile;privateStringemail;privateStringcreateDate;privateStringupdateDate;/***拥有角色集合*/@TableField(exist=false)privateList<com.ychen.security.model.SysRole>roleList=Lists.newArrayList();/***获取所有角色id*/@TableField(exist=false)privateList<Long>roleIds=Lists.newArrayList();publicList<Long>getRoleIds(){if(CollectionUtils.isNotEmpty(roleList)){roleIds=Lists.newArrayList();for(com.ychen.security.model.SysRolerole:roleList){roleIds.add(role.getId());}}returnroleIds;}/***封装当前用户拥有的权限资源对象*/@TableField(exist=false)privateList<com.ychen.security.model.SysPermission>permissions=Lists.newArrayList();}@DatapublicclassSysRoleimplementsSerializable{@TableId(type=IdType.AUTO)privateLongid;/***角色名称*/privateStringname;/***角色描述*/privateStringremark;privateDatecreateDate;privateDateupdateDate;/***存储当前角色的权限资源对象集合*修改角色时用到*/@TableField(exist=false)privateList<com.ychen.security.model.SysPermission>perList=Lists.newArrayList();/***存储当前角色的权限资源ID集合*修改角色时用到*/@TableField(exist=false)privateList<Long>perIds=Lists.newArrayList();publicList<Long>getPerIds(){if(CollectionUtils.isNotEmpty(perList)){perIds=Lists.newArrayList();for(com.ychen.security.model.SysPermissionper:perList){perIds.add(per.getId());}}returnperIds;}}@DatapublicclassSysPermissionimplementsSerializable{@TableId(type=IdType.AUTO)privateLongid;/***父资源id,给它初始值0*新增和修改页面上默认的父资源id*/privateLongparentId=0L;/***用于新增和修改页面上默认的根菜单名称*/@TableField(exist=false)privateStringparentName="根菜单";privateStringname;privateStringcode;privateStringurl;/***菜单：1，按钮：2*/privateIntegertype;privateStringicon;privateStringremark;privateDatecreateDate;privateDateupdateDate;/***所有子权限对象集合*左侧菜单渲染时要用*/@TableField(exist=false)privateList<SysPermission>children;/***所有子权限URL集合*左侧菜单渲染时要用*/@TableField(exist=false)privateList<String>childrenUrl;}

• 测试mybatis plus是否可以使用

#mapperpublicinterfaceSysUserMapperextendsBaseMapper<SysUser>{}#servicepublicinterfaceSysUserServiceextendsIService<SysUser>{}@ServicepublicclassSysUserServiceImplextendsServiceImpl<SysUserMapper,SysUser>implementsSysUserService{}#controller测试@RestControllerpublicclassTestController2{@AutowiredSysUserServicesysUserService;@GetMapping("/getTest")publicStringgetTest(){List<SysUser>list=sysUserService.list();System.out.println(list);return"success";}}

• 编写用户管理

#业务层接口publicinterfaceSysUserServiceextendsIService<SysUser>{/***通过用户名查询用户信息*@paramusername用户名*@return*/SysUserfindByUsername(Stringusername);}#业务层实现类@ServicepublicclassSysUserServiceImplextendsServiceImpl<SysUserMapper,SysUser>implementsSysUserService{@OverridepublicSysUserfindByUsername(Stringusername){if(StringUtils.isEmpty(username)){returnnull;}QueryWrapperqueryWrapper=newQueryWrapper();queryWrapper.eq("username",username);//baseMapper对应的是就是SysUserMapperreturnbaseMapper.selectOne(queryWrapper);}}#控制层@RestController@RequestMapping("/test")publicclassTestController2{@AutowiredSysUserServicesysUserService;//查询单个@GetMapping("/test2")publicStringtest2(){SysUseruser=sysUserService.findByUsername("admin");System.out.println("user:"+user);return"success";}}#控制台11:24:13.599DEBUG20080---[p-nio-80-exec-1]c.y.s.mapper.SysUserMapper.selectOne:==>Preparing:SELECTid,update_date,nick_name,mobile,is_account_non_locked,password,is_account_non_expired,is_credentials_non_expired,is_enabled,email,username,create_dateFROMsys_userWHERE(username=?)11:24:13.618DEBUG20080---[p-nio-80-exec-1]c.y.s.mapper.SysUserMapper.selectOne:==>Parameters:admin(String)11:24:13.651DEBUG20080---[p-nio-80-exec-1]c.y.s.mapper.SysUserMapper.selectOne:<==Total:1user: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

• 编写角色管理

#mapperpublicinterfaceSysRoleMapperextendsBaseMapper<SysRole>{}#业务层接口publicinterfaceSysRoleServiceextendsIService<SysRole>{}#业务层实现@ServicepublicclassSysRoleServiceImplextendsServiceImpl<SysRoleMapper,SysRole>implementsSysRoleService{}#接口@AutowiredSysRoleServicesysRoleService;/***查询角色表*/@GetMapping("/test3")publicStringtest3(){SysRolerole=sysRoleService.getById(9);System.out.println("role:"+role);return"success";}#控制台11:52:35.391DEBUG11852---[p-nio-80-exec-3]c.y.s.mapper.SysRoleMapper.selectById:==>Preparing:SELECTid,update_date,name,remark,create_dateFROMsys_roleWHEREid=?11:52:35.392DEBUG11852---[p-nio-80-exec-3]c.y.s.mapper.SysRoleMapper.selectById:==>Parameters:9(Integer)11:52:35.414DEBUG11852---[p-nio-80-exec-3]c.y.s.mapper.SysRoleMapper.selectById:<==Total:1role:SysRole(id=9,name=超级管理员,remark=拥有所有的权限,createDate=TueAug0811:11:11GMT+08:002023,updateDate=TueAug0811:11:11GMT+08:002023,perList=[],perIds=[])

• 编写权限管理

#mapperpublicinterfaceSysPermissionMapperextendsBaseMapper<SysPermission>{}#业务层接口publicinterfaceSysPermissionServiceextendsIService<SysPermission>{}#业务层实现@ServicepublicclassSysPermissionServiceImplextendsServiceImpl<SysPermissionMapper,SysPermission>implementsSysPermissionService{}#控制层接口@AutowiredSysPermissionServicesysPermissionService;@GetMapping("/test4")publicStringtest4(){SysPermissionpermissions=sysPermissionService.getById(29);System.out.println("permissions:"+permissions);return"success";}#控制台11:50:03.198DEBUG11852---[p-nio-80-exec-1]c.y.s.m.SysPermissionMapper.selectById:==>Preparing:SELECTid,update_date,code,icon,remark,type,parent_id,url,name,create_dateFROMsys_permissionWHEREid=?11:50:03.210DEBUG11852---[p-nio-80-exec-1]c.y.s.m.SysPermissionMapper.selectById:==>Parameters:29(Integer)11:50:03.266DEBUG11852---[p-nio-80-exec-1]c.y.s.m.SysPermissionMapper.selectById:<==Total:1permissions:SysPermission(id=29,parentId=28,parentName=根菜单,name=列表,code=sys:permission:list,url=null,type=2,icon=null,remark=权限列表,createDate=TueAug0811:11:11GMT+08:002023,updateDate=TueAug0811:11:11GMT+08:002023,

• 根据用户id查询该用户所拥有的所有权限

#mapperpublicinterfaceSysPermissionMapperextendsBaseMapper<SysPermission>{//根据用户id查询用户所拥有的权限List<SysPermission>selectPermissionByUserId(@Param("userId")LonguserId);}<!DOCTYPEmapperPUBLIC"-//mybatis.org//DTDMapper3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.ychen.security.mapper.SysPermissionMapper"><selectid="selectPermissionByUserId"resultType="SysPermission">SELECTDISTINCTp.id,p.parent_id,p.NAME,p.CODE,p.url,p.type,p.icon,p.remark,p.create_date,p.update_dateFROMsys_userASuLEFTJOINsys_user_roleASurONu.id=ur.user_idLEFTJOINsys_roleASrONur.role_id=r.idLEFTJOINsys_role_permissionASrpONrp.role_id=r.idLEFTJOINsys_permissionASpONrp.permission_id=p.idWHEREu.id=#{userId}</select></mapper>#业务层接口publicinterfaceSysPermissionServiceextendsIService<SysPermission>{/***通过用户id查询所拥有权限*@paramuserId*@return*/List<SysPermission>findByUserId(LonguserId);}#业务层实现@ServicepublicclassSysPermissionServiceImplextendsServiceImpl<SysPermissionMapper,SysPermission>implementsSysPermissionService{@OverridepublicList<SysPermission>findByUserId(LonguserId){if(userId==null){returnnull;}List<SysPermission>permissionList=baseMapper.selectPermissionByUserId(userId);//如果没有权限，则将集合中的数据null移除permissionList.remove(null);returnpermissionList;}}#控制层接口@RestController@RequestMapping("/test")publicclassTestController2{@AutowiredSysPermissionServicesysPermissionService;@GetMapping("/test5")publicStringtest5(){List<SysPermission>permissions=sysPermissionService.findByUserId(9L);System.out.println(permissions);System.out.println("permissions:"+permissions.size());return"success";}}#控制台13:23:56.820DEBUG19664---[p-nio-80-exec-1]c.y.s.m.S.selectPermissionByUserId:==>Preparing:SELECTDISTINCTp.id,p.parent_id,p.NAME,p.CODE,p.url,p.type,p.icon,p.remark,p.create_date,p.update_dateFROMsys_userASuLEFTJOINsys_user_roleASurONu.id=ur.user_idLEFTJOINsys_roleASrONur.role_id=r.idLEFTJOINsys_role_permissionASrpONrp.role_id=r.idLEFTJOINsys_permissionASpONrp.permission_id=p.idWHEREu.id=?13:23:56.831DEBUG19664---[p-nio-80-exec-1]c.y.s.m.S.selectPermissionByUserId:==>Parameters:9(Long)13:23:56.867DEBUG19664---[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=fafa-dashboard,remark=,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=17,parentId=0,parentName=根菜单,name=系统管理,code=sys:manage,url=null,type=1,icon=fafa-cogs,remark=null,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=18,parentId=17,parentName=根菜单,name=用户管理,code=sys:user,url=/user,type=1,icon=fafa-users,remark=null,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=19,parentId=18,parentName=根菜单,name=列表,code=sys:user:list,url=,type=2,icon=,remark=员工列表,createDate=TueAug0811:11:11GMT+08:002023,updateDate=TueAug0811:11:11GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=20,parentId=18,parentName=根菜单,name=新增,code=sys:user:add,url=,type=2,icon=,remark=新增用户,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=21,parentId=18,parentName=根菜单,name=修改,code=sys:user:edit,url=,type=2,icon=,remark=修改用户,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=22,parentId=18,parentName=根菜单,name=删除,code=sys:user:delete,url=,type=2,icon=,remark=删除用户,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=23,parentId=17,parentName=根菜单,name=角色管理,code=sys:role,url=/role,type=1,icon=fafa-user-secret,remark=null,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=24,parentId=23,parentName=根菜单,name=列表,code=sys:role:list,url=null,type=2,icon=null,remark=角色列表,createDate=TueAug0811:11:11GMT+08:002023,updateDate=TueAug0811:11:11GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=25,parentId=23,parentName=根菜单,name=新增,code=sys:role:add,url=,type=2,icon=,remark=新增角色,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=26,parentId=23,parentName=根菜单,name=修改,code=sys:role:edit,url=,type=2,icon=,remark=修改角色,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=27,parentId=23,parentName=根菜单,name=删除,code=sys:role:delete,url=,type=2,icon=,remark=删除角色,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=28,parentId=17,parentName=根菜单,name=权限管理,code=sys:permission,url=/permission,type=1,icon=fafa-cog,remark=null,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=29,parentId=28,parentName=根菜单,name=列表,code=sys:permission:list,url=null,type=2,icon=null,remark=权限列表,createDate=TueAug0811:11:11GMT+08:002023,updateDate=TueAug0811:11:11GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=30,parentId=28,parentName=根菜单,name=新增,code=sys:permission:add,url=,type=2,icon=null,remark=新增权限,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=31,parentId=28,parentName=根菜单,name=修改,code=sys:permission:edit,url=,type=2,icon=null,remark=修改权限,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null),SysPermission(id=32,parentId=28,parentName=根菜单,name=删除,code=sys:permission:delete,url=,type=2,icon=,remark=删除权限,createDate=TueAug0811:11:11GMT+08:002023,updateDate=WedAug0915:26:28GMT+08:002023,children=null,childrenUrl=null)]permissions:17

• 用户名密码动态认证

@Component("customUserDetailsService")publicclassCustomUserDetailsServiceimplementsUserDetailsService{//日志Loggerlogger=LoggerFactory.getLogger(getClass());//加密方式@AutowiredPasswordEncoderpasswordEncoder;//用户业务对象@AutowiredSysUserServicesysUserService;//权限对象@AutowiredSysPermissionServicesysPermissionService;@OverridepublicUserDetailsloadUserByUsername(Stringusername)throwsUsernameNotFoundException{logger.info("请求认证的用户名:"+username);//1.通过请求的用户名去数据库中查询用户信息SysUsersysUser=sysUserService.findByUsername(username);//如果没有查询到，则抛出异常if(sysUser==null){thrownewUsernameNotFoundException("用户名或密码错误");}//2.查询该用户有哪一些权限List<SysPermission>permissions=sysPermissionService.findByUserId(sysUser.getId());//如果该用户没有权限，则直接返回该对象if(CollectionUtils.isEmpty(permissions)){returnsysUser;}/***如果有权限，则封装到该对象中*在左侧菜单动态渲染会使用，目前先把它都传入*/sysUser.setPermissions(permissions);//3.封装权限信息List<GrantedAuthority>authorities=Lists.newArrayList();for(SysPermissionsp:permissions){//获取到权限标识Stringcode=sp.getCode();authorities.add(newSimpleGrantedAuthority(code));}//将封装的权限信息添加到该对象中sysUser.setAuthorities(authorities);//4.返回该对象，springsecurity自动进行身份认证returnsysUser;}}

• 手机号+短信动态认证

#业务层接口publicinterfaceSysUserServiceextendsIService<SysUser>{/***通过手机号查询用户信息*@parammobile手机号*@return*/SysUserfindByMobile(Stringmobile);}#业务层实现@ServicepublicclassSysUserServiceImplextendsServiceImpl<SysUserMapper,SysUser>implementsSysUserService{@OverridepublicSysUserfindByMobile(Stringmobile){if(StringUtils.isEmpty(mobile)){returnnull;}QueryWrapperqueryWrapper=newQueryWrapper();queryWrapper.eq("mobile",mobile);//baseMapper对应的是就是SysUserMapperreturnbaseMapper.selectOne(queryWrapper);}}#实现UserDetailsService接口@Component("mobileUserDetailsService")publicclassMobileUserDetailsServiceimplementsUserDetailsService{//日志Loggerlogger=LoggerFactory.getLogger(getClass());//用户业务对象@AutowiredSysUserServicesysUserService;//权限对象@AutowiredSysPermissionServicesysPermissionService;@OverridepublicUserDetailsloadUserByUsername(Stringmobile)throwsUsernameNotFoundException{logger.info("请求的手机号是："+mobile);//1.通过手机号查询用户信息SysUsersysUser=sysUserService.findByMobile(mobile);//判断查询到的用户信息是否未空，为空则抛出异常if(sysUser==null){thrownewUsernameNotFoundException("该手机号未注册");}//2.查询该用户所拥有的权限List<SysPermission>permissions=sysPermissionService.findByUserId(sysUser.getId());//判断查询到的权限是否为空if(CollectionUtils.isEmpty(permissions)){returnsysUser;}/***将查询到的权限封装到该对象中*在左侧菜单动态渲染会使用，目前先把它都传入*/sysUser.setPermissions(permissions);//3.封装权限信息List<GrantedAuthority>authorities=Lists.newArrayList();for(SysPermissionsp:permissions){//获取到权限标识Stringcode=sp.getCode();authorities.add(newSimpleGrantedAuthority(code));}//将权限设置给该用户sysUser.setAuthorities(authorities);//4.返回用户对象，springsecurity自动进行身份认证returnsysUser;}}