ToB企服应用市场:ToB评测及商务社交产业平台
标题:
MySQL-递归查询
[打印本页]
作者:
水军大提督
时间:
2024-10-8 19:57
标题:
MySQL-递归查询
背景
相信各人在平时开辟的时候都遇见过以下这种树形数据
这种树形数据怎样落库应该这里就不赘述了
核心就是使用额外一个字段parent_id保存父亲节点的id，如下图所示
这里的classpath指的是当前节点的路径，后续说明其作用
现有需求如下：
1、查询指定id的分类节点的全部
子节点
2、查询指定id的分类节点的全部
父节点
3、查询整棵分类树，可指定
最大层级
通例操作
通例操作就是直接在步伐层面控制递归，下面根据需求一 一演示代码。
PS：底子工程代码就不演示了，工程项目代码在品评区链接中获取
查询指定id的分类节点的全部子节点
NormalController
/**
* 返回指定nodeId的节点信息，包括所有孩子节点
* @param nodeId
* @return
*/
@GetMapping("/childNodes/{nodeId}")
public CategoryVO childNodes(@PathVariable("nodeId") Integer nodeId){
return categoryService.normalChildNodes(nodeId);
}
复制代码
CategoryServiceImpl
@Override
public CategoryVO normalChildNodes(Integer nodeId) {
// 查询当前节点信息
Category category = getById(nodeId);
return assembleChildren(category);
}
private CategoryVO assembleChildren(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 = new ArrayList<>();
for (Category child : children) {
// 组装每一个孩子节点
CategoryVO cv = assembleChildren(child);
// 将其加入到当前层的孩子节点集合中
childrenVOs.add(cv);
}
categoryVO.setChildren(childrenVOs);
return categoryVO;
}
private List<Category> getChildren(int nodeId) {
// 如果不存在父亲节点为nodeId的，则说明nodeId并不存在子节点
return lambdaQuery().eq(Category::getParentId,nodeId).list();
}
复制代码
查询id为6的分类信息
查询指定id的分类节点的全部父节点
NormalController
/**
* 返回指定nodeId的节点父级集合，按照从下到上的顺序
* @param nodeId
* @return
*/
@GetMapping("/parentNodes/{nodeId}")
public List<Category> parentNodes(@PathVariable("nodeId") Integer nodeId){
return categoryService.normalParentNodes(nodeId);
}
复制代码
CategoryServiceImpl
@Override
public List<Category> normalParentNodes(Integer nodeId) {
Category category = getById(nodeId);
// 找到其所有的父亲节点信息，即根据category的parentId一直查，直到查不到
List<Category> parentCategories = new ArrayList<>();
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")
public List<CategoryVO> treeCategory(@RequestParam(value = "maxLevel",required = false) Integer maxLevel){
return categoryService.normalTreeCategory(maxLevel);
}
复制代码
CategoryServiceImpl
@Override
public List<CategoryVO> normalTreeCategory(Integer maxLevel) {
// 虚拟根节点
CategoryVO root = new CategoryVO();
root.setId(-1);
root.setName("ROOT");
root.setClasspath("/");
// 队列，为了控制层级的
Queue<CategoryVO> queue = new LinkedList<>();
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 = new ArrayList<>();
// 构建孩子节点
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 = 1
union ALL
-- 递归步：关联查询
select c.id,c.parent_id,c.name
from category c inner join r
-- r作为父表，c作为子表，所以查询条件是c的parent_id=r.id
where 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 = 12
union ALL
-- 递归步
select c.id,c.parent_id,c.name
from category c inner join 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}")
public CategoryVO childNodes(@PathVariable("nodeId") Integer nodeId){
return categoryService.advancedChildNodes(nodeId);
}
复制代码
CategoryServiceImpl
@Override
public CategoryVO advancedChildNodes(Integer nodeId) {
List<Category> categories = categoryMapper.advancedChildNodes(nodeId);
List<CategoryVO> assemble = assemble(categories);
// 这里一定是第一个，因为categories集合中的是id为nodeId和其子分类的信息，结果assemble组装后，只会存在一个根节点
return assemble.get(0);
}
// 组装categories
private List<CategoryVO> assemble(List<Category> categories){
// 组装categories
CategoryVO root = new CategoryVO();
root.setId(-1);
root.setChildren(new ArrayList<>());
Map<Integer,CategoryVO> categoryMap = new HashMap<>();
categoryMap.put(-1, root);
for (Category category : categories) {
CategoryVO categoryVO = BeanUtil.copyProperties(category, CategoryVO.class);
categoryVO.setChildren(new ArrayList<>());
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
<select id="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子查询的写法，一笔带过
<resultMap id="BaseResultMap" type="com.example.mysql8recursive.entity.Category">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="parentId" column="parent_id"/>
<result property="classpath" column="classpath"/>
</resultMap>
<resultMap id="CategoryVOResultMap" type="com.example.mysql8recursive.vo.CategoryVO" extends="BaseResultMap">
<collection property="children"
column="id"
ofType="com.example.mysql8recursive.vo.CategoryVO"
javaType="java.util.ArrayList"
select="advancedChildNodes"
>
</collection>
</resultMap>
<select id="advancedChildNodes" resultMap="CategoryVOResultMap">
select * from category where parent_id = #{id}
</select>
复制代码
查询指定id的分类节点的全部
父节点
AdvancedController
/**
* 返回指定nodeId的节点父级集合，按照从下到上的顺序
* @param nodeId
* @return
*/
@GetMapping("/parentNodes/{nodeId}")
public List<Category> parentNodes(@PathVariable("nodeId") Integer nodeId){
return categoryService.advancedParentNodes(nodeId);
}
复制代码
CategorySericeImpl
@Override
public List<Category> advancedParentNodes(Integer nodeId) {
return categoryMapper.advancedParentNodes(nodeId);
}
复制代码
CategoryMapper
<select id="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")
public List<CategoryVO> treeCategory(){
return categoryService.advancedTreeCategory();
}
复制代码
CategoryServiceImpl
@Override
public List<CategoryVO> advancedTreeCategory() {
return assemble(list());
}
复制代码
查询整棵分类树
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)
Powered by Discuz! X3.4
