用户 角色 权限之间的关系
image.png
mysql 数据库建表语句
t_user没动
以下是新增加的三张表t_menu t_role t_role_menu
CREATE TABLE t_menu (
menuId int(11) NOT NULL AUTO_INCREMENT,
menuName varchar(50) DEFAULT NULL,
path varchar(100) DEFAULT NULL,
icon varchar(50) DEFAULT NULL,
visible varchar(50) DEFAULT NULL,
parentId int(11) DEFAULT NULL,
parentName varchar(50) DEFAULT NULL,
orderNum varchar(11) DEFAULT NULL,
PRIMARY KEY (menuId)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- Records of t_menu
INSERT INTO t_menu VALUES ('1', '系统管理', null, 'el-icon-location', '0', '0', '', '1');
INSERT INTO t_menu VALUES ('2', '系统监控', null, 'el-icon-location', '0', '0', null, '2');
INSERT INTO t_menu VALUES ('3', '用户管理', 'findalluser', 'el-icon-location', '0', '1', '系统管理', '1');
INSERT INTO t_menu VALUES ('4', '角色管理', 'findalluser', 'el-icon-location', '0', '1', '系统管理', '2');
INSERT INTO t_menu VALUES ('5', '数据监控', 'findalluser', 'el-icon-location', '0', '2', '系统监控', '1');
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE t_role (
roleId int(11) NOT NULL AUTO_INCREMENT,
roleName varchar(50) NOT NULL,
roleSort varchar(5) NOT NULL,
status varchar(1) NOT NULL,
delFlag varchar(2) NOT NULL,
PRIMARY KEY (roleId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Records of t_role
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE t_role_menu (
roleId int(11) DEFAULT NULL,
menuId int(11) DEFAULT NULL,
KEY fk_role_id (roleId),
KEY fk_menu_id (menuId),
CONSTRAINT fk_menu_id FOREIGN KEY (menuId) REFERENCES t_menu (menuId),
CONSTRAINT fk_role_id FOREIGN KEY (roleId) REFERENCES t_role (roleId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Records of t_role_menu
SET FOREIGN_KEY_CHECKS=1;
前台findAllMenu.vue
<template>
<div>
<div class="nav">
<el-button type="primary" icon="el-icon-plus" @click="dialogFormVisible = true">新增</el-button>
</div>
<el-dialog title="菜单添加" :visible.sync="dialogFormVisible" >
<!-- <reg></reg> -->
</el-dialog>
<el-table v-loading="loading" ref="multipleTable" :data="tableData" tooltip-effect="dark" style="width: 100%" @selection-change="handleSelectionChange">
<el-table-column type="selection" width="55">
</el-table-column>
<el-table-column prop="menuName" label="菜单名称" width="120">
</el-table-column>
<el-table-column prop="parentName" label="父菜单名称" width="200">
</el-table-column>
<el-table-column prop="orderNum" label="排序" width="200">
</el-table-column>
<el-table-column prop="path" label="路径" width="120">
</el-table-column>
<el-table-column prop="icon" label="图标" width="120">
</el-table-column>
<el-table-column label="操作">
<template slot-scope="scope">
<el-button size="mini" @click="handleEdit(scope.$index, scope.row)">编辑</el-button>
<el-button size="mini" type="danger" @click="handleDelete(scope.$index, scope.row)">删除</el-button>
</template>
</el-table-column>
</el-table>
<el-pagination background layout="prev, pager, next" :total="total" :page-size="pageSize" @current-change="changePage">
</el-pagination>
</div>
</template>
<script>
//import reg from '@/components/user/Register'
export default {
data() {
return {
tableData: [],
multipleSelection: [],
//以下三个是分页相关的属性
total: 0, //查询得到
pageSize: 2, //每页条数
currentPage: 1,
dialogFormVisible: false,
loading:true
}
},
mounted() {
//调用查询所有用户的方法
this.findAll();
},
methods: {
toggleSelection(rows) {
if (rows) {
rows.forEach(row => {
this.$refs.multipleTable.toggleRowSelection(row);
});
} else {
this.$refs.multipleTable.clearSelection();
}
},
handleSelectionChange(val) {
this.multipleSelection = val;
},
findAll() {
this.loading = true;
//请求后台，查询所有用户
this.$axios.get("http://localhost:8082/vue-servlet/findAllMenu", {
params: {
currentPage: this.currentPage,
pageSize: this.pageSize
}
})
.then(response => {
let data = response.data; //后台的responseBean
//用响应数据去更新
this.tableData = data.list;
this.total = data.page.count;
this.loading = false;
})
.catch()
},
changePage: function(currentPage) {
//alert(`当前页${currentPage}`);
this.currentPage = currentPage;
this.findAll();
},
handleEdit(index, row) {
//console.log(index, row);
},
handleDelete(index, row) {
//console.log(index, row);
alert(row.id); //是实体类的属性 是主键，不是索引
//请求后台，通过id删除指定用户的信息
//删除成功之后，重新调用findall
}
}
/* ,
components: {
reg
} */
}
</script>
<style>
.nav {
text-align: left;
height: 50px;
margin: 10px;
}
.el-main {
line-height: 0;
}
</style>
后台关键代码
实体类：
public class SysMenu
{
private static final long serialVersionUID = 1L;
/** 菜单ID */
private int menuId;
/** 菜单名称 */
private String menuName;
/** 父菜单名称 */
private String parentName;
/** 父菜单ID */
private int parentId;
/** 显示顺序 */
private String orderNum;
/** 路由地址 */
private String path;
/** 菜单状态:0显示,1隐藏 */
private String visible;
/** 菜单图标 */
private String icon;
。。。。。。
}
findallmenuservelt
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 查询数据库表t_user里的所有数据，返回list
IMenuService us = new MenuServiceImpl();
// 从前台获取当前请求页码和每页条数
String currentPage = request.getParameter("currentPage");// 当前面码
String pageSize = request.getParameter("pageSize");// 每页条数
// step1 调用service的count，汇总一下总条数
// select count(*) from t_user
int count = 0;
try {
count = us.count();
} catch (ClassNotFoundException | SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// step 2 新建一个pageBean，传到前台,供前台以后调用
PageBean pb = new PageBean();
pb.setPageSize(Integer.parseInt(pageSize));//
pb.setCount(count);// 需要查询数据库 这条很关键
pb.setCurrentPage(Integer.parseInt(currentPage));
try {
// 将list放入响应对象 ResponseBean里
List<SysMenu> list = us.findAll(pb.getPageSize(), pb.getCurrentPage());
ResponseBean resp = new ResponseBean();
resp.setCode("0");
resp.setMsg("查询成功");
resp.setList(list);
resp.setPage(pb);
// 转成json，给前台
ObjectMapper om = new ObjectMapper();// 转成json，给前台
String json = om.writeValueAsString(resp);
response.getWriter().write(json);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
MenuDaoImpl
@Override
public List<SysMenu> findAll() throws SQLException, ClassNotFoundException {
List<SysMenu> list=new ArrayList();
Connection conn=DBUtil.getConnection();
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=conn.prepareStatement("select * from t_menu");
rs=ps.executeQuery();
while(rs.next()){//如果有该用户
SysMenu obj=new SysMenu();
obj.setMenuId(rs.getInt("menuId"));
obj.setMenuName(rs.getString("menuName"));
obj.setPath(rs.getString("path"));
obj.setIcon(rs.getString("icon"));
obj.setVisible(rs.getString("visible"));
obj.setParentId(rs.getInt("parentId"));
obj.setParentName(rs.getString("parentName"));
obj.setOrderNum(rs.getString("orderNum"));
list.add(obj);
}
} catch (SQLException e) {
e.printStackTrace();
}
DBUtil.closeDB(rs, ps, conn);
return list;
}
@Override
public int count() throws SQLException, ClassNotFoundException {
int count=0;
Connection conn=DBUtil.getConnection();
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=conn.prepareStatement("select count(*) from t_menu");
rs=ps.executeQuery();
if(rs.next()){//如果有该用户
count=rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
DBUtil.closeDB(rs, ps, conn);
return count;
}
@Override
public List<SysMenu> findAll(int pageSize, int currentPage) throws SQLException {
//调用jdbc程序进行存储
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs=null;
List<SysMenu> list=new ArrayList();
try {
//加载驱动，定义连接参数，建立连接
conn=DBUtil.getConnection();
//预处理
ps=conn.prepareStatement("select * from t_menu limit ?,?");
ps.setInt(1, pageSize*(currentPage-1));
ps.setInt(2, pageSize);
rs=ps.executeQuery();
//循环结果集，多条用while
while(rs.next()){
//循环到一条，就新实例化一个menu
SysMenu obj=new SysMenu();
obj.setMenuId(rs.getInt("menuId"));
obj.setMenuName(rs.getString("menuName"));
obj.setPath(rs.getString("path"));
obj.setIcon(rs.getString("icon"));
obj.setVisible(rs.getString("visible"));
obj.setParentId(rs.getInt("parentId"));
obj.setParentName(rs.getString("parentName"));
obj.setOrderNum(rs.getString("orderNum"));
list.add(obj);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();//异常详情，给程序看的
} catch (SQLException e) {
e.printStackTrace();
}
finally {
DBUtil.closeDB(rs, ps, conn);
}
return list;
}
目前效果
image.png
完善原有的菜单功能
由原来的程序写死变为从t_menu表读取数据后动态加载菜单功能
改造;menu.vue
<template>
<el-menu
class="el-menu-vertical-demo"
@open="handleOpen"
@close="handleClose"
background-color="#545c64"
text-color="#fff"
active-text-color="#ffd04b"
:default-active="$router.path" router>
<!--select * from t_menu where parentId=0 加载出父菜单
select * from t_menu where parentId=1
{"code":"0","msg":null,"list":[{"menuId":1,"menuName":"系统管理","parentName":"","parentId":0,"orderNum":"1","path":null,"visible":"0","icon":"el-icon-location"},{"menuId":2,"menuName":"系统监控","parentName":null,"parentId":0,"orderNum":"2","path":null,"visible":"0","icon":"el-icon-location"}],"page":null}
{"code":"0","msg":null,"list":[{"menuId":3,"menuName":"用户管理","parentName":"系统管理","parentId":1,"orderNum":"1","path":"findalluser","visible":"0","icon":"el-icon-location"},{"menuId":4,"menuName":"角色管理","parentName":"系统管理","parentId":1,"orderNum":"2","path":"findalluser","visible":"0","icon":"el-icon-location"}],"page":null}
如何判断当前的子菜单是哪个父类的呢？
-->
<el-submenu v-for="menu in menuList" index="menu.menuId" :key="menu.menuId">
<template slot="title">
<i :class="menu.icon"></i>
<span @click="getChildList(menu.menuId)">{{menu.menuName}}</span>
</template>
<el-menu-item-group v-if="child.parentId==menu.menuId" v-for="child in childList" :key="child.menuId" >
<el-menu-item :index="child.path"> {{child.menuName}}</el-menu-item>
</el-menu-item-group>
</el-submenu>
</el-menu>
</template>
<script>
export default{
data(){
return {
menuList:[],//父级
childList:[]
}
},
methods: {
handleOpen(key, keyPath) {
console.log(key, keyPath);
},
handleClose(key, keyPath) {
console.log(key, keyPath);
},
getList(){
//请求后台获取父级菜单列表
this.$axios.get("http://localhost:8082/vue-servlet/findMenuByParentId?parentId=0")
.then(response=>{
//用返回的list去更新menuList
this.menuList=response.data.list;
})
},
getChildList(parentId){
this.childList=null;
//alert(parentId);
this.$axios.get("http://localhost:8082/vue-servlet/findMenuByParentId?parentId="+parentId)
.then(response=>{
//用返回的list去更新menuList
this.childList=response.data.list;
})
}
},
mounted(){
this.getList();
}
}
</script>
<style>
.el-menu-vertical-demo{
border:none;
}
</style>
data(){
return {
// 菜单表格树数据
menuList: [],
childList:[]
}
},
mounted(){
this.getList();
}
methods: {
getList(){
this.$axios.get("http://localhost:8082/vue-servlet/findAllMenu")
.then(response => {
console.log(response);
//查询成功之后，携带用户信息进修改页
this.menuList=response.data.list;
})
.catch(error => {
console.log(error);
})
},
getChildList(parentId){
alert(parentId);
this.$axios.get("http://localhost:8082/vue-servlet/findAllMenu?parentId="+parentId)
.then(response => {
console.log(response);
//查询成功之后，携带用户信息进修改页
this.childList=response.data.list;
})
.catch(error => {
console.log(error);
})
}
}
后台关键代码
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//获取页面传输的parentId
String parentId=request.getParameter("parentId");
//调用后台查询
IMenuService us = new MenuServiceImpl();
List<SysMenu> menuList=null;
try {
menuList = us.getMenuList(parentId);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//拼装response对象
ResponseBean resp=new ResponseBean();
resp.setList(menuList);
resp.setCode("0");
//转json
ObjectMapper om=new ObjectMapper();
String json=om.writeValueAsString(resp);
System.out.println(json);
//打印到前台
response.getWriter().print(json);
}
@Override
public List<SysMenu> getMenuList(String parentId) throws SQLException, ClassNotFoundException {
List<SysMenu> list=new ArrayList();
Connection conn=DBUtil.getConnection();
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=conn.prepareStatement("select * from t_menu where visible=0 and parentId=?");
ps.setInt(1, Integer.parseInt(parentId));
rs=ps.executeQuery();
while(rs.next()){//如果有该用户
SysMenu obj=new SysMenu();
obj.setMenuId(rs.getInt("menuId"));
obj.setMenuName(rs.getString("menuName"));
obj.setPath(rs.getString("path"));
obj.setIcon(rs.getString("icon"));
obj.setVisible(rs.getString("visible"));
obj.setParentId(rs.getInt("parentId"));
obj.setParentName(rs.getString("parentName"));
obj.setOrderNum(rs.getString("orderNum"));
list.add(obj);
}
} catch (SQLException e) {
e.printStackTrace();
}
DBUtil.closeDB(rs, ps, conn);
return list;
}
image.png
作业
学生自行完成菜单添加功能
菜单添加功能
预备知识：先学习异步树
step1 制作 菜单树common/menutree
<template>
<div>
<el-tree
:props="props"
:load="loadNode"
node-key="id"
@node-click="handleNodeClick"
lazy
>
</el-tree>
</div>
</template>
<script>
export default {
data() {
return {
props: {
id:0,//新增加的，将来这部分就放菜单的id
label: 'name',//菜单名字
children: 'zones',//子菜单
isLeaf: 'leaf'
},
};
},
methods: {
handleNodeClick(data) {
console.log(data.id);
},
loadNode(node, resolve) {
if (node.level === 0) {
return resolve([{ id:0,name: 'root' }]);
}else{//无限级的菜单
let selid = node.data.id;
this.$axios.get("http://localhost:8082/vue-servlet/getMenuData?id=" + selid)
.then(response => {
resolve(response.data.list);
})
}
}
}
};
</script>
<style>
</style>
step 2 组件方式加入到addMenu里。
改造上面的handleNodeClick方法，向父组件发送id
handleNodeClick(data) {
console.log(data.id);
this.$emit("getParentId",data.id)
},
父组件引用和接收，开发 menu/addMenu
<template>
<div>
<el-form ref="form" :model="form" :rules="rules" label-width="80px">
<el-row>
<el-col :span="24">
<el-form-item label="上级菜单" v-model="form.parentId">
<menutree @getParentId="getId"></menutree>
</el-form-item>
</el-col>
<el-col :span="24">
<el-form-item label="菜单图标">
<el-input v-model="form.icon">
</el-input>
</el-popover>
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="菜单名称" prop="menuName">
<el-input v-model="form.menuName" placeholder="请输入菜单名称" />
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="显示排序" prop="orderNum">
<el-input-number v-model="form.orderNum" :min="0" :max="50" label="排序"></el-input-number>
</el-form-item>
</el-col>
<el-col :span="24">
<el-form-item label="路由地址" prop="path">
<el-input v-model="form.path" placeholder="请输入路由地址" />
</el-form-item>
</el-col>
<el-col :span="12">
<el-form-item label="显示状态" prop="visible">
<el-radio v-model="form.visible" label="0">显示</el-radio>
<el-radio v-model="form.visible" label="1">隐藏</el-radio>
</el-form-item>
</el-col>
</el-row>
<el-form-item>
<el-button type="primary" @click="submitForm('form')">立即创建</el-button>
<el-button @click="resetForm('form')">重置</el-button>
</el-form-item>
</el-form>
</div>
</template>
<script>
import menutree from '@/components/common/menutree'
export default {
data() {
return {
form:{
parentId:0,
menuName:'',
orderNum:0,
path:'',
icon:'',
visible:0
},
rules: {
//required是否必填项 trigger:触发方式 blur：表示离开焦点时 message：错误提示
menuName: [
{ required: true, message: '请输入名称', trigger: 'blur' },
{ min: 4, max: 20, message: '长度在 6 到 20 个字符', trigger: 'blur' }
]}
};
},
methods: {
submitForm(formName) {
this.$refs[formName].validate((valid) => {
if (valid) {
//alert('submit!');
//step 1，把form转换成json字符串形式
let obj=this.$qs.stringify(this.form);
alert(obj);
//step 2计划在这里用axios去给后台发送注册请求 (url,data) data-json
this.$axios.post('http://localhost:8082/vue-servlet/addMenu',obj)
.then(response=>{
alert(response.data.msg);
})
.catch(error=>{
console.log(error)
})
} else {
console.log('error submit!!');
return false;
}
});
},
resetForm(formName) {
this.$refs[formName].resetFields();
},
getId(id){
//alert("parentid:"+id);
this.form.parentId=id;
}
},
components:{
menutree
}
};
</script>
<style>
</style>
关键代码：
image.png
image.png
image.png
页面效果：
image.png
后台 关键代码
addMenuServlet
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String parentId=request.getParameter("parentId");
String menuName=request.getParameter("menuName");
String orderNum=request.getParameter("orderNum");
String path=request.getParameter("path");
String icon=request.getParameter("icon");
String visible=request.getParameter("visible");
SysMenu sm=new SysMenu();
sm.setIcon(icon);
sm.setMenuName(menuName);
sm.setOrderNum(orderNum);
sm.setParentId(Integer.parseInt(parentId));
sm.setPath(path);
sm.setVisible(visible);
IMenuService ms=new MenuServiceImpl();
int result=0;
try {
result = ms.save(sm);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ResponseBean rb=new ResponseBean();
if(result>0){
rb.setCode("0");
rb.setMsg("添加成功");
}
ObjectMapper om=new ObjectMapper();
String json=om.writeValueAsString(rb);
response.getWriter().write(json);
}
MenuDaoImpl
@Override
public int save(SysMenu menu) throws SQLException, ClassNotFoundException {
Connection conn = null;
PreparedStatement ps = null;
// 加载驱动，定义连接参数，建立连接
conn = DBUtil.getConnection();
// 预处理
ps = conn.prepareStatement("insert into t_menu values(null,?,?,?,?,?,?,?)");
ps.setString(1, menu.getMenuName());
ps.setString(2, menu.getPath());
ps.setString(3, menu.getIcon());
ps.setString(4, menu.getVisible());
ps.setInt(5, menu.getParentId());
ps.setString(6, "");
ps.setString(7, menu.getOrderNum());
int num = ps.executeUpdate();
DBUtil.closeDB(null, ps, conn);
return num;
}
网友评论