文章目录
一、SQL1.1. role表新增角色标识1.2. 新增用户角色关系表1.3. 数据复制1.4. 设计视图1.5. 完成版视图二、用户角色关系2.1. 创建UserRole实体类2.2. 持久层接口2.3. 逻辑层2.4. xml映射2.5.三、现代码调整3.1. 新增标识3.2. 删除用户关联条件3.3. 删除角色判断四、工作流业务模块调整4.1. 关联关系调整4.2. 节点提交调整五、登陆调整5.1.内置用户一、SQL
1.1. role表新增角色标识
ALTER TABLE sys_role ADD role_key varchar(255) COMMENT '角色标识';
1.2. 新增用户角色关系表
CREATE TABLE `sys_user_role` (`user_id` bigint(20) NOT null COMMENT '用户id',`role_id` bigint(20) NOT null COMMENT '角色id',PRIMARY KEY (`user_id`,`role_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户和角色关联表';
1.3. 数据复制
将act_id_membership表中数据复制到新增sys_user_role表中，修改表名、字段名称
1.4. 设计视图
视图设计要点：
对应关系
业务场景
补充：由于工作流act_id_user表中的_ID是账户，因此，系统用户表中的account 和 act_id_user表中的_ID对应
sql
--角色表SELECT `r`.`role_key` AS `ID_`,NULL AS `REV_`, `r`.`name` AS `NAME_`,'assignment' AS `TYPE_` FROM `sys_role` `r`;--用户角色中间表SELECT(SELECT`u`.`account` FROM `sys_user` `u` WHERE( `u`.`user_id` = `ur`.`user_id`)) AS `USER_ID_`,(SELECT `r`.`role_key` FROM `sys_role` `r` WHERE( `r`.`role_id` = `ur`.`role_id` )) AS `GROUP_ID_` FROM `sys_user_role` `ur`;--用户表SELECT `u`.`account` AS `ID_`,0 AS `REV_`, `u`.`name` AS `FIRST_`,'' AS `LAST_`, `u`.`email` AS `EMAIL_`, `u`.`password` AS `PWD_`,'' AS `PICTURE_ID_` FROM `sys_user` `u`;
1.5. 完成版视图
--视图-- ------------------------------ View structure for act_id_group-- ----------------------------DROP VIEW IF EXISTS `act_id_group`;CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `act_id_group` AS SELECT `r`.`role_key` AS `ID_`,NULL AS `REV_`, `r`.`name` AS `NAME_`,'assignment' AS `TYPE_` FROM `sys_role` `r`;-- ------------------------------ View structure for act_id_membership-- ----------------------------DROP VIEW IF EXISTS `act_id_membership`;CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `act_id_membership` AS SELECT(SELECT`u`.`account` FROM `sys_user` `u` WHERE( `u`.`user_id` = `ur`.`user_id`)) AS `USER_ID_`,(SELECT `r`.`role_key` FROM `sys_role` `r` WHERE( `r`.`role_id` = `ur`.`role_id` )) AS `GROUP_ID_` FROM `sys_user_role` `ur`;-- ------------------------------ View structure for act_id_user-- ----------------------------DROP VIEW IF EXISTS `act_id_user`;CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `act_id_user` AS SELECT `u`.`account` AS `ID_`,0 AS `REV_`, `u`.`name` AS `FIRST_`,'' AS `LAST_`, `u`.`email` AS `EMAIL_`, `u`.`password` AS `PWD_`,'' AS `PICTURE_ID_` FROM `sys_user` `u`;
二、用户角色关系
2.1. 创建UserRole实体类
package com.gblfy.modular.system.entity;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableName;import java.io.Serializable;/*** <p>* 角色和菜单关联表* </p>** @author gblfy* @since /11/12*/@TableName("sys_user_role")public class UserRole implements Serializable {private static final long serialVersionUID = 1L;/*** 菜单id*/@TableField("user_id")private Long userId;/*** 角色id*/@TableField("role_id")private Long roleId;public Long getUserId() {return userId;}public void setUserId(Long userId) {this.userId = userId;}public Long getRoleId() {return roleId;}public void setRoleId(Long roleId) {this.roleId = roleId;}@Overridepublic String toString() {return "UserRole{" +"userId=" + userId +", roleId=" + roleId +'}';}}
2.2. 持久层接口
package com.gblfy.modular.system.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.gblfy.modular.system.entity.UserRole;import java.util.List;/*** <p>* 用户角色关系表 Mapper 接口* </p>** @author gblfy* @since /11/05*/public interface UserRoleMapper extends BaseMapper<UserRole> {/*** 批量新增用户角色信息** @param userRoleList 用户角色列表* @return 结果*/public int batchUserRole(List<UserRole> userRoleList);/*** 通过用户ID删除用户和角色关联** @param userId 用户ID* @return 结果*/public int deleteUserRoleByUserId(Long userId);}
2.3. 逻辑层
package com.gblfy.modular.system.service;import cn.hutool.core.convert.Convert;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;import com.gblfy.modular.system.entity.User;import com.gblfy.modular.system.entity.UserRole;import com.gblfy.modular.system.mapper.UserMapper;import com.gblfy.modular.system.mapper.UserRoleMapper;import com.gblfy.modular.system.util.StringUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.ArrayList;import java.util.List;/*** <p>* 用户角色关系表 服务实现类* </p>** @author gblfy* @since /11/07*/@Servicepublic class UserRoleService extends ServiceImpl<UserMapper, User> {@Autowiredprivate UserRoleMapper userRoleMapper;/*** 分配角色** @param userId* @param roleIds*/public void assignRoles(Long userId, String roleIds) {// 删除用户与角色关联userRoleMapper.deleteUserRoleByUserId(userId);// 新增用户与角色管理insertUserRole(userId,roleIds);}/*** 新增用户角色信息** @param userId* @param roleIds*/public void insertUserRole(Long userId, String roleIds) {Long[] roles = Convert.toLongArray(roleIds.split(","));if (StringUtils.isNotNull(roles)) {// 新增用户与角色管理List<UserRole> list = new ArrayList<UserRole>();for (Long roleId : roles) {UserRole ur = new UserRole();ur.setUserId(userId);ur.setRoleId(roleId);list.add(ur);}if (list.size() > 0) {userRoleMapper.batchUserRole(list);}}}// public static void main(String[] args) {//String roleIds = "1,2,3,4,5";//Long[] roles = Convert.toLongArray(roleIds.split(","));//for (Long role : roles) {// System.out.println(JSON.toJSONString(role, true));//}//// }}
2.4. xml映射
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.gblfy.modular.system.mapper.UserRoleMapper"><insert id="batchUserRole">insert into sys_user_role (user_id, role_id) values<foreach item="item" index="index" collection="list" separator=",">(#{item.userId},#{item.roleId})</foreach></insert><delete id="deleteUserRoleByUserId" parameterType="Long">deletefrom sys_user_rolewhere user_id = #{userId}</delete></mapper>
2.5.
三、现代码调整
3.1. 新增标识
Role实体类新增role_key标识Role.xml新增role_key标识前台role添加页面新增role_key标识
3.2. 删除用户关联条件
删除用户并删除用户角色中间表数据
3.3. 删除角色判断
删除角色需要判断该角色下是否存在用户，如果存在则需要先删除该角色下的所有用户
四、工作流业务模块调整
4.1. 关联关系调整
将系统工作流act_id_user中_ID 关联sys_user中的账户，
act_id_group中的_ID采用sys_role中的role_key标识
以前的id均调整为账户account和rile_key
4.2. 节点提交调整
将节点提交用户id和角色id调整为account和role_key
注：account表示工作流用户 role_key表示工作流角色
五、登陆调整
5.1.内置用户
使用admin用户部署
User user = new UserEntityImpl();user.setId("admin");SecurityUtils.assumeUser(user);
