2023-7-17

使用PDManer建表

新建项目

点击创建主题区域

点击数据表右键选择新增数据表

• 创建users表

• 创建roles表

• 创建permissions表

• 用户-角色表

• 角色-权限表

创建关系图

鼠标左键可以改变关系

MySQL的cmd操作

找到MySQL的安装目录

右键选择在终端中打开

输入以下命令从而链接上数据库

mysql -uroot -p123456

使用命令切换数据库

use 16ban

往表格加入数据

• 往permissions表加入数据

//一级菜单
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,icon,IS_PARENT) values(0,'首页','home.html','content','fa fa-home','true');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,icon,IS_PARENT) values(0,'用户管理','null','null','fa fa-user','true');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,icon,IS_PARENT) values(0,'角色管理','null','null','fa fa-user-o','true');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,icon,IS_PARENT) values(0,'权限管理','null','null','fa fa-bus','true');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,icon,IS_PARENT) values(0,'文章管理','null','null','fa fa-book','true');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,icon,IS_PARENT) values(0,'轮播图管理','null','null','fa fa-user-o','true');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,icon,IS_PARENT) values(0,'新闻管理','null','null','fa fa-file-zip-o','true');

创建成功

//二级菜单
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,IS_PARENT) values(2,'用户列表','userList.html','content','false');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,IS_PARENT) values(3,'角色列表','roleList.html','content','false');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,IS_PARENT) values(4,'权限列表','permissionList.html','content','false');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,IS_PARENT) values(5,'文章列表','articleList.html','content','false');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,IS_PARENT) values(5,'文章分类','articleCatgory.html','content','false');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,IS_PARENT) values(6,'轮播图列表','loopImgList.html','content','false');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,IS_PARENT) values(7,'发布新闻','publishNews.html','content','false');
insert into permissions(PARENT_PERMISSION_ID,PERMISSION_NAME,url,target,IS_PARENT) values(7,'查看新闻','viewNews.html','content','false');

• roles表数据

insert into roles(role_name) values('超级管理员');
insert into roles(role_name) values('项目经理');
insert into roles(role_name) values('测试人员');

• 角色-权限数据

--超级管理员权限
insert into role_permission values(1,1);
insert into role_permission values(1,2);
insert into role_permission values(1,3);
insert into role_permission values(1,4);
insert into role_permission values(1,5);
insert into role_permission values(1,6);
insert into role_permission values(1,7);
insert into role_permission values(1,8);
insert into role_permission values(1,9);
insert into role_permission values(1,10);
insert into role_permission values(1,11);
insert into role_permission values(1,12);
insert into role_permission values(1,13);
insert into role_permission values(1,14);
insert into role_permission values(1,15);
--测试人员权限
insert into role_permission values(3,1);
insert into role_permission values(3,7);
insert into role_permission values(3,14);
insert into role_permission values(3,15);

• 用户数据

insert into users(username,password) values('admin','admin1234');
insert into users(username,password) values('guet','guet1234');

• 用户-角色数据

insert into user_role values(1,1);
insert into user_role values(2,3);

如果此时用户是guet，写一条SQL来获取guet 的所有菜单

--涉及多表查询
select p.*
from users u,user_role ur,roles r,permissions p,role_permission rp
where u.user_id=ur.user_id and ur.role_id=r.role_id and
r.role_id=rp.role_id and p.permission_id=rp.permission_id
and u.username='guet'

后端代码运行

利用Generator快速生成SpringMVC框架中的相关代码

• 配置applicationContext.xml
  
• Generator函数生成
  
  

创建SQL语句

• 查找用户guet的所有菜单

select p.*
from users u,user_role ur,roles r,permissions p,role_permission rp
where u.user_id=ur.user_id and ur.role_id=r.role_id and
r.role_id=rp.role_id and p.permission_id=rp.permission_id
and u.username='guet'

• 在UserMapper中添加getPermissionByUsername()方法
  
• 在xml文件中实现该方法
  

创建UserController

• 调用getPermissionByUsername()进行查询

• 配置applicationContext.xml
  

Postman进行测试

• 结果：

• 发现数据不全，解决如下
  resultType换成resultMap="cn.edu.guet.mapper.PermissionMapper.BaseResultMap"
  
  结果如下：