Layui 根据数据库数据生成菜单

简单记录项目开发技巧,方便以后查看。

目标

效果一:使用管理员登录

效果二:使用VIP登录

脚本初始化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- ----------------------------
-- 9、菜单权限表
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu` (
`menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
`menu_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '菜单名称',
`parent_id` int(11) NULL DEFAULT 0 COMMENT '父菜单ID',
`order_num` int(4) NULL DEFAULT 0 COMMENT '显示顺序',
`url` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '#' COMMENT '请求地址',
`menu_type` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '菜单类型(M目录 C菜单)',
`visible` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '菜单状态(0显示 1隐藏)',
`icon` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '#' COMMENT '菜单图标',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2000 CHARACTER SET = utf8 COMMENT = '菜单权限表';


-- ----------------------------
-- 初始化-菜单信息表数据
-- ----------------------------
-- 一级菜单
INSERT INTO `sys_menu` VALUES (1, '系统管理', 0, 1, '#', 'M', '0', 'layui-icon layui-icon-component', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '系统管理目录');
INSERT INTO `sys_menu` VALUES (2, '系统监控', 0, 2, '#', 'M', '0', 'layui-icon layui-icon-template', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '系统监控目录');
INSERT INTO `sys_menu` VALUES (3, '系统工具', 0, 3, '#', 'M', '0', 'layui-icon layui-icon-app', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '系统工具目录');
INSERT INTO `sys_menu` VALUES (4, '系统设置', 0, 4, '#', 'M', '0', 'layui-icon layui-icon-set', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '系统设置目录');

-- 二级菜单
INSERT INTO `sys_menu` VALUES (100, '用户管理', 1, 1, '/sysUser/user/user', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '用户管理菜单');
INSERT INTO `sys_menu` VALUES (101, '角色管理', 1, 2, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '角色管理菜单');
INSERT INTO `sys_menu` VALUES (102, '菜单管理', 1, 3, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '菜单管理菜单');
INSERT INTO `sys_menu` VALUES (107, '通知公告', 1, 4, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '通知公告菜单');
INSERT INTO `sys_menu` VALUES (108, '日志管理', 1, 5, '#', 'M', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '日志管理菜单');
INSERT INTO `sys_menu` VALUES (109, '在线用户', 2, 1, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '在线用户菜单');
INSERT INTO `sys_menu` VALUES (111, '数据监控', 2, 2, '/tool/druid', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '数据监控菜单');
INSERT INTO `sys_menu` VALUES (112, '文档接口', 3, 1, '/tool/swagger', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '表单构建菜单');
INSERT INTO `sys_menu` VALUES (113, '定时任务', 3, 2, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '代码生成菜单');
INSERT INTO `sys_menu` VALUES (114, '404页面', 3, 3, '/tool/404test', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '系统接口菜单');
INSERT INTO `sys_menu` VALUES (115, '网站设置', 4, 1, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '网站设置菜单');
INSERT INTO `sys_menu` VALUES (116, '邮件服务', 4, 2, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '邮件服务菜单');

-- 三级菜单
INSERT INTO `sys_menu` VALUES (500, '操作日志', 108, 1, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '操作日志菜单');
INSERT INTO `sys_menu` VALUES (501, '登录日志', 108, 2, '#', 'C', '0', '#', '2018-11-11 11:11:11', '2018-11-11 11:11:11', '登录日志菜单');


-- ----------------------------
-- 10、角色和菜单关联表 角色1-N菜单
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_menu`;
CREATE TABLE `sys_role_menu` (
`r_id` int(11) NOT NULL COMMENT '角色ID',
`m_id` int(11) NOT NULL COMMENT '菜单ID',
PRIMARY KEY (`r_id`, `m_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '角色和菜单关联表';

-- ----------------------------
-- 初始化-角色和菜单关联表数据
-- ----------------------------
INSERT INTO `sys_role_menu` VALUES (2, 1);
INSERT INTO `sys_role_menu` VALUES (2, 2);
INSERT INTO `sys_role_menu` VALUES (2, 3);
INSERT INTO `sys_role_menu` VALUES (2, 100);
INSERT INTO `sys_role_menu` VALUES (2, 101);
INSERT INTO `sys_role_menu` VALUES (2, 107);
INSERT INTO `sys_role_menu` VALUES (2, 111);
INSERT INTO `sys_role_menu` VALUES (2, 112);
INSERT INTO `sys_role_menu` VALUES (2, 113);
INSERT INTO `sys_role_menu` VALUES (2, 114);


-- ----------------------------
-- 2、角色表
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role`(
`role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
`role_name` varchar(50) NOT NULL COMMENT '角色表示,如admin、user',
`description` varchar(50) NOT NULL COMMENT '角色描述,如管理员,用户',
`available` char(1) DEFAULT '0' COMMENT '是否可用:正常:0, 禁用:1',
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`role_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '角色表';

-- ----------------------------
-- 初始化-角色表信息数据
-- ----------------------------
INSERT INTO `sys_role`(`role_id`,`role_name`,`description`,`available`) VALUES (null, 'admin','管理员','0');
INSERT INTO `sys_role`(`role_id`,`role_name`,`description`,`available`) VALUES (null, 'vip','VIP会员','0');
INSERT INTO `sys_role`(`role_id`,`role_name`,`description`,`available`) VALUES (null, 'user','普通用户','0');

实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
/**
* <p>
* 菜单权限表
* </p>
*
* @author Ray
* @since 2018-11-27
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class SysMenu implements Serializable {

private static final long serialVersionUID = 1L;

/**
* 菜单ID
*/
@TableId(value = "menu_id", type = IdType.AUTO)
private Integer menuId;

/**
* 菜单名称
*/
private String menuName;

/**
* 父菜单ID
*/
private Integer parentId;

/**
* 显示顺序
*/
private Integer orderNum;

/**
* 请求地址
*/
private String url;

/**
* 菜单类型(M目录 C菜单 F按钮)
*/
private String menuType;

/**
* 菜单状态(0显示 1隐藏)
*/
private String visible;

/**
* 菜单图标
*/
private String icon;

/**
* 创建时间
*/
@TableField(fill = FieldFill.INSERT)
private LocalDateTime createTime;

/**
* 更新时间
*/
@TableField(fill = FieldFill.INSERT_UPDATE)
private LocalDateTime updateTime;

/**
* 备注
*/
private String remark;

/**
* 子菜单
*/
@TableField(exist = false)
private List<SysMenu> children = new ArrayList<>();
}

数据层

这里使用手写sql代码

Mapper 接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
* <p>
* 菜单权限表 Mapper 接口
* </p>
*
* @author Ray
* @since 2018-11-27
*/
public interface SysMenuMapper extends BaseMapper<SysMenu> {

/**
* 查询系统正常显示菜单(不含按钮)
* @return 菜单列表
*/
public List<SysMenu> selectMenuNormalAll();

/**
* 查询系统部分显示菜单(不含按钮)
* @return 菜单列表
*/
public List<SysMenu> selecMenusByUserId(Integer userId);
}

说明

  1. selectMenuNormalAll(): 查询所有菜单
  2. selecMenusByUserId(Integer userId): 根据userId查询菜单

Mapper XML

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ray.system.mapper.SysMenuMapper">

<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.ray.system.entity.SysMenu">
<id column="menu_id" property="menuId" />
<result column="menu_name" property="menuName" />
<result column="parent_id" property="parentId" />
<result column="order_num" property="orderNum" />
<result column="url" property="url" />
<result column="menu_type" property="menuType" />
<result column="visible" property="visible" />
<result column="perms" property="perms" />
<result column="icon" property="icon" />
<result column="create_time" property="createTime" />
<result column="update_time" property="updateTime" />
<result column="remark" property="remark" />
</resultMap>

<!-- 通用查询结果列 -->
<sql id="Base_Column_List">
menu_id, menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_time, update_time, remark
</sql>

<select id="selectMenuNormalAll" resultMap="BaseResultMap">
select distinct m.menu_id, m.parent_id, m.menu_name, m.url, m.menu_type, m.icon, m.order_num, m.create_time
from sys_menu m
where m.menu_type in ('M', 'C') and m.visible = 0
order by m.order_num
</select>

<select id="selecMenusByUserId" parameterType="integer" resultMap="BaseResultMap">
select distinct m.menu_id, m.parent_id, m.menu_name, m.url, m.menu_type, m.icon, m.order_num, m.create_time
from sys_menu m
left join sys_role_menu srm on m.menu_id = srm.m_id
left join sys_user_role sur on srm.r_id = sur.r_id
left join sys_role r on sur.r_id = r.role_id
where sur.u_id = #{userId} and m.menu_type in ('M', 'C') and m.visible = 0 AND r.available = 0
order by m.order_num
</select>
</mapper>

说明

在表中,可能会包含重复值。这并不成问题,不过,有时您也许希望仅仅列出不同(distinct)的值。
关键词 DISTINCT 用于返回唯一不同的值。

服务层

接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
* <p>
* 菜单权限表 服务类
* </p>
*
* @author Ray
* @since 2018-11-27
*/
public interface SysMenuService extends IService<SysMenu> {

/**
* 根据用户ID查询菜单
* @return 菜单列表
*/
public List<SysMenu> selectMenusByUser(SysUser sysUser);
}

实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
package com.ray.system.service.impl;

import com.ray.system.entity.SysMenu;
import com.ray.system.entity.SysUser;
import com.ray.system.mapper.SysMenuMapper;
import com.ray.system.service.SysMenuService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

/**
* <p>
* 菜单权限表 服务实现类
* </p>
*
* @author Ray
* @since 2018-11-27
*/
@Service
public class SysMenuServiceImpl extends ServiceImpl<SysMenuMapper, SysMenu> implements SysMenuService {

@Autowired
private SysMenuMapper sysMenuMapper;

/**
* 显示所有菜单
*/
@Override
public List<SysMenu> selectMenusByUser(SysUser sysUser) {

List<SysMenu> menus = new LinkedList<>();

// 管理员显示所有菜单信息
if (sysUser.isAdmin()) {
menus = sysMenuMapper.selectMenuNormalAll();
} else {
menus = sysMenuMapper.selecMenusByUserId(sysUser.getUserId());
}

return getChildPerms(menus, 0);
}

/**
* 根据父节点的ID获取所有子节点
*
* @param list 分类表
* @param parentId 传入的父节点ID
*/
public List<SysMenu> getChildPerms(List<SysMenu> list, int parentId) {
List<SysMenu> returnList = new ArrayList<>();
for (Iterator<SysMenu> iterator = list.iterator(); iterator.hasNext();) {
SysMenu t = iterator.next();
if (t.getParentId() == parentId) {
recursionFn(list, t);
returnList.add(t);
}
}
return returnList;
}

/**
* 递归列表
*/
private void recursionFn(List<SysMenu> list, SysMenu sysMenu) {
// 得到子节点列表
List<SysMenu> childList = getChildList(list, sysMenu);
sysMenu.setChildren(childList);
for (SysMenu tChild :
childList) {
if (hasChild(list, tChild)) {
// 判断是否有子节点
Iterator<SysMenu> it = childList.iterator();
while (it.hasNext()) {
SysMenu n = it.next();
recursionFn(list, n);
}
}
}
}

/**
* 得到子节点列表
*/
private List<SysMenu> getChildList(List<SysMenu> list, SysMenu sysMenu) {
List<SysMenu> sysMenus = new ArrayList<>();
Iterator<SysMenu> it = list.iterator();
while (it.hasNext()) {
SysMenu n = it.next();
if (n.getParentId().longValue() == sysMenu.getMenuId().longValue()) {
sysMenus.add(n);
}
}
return sysMenus;
}

/**
* 判断是否有子节点
*/
private boolean hasChild(List<SysMenu> list, SysMenu sysMenu) {
return getChildList(list, sysMenu).size() > 0 ? true : false;
}
}

页面

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<!-- 侧边菜单 -->
<div class="layui-side layui-side-menu">
<div class="layui-side-scroll">
<div class="layui-logo" lay-href="/system/main">
<span><b>Ray</b> 后台管理系统</span>
</div>

<ul class="layui-nav layui-nav-tree" lay-shrink="all" id="LAY-system-side-menu">

<li class="layui-nav-item layui-nav-itemed">
<a href="javascript:;">
<i class="layui-icon layui-icon-home"></i>
<cite>主页</cite>
<span class="layui-nav-more"></span>
</a>
<dl class="layui-nav-child">
<dd data-name="console" class="layui-this">
<a lay-href="/system/main">了解Ray</a>
</dd>
</dl>
</li>

<li class="layui-nav-item" th:each="menu : ${menus}">
<a href="javascript:;">
<i class="layui-icon layui-icon-home" th:class="${menu.icon}"></i>
<cite th:text="${menu.menuName}">一级菜单</cite>
<span class="layui-nav-more"></span>
</a>

<dl class="layui-nav-child layui-nav-itemed" th:each="cmenu : ${menu.children}">
<dd>
<a th:if="${#lists.isEmpty(cmenu.children)}" th:utext="${cmenu.menuName}" th:lay-href="${cmenu.url}">二级菜单</a>
<a th:if="${not #lists.isEmpty(cmenu.children)}" href="javascript:;">[[${cmenu.menuName}]]</a>
<dl th:if="${not #lists.isEmpty(cmenu.children)}" class="layui-nav-child">
<dd th:each="emenu : ${cmenu.children}">
<a th:text="${emenu.menuName}" th:lay-href="${emenu.url}">三级菜单</a>
</dd>
</dl>
</dd>
</dl>
</li>

</ul>
</div>
</div>