MySQL-递归查询

水军大提督  金牌会员 | 2024-10-8 19:57:41 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 872|帖子 872|积分 2616

背景

相信各人在平时开辟的时候都遇见过以下这种树形数据

这种树形数据怎样落库应该这里就不赘述了
核心就是使用额外一个字段parent_id保存父亲节点的id，如下图所示

   这里的classpath指的是当前节点的路径，后续说明其作用
  现有需求如下：
1、查询指定id的分类节点的全部子节点
2、查询指定id的分类节点的全部父节点
3、查询整棵分类树，可指定最大层级
通例操作

通例操作就是直接在步伐层面控制递归，下面根据需求一 一演示代码。
   PS：底子工程代码就不演示了，工程项目代码在品评区链接中获取
  查询指定id的分类节点的全部子节点

   NormalController
  2.     /**
  3.      * 返回指定nodeId的节点信息，包括所有孩子节点
  4.      * @param nodeId
  5.      * @return
  6.      */
  7.     @GetMapping("/childNodes/{nodeId}")
  8.     public CategoryVO childNodes(@PathVariable("nodeId") Integer nodeId){
  9.         return categoryService.normalChildNodes(nodeId);
  10.     }
复制代码
  CategoryServiceImpl
  1.     @Override
  2.     public CategoryVO normalChildNodes(Integer nodeId) {
  3.         // 查询当前节点信息
  4.         Category category = getById(nodeId);
  5.         return assembleChildren(category);
  6.     }
  7.     private CategoryVO assembleChildren(Category category) {
  8.         // 组装vo信息
  9.         CategoryVO categoryVO = BeanUtil.copyProperties(category, CategoryVO.class);
  10.         // 如果没有子节点了，则退出递归
  11.         List<Category> children = getChildren(category.getId());
  12.         if (children == null || children.isEmpty()) {
  13.             return categoryVO;
  14.         }
  15.         List<CategoryVO> childrenVOs = new ArrayList<>();
  16.         for (Category child : children) {
  17.             // 组装每一个孩子节点
  18.             CategoryVO cv = assembleChildren(child);
  19.             // 将其加入到当前层的孩子节点集合中
  20.             childrenVOs.add(cv);
  21.         }
  22.         categoryVO.setChildren(childrenVOs);
  23.         return categoryVO;
  24.     }
  25.     private List<Category> getChildren(int nodeId) {
  26.         // 如果不存在父亲节点为nodeId的，则说明nodeId并不存在子节点
  27.         return lambdaQuery().eq(Category::getParentId,nodeId).list();
  28.     }
复制代码
查询id为6的分类信息

查询指定id的分类节点的全部父节点

   NormalController
  1.     /**
  2.      * 返回指定nodeId的节点父级集合，按照从下到上的顺序
  3.      * @param nodeId
  4.      * @return
  5.      */
  6.     @GetMapping("/parentNodes/{nodeId}")
  7.     public List<Category> parentNodes(@PathVariable("nodeId") Integer nodeId){
  8.         return categoryService.normalParentNodes(nodeId);
  9.     }
复制代码
  CategoryServiceImpl
  1.     @Override
  2.     public List<Category> normalParentNodes(Integer nodeId) {
  3.         Category category = getById(nodeId);
  4.         // 找到其所有的父亲节点信息，即根据category的parentId一直查，直到查不到
  5.         List<Category> parentCategories = new ArrayList<>();
  6.         Category current = category;
  7.         while (true) {
  8.             Category parent = lambdaQuery().eq(Category::getId, current.getParentId()).one();
  9.             if (parent == null) {
  10.                 break;
  11.             }
  12.             parentCategories.add(parent);
  13.             current = parent;
  14.         }
  15.         return parentCategories;
  16.     }
复制代码
查询id为12的父级分类信息

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

   NormalController
  1.     /**
  2.      * 返回整棵分类树，可设置最大层级
  3.      * @param maxLevel
  4.      * @return
  5.      */
  6.     @GetMapping("/treeCategory")
  7.     public List<CategoryVO> treeCategory(@RequestParam(value = "maxLevel",required = false) Integer maxLevel){
  8.         return categoryService.normalTreeCategory(maxLevel);
  9.     }
复制代码
  CategoryServiceImpl
  1.     @Override
  2.     public List<CategoryVO> normalTreeCategory(Integer maxLevel) {
  3.         // 虚拟根节点
  4.         CategoryVO root = new CategoryVO();
  5.         root.setId(-1);
  6.         root.setName("ROOT");
  7.         root.setClasspath("/");
  8.         // 队列，为了控制层级的
  9.         Queue<CategoryVO> queue = new LinkedList<>();
  10.         queue.offer(root);
  11.         int level = 1;
  12.         while (!queue.isEmpty()) {
  13.             // 到达最大层级了
  14.             if (maxLevel != null && maxLevel == level) {
  15.                 break;
  16.             }
  17.             int size = queue.size();
  18.             for (int i = 0; i < size; i++) {
  19.                 CategoryVO poll = queue.poll();
  20.                 if (poll == null) {
  21.                     continue;
  22.                 }
  23.                 //得到当前层级的所有孩子节点
  24.                 List<Category> children = getChildren(poll.getId());
  25.                 // 有孩子节点
  26.                 if (children != null && !children.isEmpty()) {
  27.                     List<CategoryVO> childrenVOs = new ArrayList<>();
  28.                     // 构建孩子节点
  29.                     for (Category child : children) {
  30.                         CategoryVO cv = BeanUtil.copyProperties(child, CategoryVO.class);
  31.                         childrenVOs.add(cv);
  32.                         queue.offer(cv);
  33.                     }
  34.                     // 设置孩子节点
  35.                     poll.setChildren(childrenVOs);
  36.                 }
  37.             }
  38.             // 层级自增
  39.             level++;
  40.         }
  41.         // 返回虚拟节点的孩子节点
  42.         return root.getChildren();
  43.     }
复制代码
查询整棵分类树


MySQL8新特性

MySQL8有一个新特性就是with共用表表达式，使用这个特性就可以在MySQL层面实现递归查询。
我们先来看看从上至下的递归查询的SQL语句，查询id为1的节点的全部子节点
  1. WITH recursive r as (
  2.         -- 递归基：由此开始递归
  3.         select id,parent_id,name from category where id = 1
  4.         union ALL
  5.         -- 递归步：关联查询
  6.         select c.id,c.parent_id,c.name
  7.         from category c inner join r
  8.         -- r作为父表，c作为子表，所以查询条件是c的parent_id=r.id
  9.         where r.id = c.parent_id
  10. )
  11. select id,parent_id,name from r
复制代码
查询结果如下图所示

闻一知十，则查询id为12的全部父节点信息的就是从下至上的递归查询，SQL如下所示
  1. WITH recursive r as (
  2.         -- 递归基：从id为12的开始
  3.         select id,parent_id,name from category where id = 12
  4.         union ALL
  5.         -- 递归步
  6.         select c.id,c.parent_id,c.name
  7.         from category c inner join r
  8.         -- 因为是从下至上的查，所以c作为子表，r作为父表
  9.         where r.parent_id = c.id
  10. )
  11. select id,parent_id,name from r
复制代码
结果如下图所示

查询指定id的分类节点的全部子节点

   AdvancedController
  1.     /**
  2.      * 返回指定nodeId的节点信息，包括所有孩子节点
  3.      * @param nodeId
  4.      * @return
  5.      */
  6.     @GetMapping("/childNodes/{nodeId}")
  7.     public CategoryVO childNodes(@PathVariable("nodeId") Integer nodeId){
  8.         return categoryService.advancedChildNodes(nodeId);
  9.     }
复制代码
  CategoryServiceImpl
  1.     @Override
  2.     public CategoryVO advancedChildNodes(Integer nodeId) {
  3.         List<Category> categories = categoryMapper.advancedChildNodes(nodeId);
  4.         List<CategoryVO> assemble = assemble(categories);
  5.         // 这里一定是第一个，因为categories集合中的是id为nodeId和其子分类的信息，结果assemble组装后，只会存在一个根节点
  6.         return assemble.get(0);
  7.     }
  8.     // 组装categories
  9.     private List<CategoryVO> assemble(List<Category> categories){
  10.         // 组装categories
  11.         CategoryVO root = new CategoryVO();
  12.         root.setId(-1);
  13.         root.setChildren(new ArrayList<>());
  14.         Map<Integer,CategoryVO> categoryMap = new HashMap<>();
  15.         categoryMap.put(-1, root);
  16.         for (Category category : categories) {
  17.             CategoryVO categoryVO = BeanUtil.copyProperties(category, CategoryVO.class);
  18.             categoryVO.setChildren(new ArrayList<>());
  19.             categoryMap.put(category.getId(), categoryVO);
  20.         }
  21.         for (Category category : categories) {
  22.             // 得到自身节点
  23.             CategoryVO categoryVO = categoryMap.get(category.getId());
  24.             // 得到父亲节点
  25.             CategoryVO parent = categoryMap.get(category.getParentId());
  26.             // 没有父亲节点（此情况只会在数据库中最上层节点的父节点id不为-1的时候出现）
  27.             if (parent == null) {
  28.                 root.getChildren().add(categoryVO);
  29.                 continue;
  30.             }
  31.             parent.getChildren().add(categoryVO);
  32.         }
  33.         return root.getChildren();
  34.     }
复制代码
  CategoryMapper
  1.     <select id="advancedChildNodes" resultType="com.example.mysql8recursive.entity.Category">
  2.         WITH recursive r as (select id, parent_id, name,classpath
  3.                              from category
  4.                              where id = #{nodeId}
  5.                              union ALL
  6.                              select c.id, c.parent_id, c.name,c.classpath
  7.                              from category c
  8.                                       inner join r
  9.                              where r.id = c.parent_id)
  11.         select id, parent_id, name, classpath
  12.         from r
  13.     </select>
复制代码
查询分类id为6的分类信息

拓展

这里其实还有另一种利用mybatis的collection子查询的写法，一笔带过
  1.     <resultMap id="BaseResultMap" type="com.example.mysql8recursive.entity.Category">
  2.         <id property="id" column="id"/>
  3.         <result property="name" column="name"/>
  4.         <result property="parentId" column="parent_id"/>
  5.         <result property="classpath" column="classpath"/>
  6.     </resultMap>
  7.     <resultMap id="CategoryVOResultMap" type="com.example.mysql8recursive.vo.CategoryVO" extends="BaseResultMap">
  8.         <collection property="children"
  9.                     column="id"
  10.                     ofType="com.example.mysql8recursive.vo.CategoryVO"
  11.                     javaType="java.util.ArrayList"
  12.                     select="advancedChildNodes"
  13.         >
  14.         </collection>
  15.     </resultMap>
  17.     <select id="advancedChildNodes" resultMap="CategoryVOResultMap">
  18.         select * from category where parent_id = #{id}
  19.     </select>
复制代码
查询指定id的分类节点的全部父节点

   AdvancedController
  1.     /**
  2.      * 返回指定nodeId的节点父级集合，按照从下到上的顺序
  3.      * @param nodeId
  4.      * @return
  5.      */
  6.     @GetMapping("/parentNodes/{nodeId}")
  7.     public List<Category> parentNodes(@PathVariable("nodeId") Integer nodeId){
  8.         return categoryService.advancedParentNodes(nodeId);
  9.     }
复制代码
  CategorySericeImpl
  1.     @Override
  2.     public List<Category> advancedParentNodes(Integer nodeId) {
  3.         return categoryMapper.advancedParentNodes(nodeId);
  4.     }
复制代码
  CategoryMapper
  1.     <select id="advancedParentNodes" resultType="com.example.mysql8recursive.entity.Category">
  2.         WITH recursive r as (select id, parent_id, name, classpath
  3.                              from category
  4.                              where id = #{nodeId}
  5.                              union ALL
  6.                              select c.id, c.parent_id, c.name, c.classpath
  7.                              from category c
  8.                                       inner join r
  9.                              where r.parent_id = c.id)
  11.         select id, parent_id, name, classpath
  12.         from r
  13.     </select>
复制代码
查询分类id为12的全部父级分类信息

查询整棵分类树

   AdvancedController
  1.     /**
  2.      * 返回整棵分类树
  3.      * @return
  4.      */
  5.     @GetMapping("/treeCategory")
  6.     public List<CategoryVO> treeCategory(){
  7.         return categoryService.advancedTreeCategory();
  8.     }
复制代码
  CategoryServiceImpl
  1.     @Override
  2.     public List<CategoryVO> advancedTreeCategory() {
  3.         return assemble(list());
  4.     }
复制代码
查询整棵分类树



免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

水军大提督

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表