0
0

MySQL-递归查询

背景

相信大家在平时开发的时候都遇见过以下这种树形数据
在这里插入图片描述
这种树形数据如何落库应该这里就不赘述了

核心就是使用额外一个字段parent_id保存父亲节点的id，如下图所示
在这里插入图片描述

这里的classpath指的是当前节点的路径，后续说明其作用

现有需求如下：
1、查询指定id的分类节点的所有子节点
2、查询指定id的分类节点的所有父节点
3、查询整棵分类树，可指定最大层级

常规操作

常规操作就是直接在程序层面控制递归，下面根据需求一 一演示代码。

PS：基础工程代码就不演示了，工程项目代码在评论区链接中获取

查询指定id的分类节点的所有子节点

NormalController

/**
     * 返回指定nodeId的节点信息，包括所有孩子节点
     * @param nodeId
     * @return
     */@GetMapping("/childNodes/{nodeId}")publicCategoryVOchildNodes(@PathVariable("nodeId")Integer nodeId){return categoryService.normalChildNodes(nodeId);}

CategoryServiceImpl

@OverridepublicCategoryVOnormalChildNodes(Integer nodeId){// 查询当前节点信息Category category =getById(nodeId);returnassembleChildren(category);}privateCategoryVOassembleChildren(Category category){// 组装vo信息CategoryVO categoryVO =BeanUtil.copyProperties(category,CategoryVO.class);// 如果没有子节点了，则退出递归List<Category> children =getChildren(category.getId());if(children ==null|| children.isEmpty()){return categoryVO;}List<CategoryVO> childrenVOs =newArrayList<>();for(Category child : children){// 组装每一个孩子节点CategoryVO cv =assembleChildren(child);// 将其加入到当前层的孩子节点集合中
            childrenVOs.add(cv);}
        categoryVO.setChildren(childrenVOs);return categoryVO;}privateList<Category>getChildren(int nodeId){// 如果不存在父亲节点为nodeId的，则说明nodeId并不存在子节点returnlambdaQuery().eq(Category::getParentId,nodeId).list();}

查询id为6的分类信息
在这里插入图片描述

查询指定id的分类节点的所有父节点

NormalController

/**
     * 返回指定nodeId的节点父级集合，按照从下到上的顺序
     * @param nodeId
     * @return
     */@GetMapping("/parentNodes/{nodeId}")publicList<Category>parentNodes(@PathVariable("nodeId")Integer nodeId){return categoryService.normalParentNodes(nodeId);}

CategoryServiceImpl

@OverridepublicList<Category>normalParentNodes(Integer nodeId){Category category =getById(nodeId);// 找到其所有的父亲节点信息，即根据category的parentId一直查，直到查不到List<Category> parentCategories =newArrayList<>();Category current = category;while(true){Category parent =lambdaQuery().eq(Category::getId, current.getParentId()).one();if(parent ==null){break;}
            parentCategories.add(parent);
            current = parent;}return parentCategories;}

查询id为12的父级分类信息
在这里插入图片描述

查询整棵分类树，可指定最大层级

NormalController

/**
     * 返回整棵分类树，可设置最大层级
     * @param maxLevel
     * @return
     */@GetMapping("/treeCategory")publicList<CategoryVO>treeCategory(@RequestParam(value ="maxLevel",required =false)Integer maxLevel){return categoryService.normalTreeCategory(maxLevel);}

CategoryServiceImpl

@OverridepublicList<CategoryVO>normalTreeCategory(Integer maxLevel){// 虚拟根节点CategoryVO root =newCategoryVO();
        root.setId(-1);
        root.setName("ROOT");
        root.setClasspath("/");// 队列，为了控制层级的Queue<CategoryVO> queue =newLinkedList<>();
        queue.offer(root);int level =1;while(!queue.isEmpty()){// 到达最大层级了if(maxLevel !=null&& maxLevel == level){break;}int size = queue.size();for(int i =0; i < size; i++){CategoryVO poll = queue.poll();if(poll ==null){continue;}//得到当前层级的所有孩子节点List<Category> children =getChildren(poll.getId());// 有孩子节点if(children !=null&&!children.isEmpty()){List<CategoryVO> childrenVOs =newArrayList<>();// 构建孩子节点for(Category child : children){CategoryVO cv =BeanUtil.copyProperties(child,CategoryVO.class);
                        childrenVOs.add(cv);
                        queue.offer(cv);}// 设置孩子节点
                    poll.setChildren(childrenVOs);}}// 层级自增
            level++;}// 返回虚拟节点的孩子节点return root.getChildren();}

查询整棵分类树
在这里插入图片描述
在这里插入图片描述

MySQL8新特性

MySQL8有一个新特性就是with共用表表达式，使用这个特性就可以在MySQL层面实现递归查询。

我们先来看看从上至下的递归查询的SQL语句，查询id为1的节点的所有子节点

WITH recursive r as(-- 递归基：由此开始递归select id,parent_id,name from category where id =1unionALL-- 递归步：关联查询select c.id,c.parent_id,c.name
    from category c innerjoin r 
    -- r作为父表，c作为子表，所以查询条件是c的parent_id=r.idwhere r.id = c.parent_id
)select id,parent_id,name from r

查询结果如下图所示
在这里插入图片描述

举一反三，则查询id为12的所有父节点信息的就是从下至上的递归查询，SQL如下所示

WITH recursive r as(-- 递归基：从id为12的开始select id,parent_id,name from category where id =12unionALL-- 递归步select c.id,c.parent_id,c.name
    from category c innerjoin r 
    -- 因为是从下至上的查，所以c作为子表，r作为父表where r.parent_id = c.id
)select id,parent_id,name from r

结果如下图所示
在这里插入图片描述

查询指定id的分类节点的所有子节点

AdvancedController

/**
     * 返回指定nodeId的节点信息，包括所有孩子节点
     * @param nodeId
     * @return
     */@GetMapping("/childNodes/{nodeId}")publicCategoryVOchildNodes(@PathVariable("nodeId")Integer nodeId){return categoryService.advancedChildNodes(nodeId);}

CategoryServiceImpl

@OverridepublicCategoryVOadvancedChildNodes(Integer nodeId){List<Category> categories = categoryMapper.advancedChildNodes(nodeId);List<CategoryVO> assemble =assemble(categories);// 这里一定是第一个，因为categories集合中的是id为nodeId和其子分类的信息，结果assemble组装后，只会存在一个根节点return assemble.get(0);}// 组装categoriesprivateList<CategoryVO>assemble(List<Category> categories){// 组装categoriesCategoryVO root =newCategoryVO();
        root.setId(-1);
        root.setChildren(newArrayList<>());Map<Integer,CategoryVO> categoryMap =newHashMap<>();
        categoryMap.put(-1, root);for(Category category : categories){CategoryVO categoryVO =BeanUtil.copyProperties(category,CategoryVO.class);
            categoryVO.setChildren(newArrayList<>());
            categoryMap.put(category.getId(), categoryVO);}for(Category category : categories){// 得到自身节点CategoryVO categoryVO = categoryMap.get(category.getId());// 得到父亲节点CategoryVO parent = categoryMap.get(category.getParentId());// 没有父亲节点（此情况只会在数据库中最上层节点的父节点id不为-1的时候出现）if(parent ==null){
                root.getChildren().add(categoryVO);continue;}
            parent.getChildren().add(categoryVO);}return root.getChildren();}

CategoryMapper

<selectid="advancedChildNodes"resultType="com.example.mysql8recursive.entity.Category">
        WITH recursive r as (select id, parent_id, name,classpath
                             from category
                             where id = #{nodeId}
                             union ALL
                             select c.id, c.parent_id, c.name,c.classpath
                             from category c
                                      inner join r
                             where r.id = c.parent_id)

        select id, parent_id, name, classpath
        from r
    </select>

查询分类id为6的分类信息
在这里插入图片描述

拓展

这里其实还有另一种利用mybatis的collection子查询的写法，一笔带过

<resultMapid="BaseResultMap"type="com.example.mysql8recursive.entity.Category"><idproperty="id"column="id"/><resultproperty="name"column="name"/><resultproperty="parentId"column="parent_id"/><resultproperty="classpath"column="classpath"/></resultMap><resultMapid="CategoryVOResultMap"type="com.example.mysql8recursive.vo.CategoryVO"extends="BaseResultMap"><collectionproperty="children"column="id"ofType="com.example.mysql8recursive.vo.CategoryVO"javaType="java.util.ArrayList"select="advancedChildNodes"></collection></resultMap><selectid="advancedChildNodes"resultMap="CategoryVOResultMap">
        select * from category where parent_id = #{id}
    </select>

查询指定id的分类节点的所有父节点

AdvancedController

/**
     * 返回指定nodeId的节点父级集合，按照从下到上的顺序
     * @param nodeId
     * @return
     */@GetMapping("/parentNodes/{nodeId}")publicList<Category>parentNodes(@PathVariable("nodeId")Integer nodeId){return categoryService.advancedParentNodes(nodeId);}

CategorySericeImpl

@OverridepublicList<Category>advancedParentNodes(Integer nodeId){return categoryMapper.advancedParentNodes(nodeId);}

CategoryMapper

<selectid="advancedParentNodes"resultType="com.example.mysql8recursive.entity.Category">
        WITH recursive r as (select id, parent_id, name, classpath
                             from category
                             where id = #{nodeId}
                             union ALL
                             select c.id, c.parent_id, c.name, c.classpath
                             from category c
                                      inner join r
                             where r.parent_id = c.id)

        select id, parent_id, name, classpath
        from r
    </select>

查询分类id为12的所有父级分类信息
在这里插入图片描述

查询整棵分类树

AdvancedController

/**
     * 返回整棵分类树
     * @return
     */@GetMapping("/treeCategory")publicList<CategoryVO>treeCategory(){return categoryService.advancedTreeCategory();}

CategoryServiceImpl

@OverridepublicList<CategoryVO>advancedTreeCategory(){returnassemble(list());}

查询整棵分类树
在这里插入图片描述
在这里插入图片描述

标签： mysql 数据库
本文转载自: https://blog.csdn.net/weixin_51456741/article/details/141315581
版权归原作者 芝麻\n 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySQL-递归查询”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

ISO26262功能安全核心思想及芯片安全设计实例

gitLab配置ssh

iStore实现 SmartDNS + AdGuard Home IP优选+广告屏蔽

Kestra 开源项目实战指南

SpringBoot（9）-Dubbo+Zookeeper

docker镜像源

ray集群部署vllm的折磨

文章导航