Mybatis-关于权限树的查询优化工作

最新推荐文章于 2023-07-23 11:02:21 发布

可爱组长

最新推荐文章于 2023-07-23 11:02:21 发布

阅读量1.2k

点赞数

分类专栏：大文件处理之mybatis批量文章标签： mybatis

本文链接：https://blog.csdn.net/qq_20667511/article/details/121358484

版权

大文件处理之mybatis批量专栏收录该内容

2 篇文章 0 订阅

订阅专栏

关于权限树mybatis查询的优化

介绍权限树的结构

关于权限树，应该都了解，一张表里面，我们定义父目录的 parentId = 0,下一级目录的parendId 等于当前目录的id值，数据库表结构如下：

CREATE TABLE `menu` (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单 ID',
  `parent_id` int(11) NOT NULL,
  `menu_name` varchar(20) DEFAULT NULL COMMENT '菜单名称',
  `url` varchar(100) DEFAULT NULL COMMENT '菜单 URL',
  `perms` varchar(50) DEFAULT NULL COMMENT '权限标识符',
  `order_num` int(11) DEFAULT NULL COMMENT '排序',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `icon` varchar(32) DEFAULT NULL COMMENT '图标',
  PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

数据库中的表记录如下：
在这里插入图片描述

前端一般使用的是layui 的tree组件

返回类似于：
在这里插入图片描述

第一种实现方式：从后端的角度来说

封装的数据，也就是一个List集合，封装了List<CategoryEntity> ，而Menu中包含了一个成员变量List<CategoryEntity> children 这样的一个结构。

@Override
    public List<CategoryEntity> listWithTree() {

        //1、查询出所有分类
        List<CategoryEntity> entities = super.baseMapper.selectList(null);

        //2、组装成父子的树形结构

        //2.1)、找到所有一级分类
        List<CategoryEntity> levelMenus = entities.stream()
                .filter(e -> e.getParentCid() == 0)
                .map((menu) -> {
                    menu.setChildren(getChildrens(menu, entities));
                    return menu;
                })
                .sorted((menu, menu2) -> {
                    return (menu.getSort() == null ? 0 : menu.getSort()) - (menu2.getSort() == null ? 0 : menu2.getSort());
                })
                .collect(Collectors.toList());

        return levelMenus;
    }

    //递归查找所有菜单的子菜单
    private List<CategoryEntity> getChildrens(CategoryEntity root, List<CategoryEntity> all) {

        List<CategoryEntity> children = all.stream().filter(categoryEntity -> {
            return categoryEntity.getParentCid().equals(root.getCatId());
        }).map(categoryEntity -> {
            //1、找到子菜单(递归)
            categoryEntity.setChildren(getChildrens(categoryEntity, all));
            return categoryEntity;
        }).sorted((menu, menu2) -> {
            //2、菜单的排序
            return (menu.getSort() == null ? 0 : menu.getSort()) - (menu2.getSort() == null ? 0 : menu2.getSort());
        }).collect(Collectors.toList());

        return children;

    }

这是根据递归实现的一套，虽然说数据库和给出的代码不同，但是大意我相信都能够看懂的。先查找所有parentId = 0 的一级目录，然后递归遍历一级目录，去判断满足当前Id等于 parentId的记录，作为其子目录。

第二种实现方式：mybatis-resultMap 中 collection的实现

不需要自己循环递归去写实现，但是底层原理还是一样的。

public class Menu{
    private Integer menuId;
    private Integer parentId;
    private String menuName;
    private String url;
    private String perms;
    private Integer orderNum;
    private Date createTime;
    private Date modifyTime;

Dao

List<Menu> selectAllTree();

Mapper.xml

    <resultMap id="TreeBaseResultMap" type="xx.Menu">
        <id column="menu_id" jdbcType="INTEGER" property="menuId"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="menu_name" jdbcType="VARCHAR" property="menuName"/>
        <result column="url" jdbcType="VARCHAR" property="url"/>
        <result column="perms" jdbcType="VARCHAR" property="perms"/>
        <result column="order_num" jdbcType="INTEGER" property="orderNum"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="modify_time" jdbcType="TIMESTAMP" property="modifyTime"/>
        <result column="icon" jdbcType="VARCHAR" property="icon"/>
        <collection property="children" ofType="xx.Menu" select="selectAllTree" column="{parent_id = menu_id}"/>
    </resultMap>

 <select id="selectAllTree" resultMap="TreeBaseResultMap">
        select
        menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
        from menu
        <where>
            <choose>
                <when test="parent_id != null">
                    and parent_id = #{parent_id}
                </when>
                <otherwise>
                    and parent_id = 0
                </otherwise>
            </choose>
        </where>
        order by order_num
 </select>

这个特点在于，resultMap中，关联了 collection 标签其属性值 select = “selectAllTree”,而且参数值parent_id = menu_id。这个虽然从代码层面上来说减少了很多逻辑。但是我分析过log日志，先查询parentId=0的所有记录，然后又会分别根据当前记录 menu_id作为 parent_id 的条件，继续查询数据库。所以我感觉效率上来说不是特别高。
以下是打印的log日志。可以不看的。

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@641d8cf7] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@3c96f769] will not be managed by Spring
==>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu where parent_id = ? order by order_num 
==> Parameters: 0(Integer)
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21436d46] was not registered for synchronization because synchronization is not active
<==    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<==        Row: 1, 0, 权限管理, #, #, 0, 2018-12-02 07:51:17, 2019-05-08 20:20:05, layui-icon-picker-securityscan
<==        Row: 11, 0, 系统监控, #, #, 5, 2019-02-04 15:07:41, 2019-05-08 20:25:01, layui-icon-picker-control
<==        Row: 30, 0, 账号关联, /oauth2/index, oauth2:index, 8, 2019-05-12 21:16:23, 2019-05-26 20:40:08, layui-icon-picker-insertrowabove
<==        Row: 31, 0, DC服务, #, #, 9, 2021-02-04 16:14:48, null, layui-icon-picker-CodeSandbox
<==        Row: 35, 0, API接口权限管理, #, #, 13, 2021-02-21 17:10:29, null, layui-icon-picker-skype-fill
<==        Row: 37, 0, DC工具配置, #, #, 13, 2021-03-15 15:04:23, null, layui-icon-picker-insertrowabove
<==        Row: 39, 0, 文档管理, #, #, 15, 2021-07-07 14:43:50, 2021-07-08 10:56:02, layui-icon-picker-file-word-fill
<==      Total: 7
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@641d8cf7]
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@3c96f769] will not be managed by Spring
==>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = 0 order by order_num 
==> Parameters: 
<==    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
Creating a new SqlSession
<==        Row: 1, 0, 权限管理, #, #, 0, 2018-12-02 07:51:17, 2019-05-08 20:20:05, layui-icon-picker-securityscan
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76cc3f98] was not registered for synchronization because synchronization is not active
====>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
====> Parameters: 1(Integer)
<====    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<====        Row: 2, 1, 用户管理, /user/index, user:list, 1, 2018-12-23 19:50:25, 2019-05-06 21:20:18, layui-icon-username
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 2(Integer)
<======      Total: 0
<====        Row: 3, 1, 角色管理, /role/index, role:list, 2, 2018-12-02 07:51:18, 2019-05-06 21:20:27, layui-icon-group
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 3(Integer)
<======      Total: 0
<====        Row: 4, 1, 菜单权限, /menu/index, menu:list, 3, 2019-02-07 10:57:06, 2019-05-06 21:21:24, layui-icon-list
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 4(Integer)
<======      Total: 0
<====        Row: 27, 1, 操作权限, /operator/index, operator:list, 4, 2019-02-10 17:39:08, 2019-02-16 19:49:22, null
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 27(Integer)
<======      Total: 0
<====        Row: 28, 1, 部门管理, /dept/index, dept:list, 6, 2019-03-13 20:58:55, null, null
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 28(Integer)
<======      Total: 0
<====      Total: 5
<==        Row: 11, 0, 系统监控, #, #, 5, 2019-02-04 15:07:41, 2019-05-08 20:25:01, layui-icon-picker-control
====>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
====> Parameters: 11(Integer)
<====    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<====        Row: 12, 11, 登录日志, /log/login/index, login:log:list, 1, 2018-12-09 10:07:36, 2019-02-10 22:27:00, null
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 12(Integer)
<======      Total: 0
<====        Row: 20, 11, 在线用户, /online/index, user:online, 3, 2018-12-23 15:40:21, 2019-02-10 22:27:00, null
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 20(Integer)
<======      Total: 0
<====        Row: 19, 11, 操作日志, /log/sys/index, sys:log:list, 4, 2018-12-22 22:48:27, 2019-02-10 22:12:13, null
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 19(Integer)
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@5d6cff6b] will not be managed by Spring
==>  Preparing: insert into sys_log (username, `operation`, `time`, `method`, params, ip, create_time) values (?, ?, ?, ?, ?, ?, ?) 
==> Parameters: admin(String), 获取菜单列表(String), 92(Integer), im.zhaojun.system.controller.MenuController.getList()(String),   parentId: 0(String), 0:0:0:0:0:0:0:1(String), null
<======      Total: 0
<====        Row: 29, 11, 系统管理, /system/index, system:index, 7, 2019-04-27 23:06:08, null, null
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 29(Integer)
<======      Total: 0
<====      Total: 4
<==        Row: 30, 0, 账号关联, /oauth2/index, oauth2:index, 8, 2019-05-12 21:16:23, 2019-05-26 20:40:08, layui-icon-picker-insertrowabove
====>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
====> Parameters: 30(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76cc3f98]
<====      Total: 0
<==        Row: 31, 0, DC服务, #, #, 9, 2021-02-04 16:14:48, null, layui-icon-picker-CodeSandbox
====>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
====> Parameters: 31(Integer)
<====    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<====        Row: 32, 31, 渠道分组管理, #, #, 10, 2021-02-04 16:16:04, 2021-02-04 16:25:46, 
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 32(Integer)
<======    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<======        Row: 34, 32, 渠道分组管理接口, /qidgroupmangerutil/getgroupbygrouptypeid, qidgroupmangerutil:getgroupbygrouptypeid, 12, 2021-02-04 16:18:38, 2021-02-21 17:08:56, 
========>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
========> Parameters: 34(Integer)
<========      Total: 0
<======      Total: 1
<====      Total: 1
<==        Row: 35, 0, API接口权限管理, #, #, 13, 2021-02-21 17:10:29, null, layui-icon-picker-skype-fill
====>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
====> Parameters: 35(Integer)
<====    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<====        Row: 36, 35, admm渠道管理接口, /admm, /admm, 14, 2021-02-21 17:12:26, 2021-02-21 17:26:26, layui-icon-picker-robot
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 36(Integer)
<======      Total: 0
<====      Total: 1
<==        Row: 37, 0, DC工具配置, #, #, 13, 2021-03-15 15:04:23, null, layui-icon-picker-insertrowabove
====>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
====> Parameters: 37(Integer)
<====    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<====        Row: 38, 37, dc邮件配置, email/index, email:index, 14, 2021-03-15 15:07:29, null, layui-icon-picker-solit-cells
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 38(Integer)
<======      Total: 0
<====      Total: 1
<==        Row: 39, 0, 文档管理, #, #, 15, 2021-07-07 14:43:50, 2021-07-08 10:56:02, layui-icon-picker-file-word-fill
====>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
====> Parameters: 39(Integer)
<====    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<====        Row: 40, 39, 数仓文档, #, #, 16, 2021-07-07 14:45:13, 2021-07-07 19:47:48, layui-icon-picker-file-word-fill
======>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
======> Parameters: 40(Integer)
<======    Columns: menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon
<======        Row: 41, 40, ODS, /doc/api/ods/index, doc:api:ods:index, 17, 2021-07-07 19:50:57, 2021-07-09 16:45:59, 
========>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
========> Parameters: 41(Integer)
<========      Total: 0
<======        Row: 42, 40, DWD, #, #, 18, 2021-07-07 19:52:04, null, 
========>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
========> Parameters: 42(Integer)
<========      Total: 0
<======        Row: 43, 40, DW, #, #, 19, 2021-07-07 19:52:18, null, 
========>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
========> Parameters: 43(Integer)
<========      Total: 0
<======        Row: 44, 40, DWS, #, #, 20, 2021-07-07 19:52:26, null, 
========>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
========> Parameters: 44(Integer)
<========      Total: 0
<======        Row: 45, 40, ADS, #, #, 21, 2021-07-07 19:52:35, null, 
========>  Preparing: select menu_id, parent_id, menu_name, url, perms, order_num, create_time, modify_time, icon from menu WHERE parent_id = ? order by order_num 
========> Parameters: 45(Integer)
<========      Total: 0
<======      Total: 5
<====      Total: 1
<==      Total: 7
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@21436d46]

可爱组长

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mybatis-关于权限树的查询优化工作

关于权限树mybatis查询的优化介绍权限树的结构前端一般使用的是layui 的tree组件第一种实现方式：从后端的角度来说第二种实现方式：mybatis-resultMap 中 collection的实现介绍权限树的结构关于权限树，应该都了解，一张表里面，我们定义父目录的 parentId = 0,下一级目录的parendId 等于当前目录的id值，数据库表结构如下：CREATE TABLE `menu` ( `menu_id` int(11) NOT NULL AUTO_INCREMENT
复制链接

扫一扫

专栏目录