如何查询MySQL存储的树形结构,层次结构

表定义如下

 如果我们需要在表中查询这个树状结构,通过SQL语句,有两种查询方法:

1.通过inner自连接查询,适用于简单的结构

SELECT
    * 
FROM
    course_category AS one
    INNER JOIN course_category AS two ON two.parentid = one.id 
WHERE
    one.parentid = '1' 
ORDER BY
    one.orderby,
    two.orderby

2.通过CTE(Common Table Expression)递归查询:适用于灵活的表结构:需要多层递归

首先举一个递归sql的例子

WITH recursive t1 as ( # with关键字用于定义CTE,,recursive关键字表示这是一个递归CTE
    SELECT 1 as n # 递归的基础部分,
    union all # 用于将两个select的结果集合并,与union不同的是他不会去重
    SELECT n+1 FROM t1 where n<5
)
select * from t1

关于性能:MySQL规定默认递归次数不能超过1000次,并且可以通过设置cte_max_recursion_depth参数来增加递归深度,通过cte_max_recursion_time限制执行时间。

MySQL递归是在存储过程中执行若干次sql语句,java程序在调用方法时会与数据库仅建立一次链接来执行递归操作。因此控制好递归次数不会影响性能

什么是存储过程:一组为了完成特定功能的SQL语句集合

# 从根节点向下递归
WITH recursive t1 as (
    SELECT * FROM course_category WHERE id='1' #取得了树根
    union all
    SELECT course_category.* FROM course_category INNER JOIN t1 on t1.id=course_category.parentid 
)
SELECT * FROM t1 ORDER BY id

# 从叶子节点向上递归
WITH recursive t1 as ( SELECT * FROM course_category WHERE id='1-1-1' #取得了树根 union all SELECT course_category.* FROM course_category INNER JOIN t1 on t1.parentid=course_category.id ) SELECT * FROM t1 ORDER BY id

如何在java中使用:

首先除了po以外,还需要一个dto,dto需要extends po,同时内包含一个List<dto>childTreeNodes属性

service层方法:获取当前节点的所有子节点后,通过遍历所有节点,依次构造树形关系

public List<CourseCategoryTreeDto> queryTreeNodes(String id) {
List<CourseCategoryTreeDto> courseCategoryTreeDtoList = mapper.selectTreeNodes(id);

// 为了方便获取结点,封装到map
Map<String, CourseCategoryTreeDto> map = courseCategoryTreeDtoList
.stream()
.filter(item -> !id.equals(item.getId()))
.collect(Collectors.toMap(key -> key.getId(), value -> value, (key1, key2) -> key2));

List<CourseCategoryTreeDto> list = new ArrayList<>();
// 遍历所有节点,如果是当前节点的下一级节点,加入到list中;如果不是,则找到他的父节点并在list中成为父节点的子节点
courseCategoryTreeDtoList
.stream()
.filter(item -> !id.equals(item.getId()))
.forEach(node -> {
// 如果是当前节点的下一级节点
if (node.getParentid().equals(id)) {
list.add(node);
}
// 找到当前节点相应的父节点
CourseCategoryTreeDto courseCategoryTreeParent = map.get(node.getParentid());
// 父节点可能有不存在的情况,比如根节点已经在上面过滤掉了,因此一级子节点的父节点是空的
if (courseCategoryTreeParent != null) {
// 如果父节点属性为null,需要new一下集合
if (courseCategoryTreeParent.getChildrenTreeNodes() == null) {
courseCategoryTreeParent.setChildrenTreeNodes(new ArrayList<>());
}
// 将当前节点加入到父节点的子节点中
courseCategoryTreeParent.getChildrenTreeNodes().add(node);
}
});
return list;
}

 

posted @ 2024-06-13 18:57  天启A  阅读(1878)  评论(0)    收藏  举报