查询树形结构的数据
大约 5 分钟
查询动态路由
菜单vo
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
public class MenuVo {
/**
* 子菜单
*/
private List<MenuVo> children;
/**
* 菜单ID
*/
private Long id;
/**
* 菜单名称
*/
private String menuName;
/**
* 父菜单ID
*/
private Long parentId;
/**
* 显示顺序
*/
private Integer orderNum;
/**
* 路由地址
*/
private String path;
/**
* 组件路径
*/
private String component;
/**
* 是否为外链（0是 1否）
*/
private Integer isFrame;
/**
* 菜单类型（M目录 C菜单 F按钮）
*/
private String menuType;
/**
* 菜单状态（0显示 1隐藏）
*/
private String visible;
/**
* 菜单状态（0正常 1停用）
*/
private String status;
/**
* 权限标识
*/
private String perms;
/**
* 菜单图标
*/
private String icon;
/**
* 创建时间
*/
private Date createTime;
}
controller
@GetMapping("/getRouters")
public ResponseResult<RoutersVo> getRouters(){
// 获取当前登录用户
Long userId = SecurityUtils.getUserId();
// 查询menu，结果是tree的形式
List<MenuVo> menus = menuService.selectRouterMenuTreeByUserId(userId);
// 封装数据返回
return ResponseResult.okResult(new RoutersVo(menus));
}
返回数据：
{
"menus": [
{
"children": [],
"component": "content/article/write/index",
"createTime": "2022-01-08 03:39:58",
"icon": "build",
"id": "2023",
"isFrame": 1,
"menuName": "写博文",
"menuType": "C",
"orderNum": 0,
"parentId": "0",
"path": "write",
"perms": "content:article:writer",
"status": "0",
"visible": "0"
},
{
"children": [
{
"children": [],
"component": "system/user/index",
"createTime": "2021-11-12 10:46:19",
"icon": "user",
"id": "100",
"isFrame": 1,
"menuName": "用户管理",
"menuType": "C",
"orderNum": 1,
"parentId": "1",
"path": "user",
"perms": "system:user:list",
"status": "0",
"visible": "0"
},
{
"children": [],
"component": "system/role/index",
"createTime": "2021-11-12 10:46:19",
"icon": "peoples",
"id": "101",
"isFrame": 1,
"menuName": "角色管理",
"menuType": "C",
"orderNum": 2,
"parentId": "1",
"path": "role",
"perms": "system:role:list",
"status": "0",
"visible": "0"
},
{
"children": [],
"component": "system/menu/index",
"createTime": "2021-11-12 10:46:19",
"icon": "tree-table",
"id": "102",
"isFrame": 1,
"menuName": "菜单管理",
"menuType": "C",
"orderNum": 3,
"parentId": "1",
"path": "menu",
"perms": "system:menu:list",
"status": "0",
"visible": "0"
}
],
"createTime": "2021-11-12 10:46:19",
"icon": "system",
"id": "1",
"isFrame": 1,
"menuName": "系统管理",
"menuType": "M",
"orderNum": 1,
"parentId": "0",
"path": "system",
"perms": "",
"status": "0",
"visible": "0"
},
{
"children": [
{
"children": [],
"component": "content/article/index",
"createTime": "2022-01-08 02:53:10",
"icon": "build",
"id": "2019",
"isFrame": 1,
"menuName": "文章管理",
"menuType": "C",
"orderNum": 0,
"parentId": "2017",
"path": "article",
"perms": "content:article:list",
"status": "0",
"visible": "0"
},
{
"children": [],
"component": "content/category/index",
"createTime": "2022-01-08 02:51:45",
"icon": "example",
"id": "2018",
"isFrame": 1,
"menuName": "分类管理",
"menuType": "C",
"orderNum": 1,
"parentId": "2017",
"path": "category",
"perms": "content:category:list",
"status": "0",
"visible": "0"
},
{
"children": [],
"component": "content/link/index",
"createTime": "2022-01-08 02:56:50",
"icon": "404",
"id": "2022",
"isFrame": 1,
"menuName": "友链管理",
"menuType": "C",
"orderNum": 4,
"parentId": "2017",
"path": "link",
"perms": "content:link:list",
"status": "0",
"visible": "0"
},
{
"children": [],
"component": "content/tag/index",
"createTime": "2022-01-08 02:55:37",
"icon": "button",
"id": "2021",
"isFrame": 1,
"menuName": "标签管理",
"menuType": "C",
"orderNum": 6,
"parentId": "2017",
"path": "tag",
"perms": "content:tag:index",
"status": "0",
"visible": "0"
}
],
"createTime": "2022-01-08 02:44:38",
"icon": "table",
"id": "2017",
"isFrame": 1,
"menuName": "内容管理",
"menuType": "M",
"orderNum": 4,
"parentId": "0",
"path": "content",
"perms": "",
"status": "0",
"visible": "0"
},
{
"children": [
{
"children": [],
"component": "405",
"createTime": "2022-12-06 11:25:42",
"icon": "404",
"id": "2041",
"isFrame": 1,
"menuName": "405",
"menuType": "C",
"orderNum": 0,
"parentId": "2039",
"path": "405",
"perms": "405",
"status": "0",
"visible": "0"
},
{
"children": [],
"component": "buttton",
"createTime": "2022-12-06 11:27:03",
"icon": "button",
"id": "2042",
"isFrame": 1,
"menuName": "buttton",
"menuType": "C",
"orderNum": 1,
"parentId": "2039",
"path": "buttton",
"perms": "buttton",
"status": "0",
"visible": "0"
}
],
"createTime": "2022-12-06 11:24:34",
"icon": "bug",
"id": "2039",
"isFrame": 1,
"menuName": "bug",
"menuType": "M",
"orderNum": 5,
"parentId": "0",
"path": "bug",
"perms": "",
"status": "0",
"visible": "0"
}
]
}
service实现
接口定义：
/**
* 根据用户id查询路由菜单
* @param userId 用户id
* @return 菜单(tree的形式)
*/
List<MenuVo> selectRouterMenuTreeByUserId(Long userId);
接口实现：
- 先查询出所有的菜单，返回一个集合。
- 根据父id字段进行子菜单的设置，并且子菜单可能还有子菜单，也无法确定有多少层级，需要使用递归调用。
@Override
public List<MenuVo> selectRouterMenuTreeByUserId(Long userId) {
MenuMapper menuMapper = getBaseMapper();
List<MenuVo> menus = null;
// 判断是否是管理员
if (SecurityUtils.isAdmin()){
// 是,返回所有符合要求的Menu
menus = menuMapper.selectAllRouterMenu();
} else {
// 否,获取当前用户所具有的Menu
menus = menuMapper.selectRouterMenuTreeByUserId(userId);
}
// 构建tree: 先找出第一层的菜单,然后找它们的子菜单, 设置到children中
List<MenuVo> menuTree = builderMenuTree(menus, 0L);
return menuTree;
}
查询菜单的sql：
<!--查询所有的路由菜单-->
<select id="selectAllRouterMenu" resultType="com.sanfen.domain.vo.MenuVo">
SELECT
DISTINCT
id,
parent_id,
menu_name,
path,
component,
visible,
status,
IFNULL(perms,'') AS perms,
is_frame,
menu_type,
icon,
order_num,
create_time
FROM sys_menu
WHERE
menu_type IN ('C','M') AND
status = 0 AND
del_flag = 0
ORDER By parent_id,order_num
</select>
<!--根据用户id查询路由菜单-->
<select id="selectRouterMenuTreeByUserId" resultType="com.sanfen.domain.vo.MenuVo">
SELECT
DISTINCT
m.id,
m.parent_id,
m.menu_name,
m.path,
m.component,
m.visible,
m.status,
IFNULL(m.perms,'') AS perms,
m.is_frame,
m.menu_type,
m.icon,
m.order_num,
m.create_time
FROM sys_user_role ur
LEFT JOIN sys_role_menu rm on ur.role_id = rm.role_id
LEFT JOIN sys_menu m on rm.menu_id = m.id
WHERE
ur.user_id = #{userId} AND
m.menu_type IN ('C','M') AND
m.`status` = 0 AND
m.del_flag = 0
ORDER By m.parent_id,m.order_num
</select>
构造建树形菜单的关键代码：builderMenuTree()。这里传入的父菜单parentId是根节点，也就是根菜单。我们是从树根自上而下构建菜单树（也可以自下而上构建，原理差不多）。
/**
* 根据查询到的Menu构建tree
* @param menus 菜单集合
* @param parentId 菜单的父id
* @return menuTree
*/
private List<MenuVo> builderMenuTree(List<MenuVo> menus, Long parentId) {
List<MenuVo> menuTree = menus.stream()
.filter(menu -> menu.getParentId().equals(parentId))
.map(menu -> menu.setChildren(getChildren(menu, menus)))
.collect(Collectors.toList());
return menuTree;
}
这里传入所有的菜单集合和父菜单的id，找出父菜单的孩子，并进行属性设置。通过一个方法getChildren获取传入菜单的孩子。
/**
* 查询指定菜单menu在menus中的子菜单集合
* @param menu 指定菜单
* @param menus 菜单集合
* @return 传入菜单的子菜单集合
*/
private List<MenuVo> getChildren(MenuVo menu, List<MenuVo> menus) {
List<MenuVo> children = menus.stream()
.filter(m -> m.getParentId().equals(menu.getId()))
// 子菜单还有子菜单: 递归调用
.map(m -> m.setChildren(getChildren(m, menus)))
.collect(Collectors.toList());
return children;
}