1.背景说明
前后端权限管理系统,用户登录成功之后给前端返回可以打开的菜单信息,业务逻辑需要只进行三级菜单展示(模块名--菜单名--子菜单名),类似于下面这种形式:
数据表结构就是最常见的用户--角色--菜单权限结构,此处只展示结构,字段不进行展示
前后端响应参数协议(篇幅原因只展示部分):
相关实体类:
@ApiModel("用户菜单权限")
public class ManageUserMenu implements Serializable {
private static final long serialVersionUID = 6700435120558712346L;
@ApiModelProperty(value = "菜单id",dataType = "long")
private Long menuId;
@ApiModelProperty(value = "菜单名称",dataType = "string")
private String menuName;
@ApiModelProperty(value = "父菜单ID",dataType = "long")
private Long parentId;
@ApiModelProperty(value = "菜单URL",dataType = "string")
private String url;
@ApiModelProperty(value = "菜单状态(1显示 2隐藏)",dataType = "int")
private int isShow;
@ApiModelProperty(value = "权限字符串",dataType = "string")
private String perms;
@ApiModelProperty(value = "文件名",dataType = "string")
private String name;
@ApiModelProperty(value = "组件名",dataType = "string")
private String component="Layout";
@ApiModelProperty(value = "菜单子内容",dataType = "MenuMeta.Class")
private MenuMeta meta=new MenuMeta();
@ApiModelProperty(value = "用户菜单子信息",dataType = "list")
private List<ManageUserMenu> manageMenuList;
// 省略get/set
}
菜单中mate:
@ApiModel("菜单子内容")
public class MenuMeta implements Serializable {
private static final long serialVersionUID = -3825960451269915179L;
@ApiModelProperty(value = "菜单标题",dataType = "string")
private String title;
// 省略get/set
}
2.思路与问题处理说明
1.三级菜单查询,使用collection标签进行嵌套;菜单表中各菜单是通过parentId进行关联的,首先要查父类id为0的菜单,然后根据菜单id查询是否有父类id为此菜单id的菜单集合,如果有点绕可以直接看sql;封装的<sql id="findUserMenuList">就是查询所有用户拥有所有的菜单信息
2.对于ManageUserMenu对象中有MenuMeta对象作为属性,如何对该属性进行赋值也是需要注意.可以在result标签中用对象.属性的方式进行
3.子查询中传参注意,注意传递两个参数的写法:column="{userId=user_id,menuId=menu_id}".
实现方案(mybatis):
dao接口:
public interface ManageUserMapper {
// 根据用户id查询拥有的菜单权限---用户登陆场成功之后前端显示用
List<ManageUserMenu> findUserMenuByUserId(Long userId);
}
配置文件:
<!--一级菜单参数组装-->
<resultMap id="UserMenuMap" type="com.kawaxiaoyu.manage.management.api.user.vo.ManageUserMenu">
<id property="menuId" column="menu_id"></id>
<result property="menuName" column="menu_name"></result>
<result property="meta.title" column="menu_name"></result>
<result property="parentId" column="parent_id"></result>
<result property="perms" column="perms"></result>
<collection property="manageMenuList" ofType="com.kawaxiaoyu.manage.management.api.user.vo.ManageUserMenu"
column="{userId=user_id,menuId=menu_id}" select="findTwoMenuList" >
<id property="menuId" column="menu_id"></id>
<result property="menuName" column="menu_name"></result>
<result property="parentId" column="parent_id"></result>
<result property="perms" column="perms"></result>
<result property="meta.title" column="menu_name"></result>
</collection>
</resultMap>
<!--二级菜单参数组装-->
<resultMap id="TwoUserMenuMap" type="com.kawaxiaoyu.manage.management.api.user.vo.ManageUserMenu">
<id property="menuId" column="menu_id"></id>
<result property="menuName" column="menu_name"></result>
<result property="parentId" column="parent_id"></result>
<result property="perms" column="perms"></result>
<result property="meta.title" column="menu_name"></result>
<collection property="manageMenuList" ofType="com.kawaxiaoyu.manage.management.api.user.vo.ManageUserMenu"
column="{userId=user_id,menuId=menu_id}" select="findThreeMenuList">
<id property="menuId" column="menu_id"></id>
<result property="menuName" column="menu_name"></result>
<result property="parentId" column="parent_id"></result>
<result property="perms" column="perms"></result>
<result property="meta.title" column="menu_name"></result>
</collection>
</resultMap>
<!--三级菜单-->
<resultMap id="ThreeUserMenuMap" type="com.kawaxiaoyu.manage.management.api.user.vo.ManageUserMenu">
<id property="menuId" column="menu_id"></id>
<result property="menuName" column="menu_name"></result>
<result property="parentId" column="parent_id"></result>
<result property="perms" column="perms"></result>
<result property="meta.title" column="menu_name"></result>
</resultMap>
<sql id="findUserMenuList">
SELECT manage_menu.`menu_id`,manage_menu.`menu_name`,manage_menu.`perms`,manage_user_role.`user_id`,manage_menu.`parent_id` FROM manage_menu
LEFT JOIN manage_role_menu ON manage_menu.`menu_id`=manage_role_menu.`menu_id`
LEFT JOIN manage_user_role ON manage_user_role.`role_id`=manage_role_menu.`role_id`
LEFT JOIN manage_user ON manage_user.`user_id`=manage_user_role.`user_id`
</sql>
<!--查询一级菜单,根据父类id为0查询-->
<select id="findUserMenuByUserId" resultMap="UserMenuMap" >
<include refid="findUserMenuList" />
<where>
manage_user_role.user_id=#{userId} and manage_menu.`parent_id`=0
</where>
</select>
<!--查询二级菜单-->
<select id="findTwoMenuList" resultMap="TwoUserMenuMap" >
<include refid="findUserMenuList" />
<where>
manage_user_role.user_id=#{userId} and manage_menu.`parent_id`=#{menuId}
</where>
</select>
<!--查询三级子菜单-->
<select id="findThreeMenuList" resultMap="ThreeUserMenuMap" >
<include refid="findUserMenuList" />
<where>
manage_user_role.user_id=#{userId} and manage_menu.`parent_id`=#{menuId}
</where>
</select>
返回详情参数:
{
"success": true,
"msg": "success",
"data": [
{
"menuId": 4,
"menuName": "场馆管理",
"parentId": 0,
"url": null,
"isShow": 0,
"perms": "studio:studio:studio",
"name": null,
"component": "Layout",
"meta": {
"title": "场馆管理"
},
"manageMenuList": [
{
"menuId": 5,
"menuName": "员工教练管理",
"parentId": 4,
"url": null,
"isShow": 0,
"perms": "studio:employee:list",
"name": null,
"component": "Layout",
"meta": {
"title": "员工教练管理"
},
"manageMenuList": [
{
"menuId": 6,
"menuName": "员工详情查看",
"parentId": 5,
"url": null,
"isShow": 0,
"perms": "studio:employee:detail",
"name": null,
"component": "Layout",
"meta": {
"title": "员工详情查看"
},
"manageMenuList": null
},
{
"menuId": 7,
"menuName": "员工详情编辑",
"parentId": 5,
"url": null,
"isShow": 0,
"perms": "studio:employee:edit",
"name": null,
"component": "Layout",
"meta": {
"title": "员工详情编辑"
},
"manageMenuList": null
},
{
"menuId": 8,
"menuName": "员工详情删除",
"parentId": 5,
"url": null,
"isShow": 0,
"perms": "studio:employee:delete",
"name": null,
"component": "Layout",
"meta": {
"title": "员工详情删除"
},
"manageMenuList": null
},
{
"menuId": 9,
"menuName": "员工详情新增",
"parentId": 5,
"url": null,
"isShow": 0,
"perms": "studio:employee:add",
"name": null,
"component": "Layout",
"meta": {
"title": "员工详情新增"
},
"manageMenuList": null
}
]
},
{
"menuId": 10,
"menuName": "公告管理",
"parentId": 4,
"url": null,
"isShow": 0,
"perms": "studio:notice:list",
"name": null,
"component": "Layout",
"meta": {
"title": "公告管理"
},
"manageMenuList": [
{
"menuId": 11,
"menuName": "公告信息编辑",
"parentId": 10,
"url": null,
"isShow": 0,
"perms": "studio:notice:edit",
"name": null,
"component": "Layout",
"meta": {
"title": "公告信息编辑"
},
"manageMenuList": null
}
]
}
]
},
{
"menuId": 12,
"menuName": "会员管理",
"parentId": 0,
"url": null,
"isShow": 0,
"perms": "member:member:member",
"name": null,
"component": "Layout",
"meta": {
"title": "会员管理"
},
"manageMenuList": [
{
"menuId": 13,
"menuName": "会员信息管理",
"parentId": 12,
"url": null,
"isShow": 0,
"perms": "member:member:list",
"name": null,
"component": "Layout",
"meta": {
"title": "会员信息管理"
},
"manageMenuList": [
{
"menuId": 14,
"menuName": "会员信息详情查看",
"parentId": 13,
"url": null,
"isShow": 0,
"perms": "member:member:detail",
"name": null,
"component": "Layout",
"meta": {
"title": "会员信息详情查看"
},
"manageMenuList": null
},
{
"menuId": 15,
"menuName": "会员信息详情编辑",
"parentId": 13,
"url": null,
"isShow": 0,
"perms": "member:member:edit",
"name": null,
"component": "Layout",
"meta": {
"title": "会员信息详情编辑"
},
"manageMenuList": null
},
{
"menuId": 16,
"menuName": "会员信息详情删除",
"parentId": 13,
"url": null,
"isShow": 0,
"perms": "member:member:delete",
"name": null,
"component": "Layout",
"meta": {
"title": "会员信息详情删除"
},
"manageMenuList": null
},
{
"menuId": 17,
"menuName": "会员信息详情新增",
"parentId": 13,
"url": null,
"isShow": 0,
"perms": "member:member:add",
"name": null,
"component": "Layout",
"meta": {
"title": "会员信息详情新增"
},
"manageMenuList": null
}
]
}
]
}
],
"time": "2021-08-27 16:21:27"
}
3.反思与总结
对于菜单嵌套或是评论类嵌套等树状结构来说,常用的方式有collection标签嵌套或是业务逻辑层进行递归调用数据库,用collection多层嵌套的方式写法可能显的比较笨重,但是性能要比业务逻辑层多次创建连接调用数据库要更高.本业务场景中只用显示三级菜单所以才用多个collection标签进行嵌套,如果存在不确定级数的结构可以考虑使用mysql中自定义存储函数,后续会对这种使用方式进行补充.
希望对有需要的小伙伴能有所帮助,如果有更好的方案或是不当之处还希望评论区进行留言讨论!