整体表结构
部门表设计
drop table if exists department;
create table department(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`name` varchar(255) not null comment '部门名称',
`parent_id` bigint comment '上级部门id',
`desc` varchar(512) comment '部门描述',
`level` int not null comment '当前层级'
)comment '部门表';
insert into department values(1,"技术部",null,"技术为王",1);
insert into department values(2,"销售部",null,"销售为天",1);
insert into department values(3,"人力资源部",null,"HR最美",1);
insert into department values(4,"前端组",1,"html+js+css",2);
insert into department values(5,"后端组",1,"java+python+php",2);
insert into department values(6,"运维组",1,"prometheus",2);
insert into department values(7,"Java组",5,"html+js+css",3);
insert into department values(8,"Python组",5,"java+python+php",3);
insert into department values(9,"Php组",5,"prometheus",3);
insert into department values(10,"销售1组",2,"今夜打老虎",2);
insert into department values(11,"销售2组",2,"无敌风火轮",2);
insert into department values(12,"销售3组",2,"天王盖地虎",2);
insert into department values(13,"薪酬组",3,"数钱数到手抽筋",2);
insert into department values(14,"招聘组",3,"拿着白菜钱操着白粉心",2);
insert into department values(15,"员工关系组",3,"为中华之崛起而奋斗",2);
@Override
public List<DepartmentVo> getDepartmentVoTree() {
List<Department> list = departmentMapperExt.getAll();
List<DepartmentVo> departmentVoList = BeanCoperUtil.copyList(list, DepartmentVo.class);
List<DepartmentVo> topList = departmentVoList.stream().filter(e -> e.getParentId() == null)
.collect(Collectors.toList());
departmentVoList.removeAll(topList);
AtomicLong level = new AtomicLong(2);
List<DepartmentVo> parentList = topList;
while (!departmentVoList.isEmpty()) {
List<DepartmentVo> chilrenList = departmentVoList.stream().filter(e -> e.getLevel() == level.longValue())
.collect(Collectors.toList());
Map<Long, DepartmentVo> parentMap = parentList.stream()
.collect(Collectors.toMap(DepartmentVo::getId, e -> e));
for (DepartmentVo departmentVo : chilrenList) {
Long parentId = departmentVo.getParentId();
List<DepartmentVo> currentChilrenList = parentMap.get(parentId).getChildren();
if (currentChilrenList == null) {
currentChilrenList = new ArrayList<>();
parentMap.get(parentId).setChildren(currentChilrenList);
}
currentChilrenList.add(departmentVo);
}
departmentVoList.removeAll(chilrenList);
parentList = chilrenList;
level.addAndGet(1);
}
return topList;
}
- 使用jsTree展示
整体表设计-精简版
drop database rbac;
create database if not exists rbac DEFAULT CHARACTER SET utf8 ;
use rbac;
drop table if exists department;
create table department(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`name` varchar(255) not null comment '部门名称',
`parent_id` bigint comment '上级部门id',
`desc` varchar(512) comment '部门描述',
`level` int not null comment '当前层级'
)comment '部门表';
insert into department values(1,"技术部",null,"技术为王",1);
insert into department values(2,"销售部",null,"销售为天",1);
insert into department values(3,"人力资源部",null,"HR最美",1);
insert into department values(4,"前端组",1,"html+js+css",2);
insert into department values(5,"后端组",1,"java+python+php",2);
insert into department values(6,"运维组",1,"prometheus",2);
insert into department values(7,"Java组",5,"html+js+css",3);
insert into department values(8,"Python组",5,"java+python+php",3);
insert into department values(9,"Php组",5,"prometheus",3);
insert into department values(10,"销售1组",2,"今夜打老虎",2);
insert into department values(11,"销售2组",2,"无敌风火轮",2);
insert into department values(12,"销售3组",2,"天王盖地虎",2);
insert into department values(13,"薪酬组",3,"数钱数到手抽筋",2);
insert into department values(14,"招聘组",3,"拿着白菜钱操着白粉心",2);
insert into department values(15,"员工关系组",3,"为中华之崛起而奋斗",2);
create table user(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`username` varchar(255) comment '用户名',
`password` varchar(255) comment '密码'
)comment '用户表';
create table department_user(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`department_id` bigint(20) comment '部门ID',
`user_id` bigint(20) comment '用户ID'
)comment '部门-用户表';
create table role(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`code` varchar(255) comment '角色编码',
`name` varchar(255) comment '角色名称'
)comment '角色表';
create table user_role(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`user_id` bigint(20) comment '用户ID',
`role_id` bigint(20) comment '角色ID'
)comment '用户-角色表';
create table department_role(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`department_id` bigint(20) comment '部门ID',
`role_id` bigint(20) comment '角色ID'
)comment '部门-角色表';
create table permission(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`code` varchar(255) comment '权限编码',
`name` varchar(255) comment '权限名称',
`resource` varchar(255) comment '权限关联的资源'
)comment '权限表';
create table user_permission(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`user_id` bigint(20) comment '用户ID',
`permission_id` bigint(20) comment '权限ID'
)comment '用户-权限表';
create table role_permission(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`role_id` bigint(20) comment '角色ID',
`permission_id` bigint(20) comment '权限ID'
)comment '角色-权限表';
create table department_permission(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`role_id` bigint(20) comment '用户ID',
`department_id` bigint(20) comment '权限ID'
)comment '部门-权限表';
create table operation_log(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT primary key comment '主键',
`table_id` int comment '表id:department=1;user=2;department_user=3;role=4;user_role=5;depart_role=6;permission=7;department_permission=8;user_permission=9;role_permission=10',
`sql`varchar(1024) comment '执行的sql',
`op_type` int comment '操作类型:select=0;insert=1;update=2;delete=3'
)comment '操作日志表';