如何查询树形结构的数据
- 在遇到数据库保存的数据为树形结构，如多级分类列表或者评论等，此处以分类列表举例
我们需要向前端返回如下部分数据
[
{
"id": "1-1",
"name": "前端开发",
"label": "前端开发",
"parentid": "1",
"isShow": 1,
"orderby": 1,
"isLeaf": 0,
"childrenTreeNodes": [
{
"id": "1-1-1",
"name": "HTML/CSS",
"label": "HTML/CSS",
"parentid": "1-1",
"isShow": 1,
"orderby": 1,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-10",
"name": "其它",
"label": "其它",
"parentid": "1-1",
"isShow": 1,
"orderby": 10,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-2",
"name": "JavaScript",
"label": "JavaScript",
"parentid": "1-1",
"isShow": 1,
"orderby": 2,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-3",
"name": "jQuery",
"label": "jQuery",
"parentid": "1-1",
"isShow": 1,
"orderby": 3,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-4",
"name": "ExtJS",
"label": "ExtJS",
"parentid": "1-1",
"isShow": 1,
"orderby": 4,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-5",
"name": "AngularJS",
"label": "AngularJS",
"parentid": "1-1",
"isShow": 1,
"orderby": 5,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-6",
"name": "ReactJS",
"label": "ReactJS",
"parentid": "1-1",
"isShow": 1,
"orderby": 6,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-7",
"name": "Bootstrap",
"label": "Bootstrap",
"parentid": "1-1",
"isShow": 1,
"orderby": 7,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-8",
"name": "Node.js",
"label": "Node.js",
"parentid": "1-1",
"isShow": 1,
"orderby": 8,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-1-9",
"name": "Vue",
"label": "Vue",
"parentid": "1-1",
"isShow": 1,
"orderby": 9,
"isLeaf": 1,
"childrenTreeNodes": null
}
]
},
{
"id": "1-10",
"name": "研发管理",
"label": "研发管理",
"parentid": "1",
"isShow": 1,
"orderby": 10,
"isLeaf": 0,
"childrenTreeNodes": [
{
"id": "1-10-1",
"name": "敏捷开发",
"label": "敏捷开发",
"parentid": "1-10",
"isShow": 1,
"orderby": 1,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-10-2",
"name": "软件设计",
"label": "软件设计",
"parentid": "1-10",
"isShow": 1,
"orderby": 2,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-10-3",
"name": "软件测试",
"label": "软件测试",
"parentid": "1-10",
"isShow": 1,
"orderby": 3,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-10-4",
"name": "研发管理",
"label": "研发管理",
"parentid": "1-10",
"isShow": 1,
"orderby": 4,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-10-5",
"name": "其它",
"label": "其它",
"parentid": "1-10",
"isShow": 1,
"orderby": 5,
"isLeaf": 1,
"childrenTreeNodes": null
}
]
},
{
"id": "1-11",
"name": "系统运维",
"label": "系统运维",
"parentid": "1",
"isShow": 1,
"orderby": 11,
"isLeaf": 0,
"childrenTreeNodes": [
{
"id": "1-11-1",
"name": "Linux",
"label": "Linux",
"parentid": "1-11",
"isShow": 1,
"orderby": 1,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-10",
"name": "其它",
"label": "其它",
"parentid": "1-11",
"isShow": 1,
"orderby": 10,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-2",
"name": "Windows",
"label": "Windows",
"parentid": "1-11",
"isShow": 1,
"orderby": 2,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-3",
"name": "UNIX",
"label": "UNIX",
"parentid": "1-11",
"isShow": 1,
"orderby": 3,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-4",
"name": "Mac OS",
"label": "Mac OS",
"parentid": "1-11",
"isShow": 1,
"orderby": 4,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-5",
"name": "网络技术",
"label": "网络技术",
"parentid": "1-11",
"isShow": 1,
"orderby": 5,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-6",
"name": "路由协议",
"label": "路由协议",
"parentid": "1-11",
"isShow": 1,
"orderby": 6,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-7",
"name": "无线网络",
"label": "无线网络",
"parentid": "1-11",
"isShow": 1,
"orderby": 7,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-8",
"name": "Ngnix",
"label": "Ngnix",
"parentid": "1-11",
"isShow": 1,
"orderby": 8,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-11-9",
"name": "邮件服务器",
"label": "邮件服务器",
"parentid": "1-11",
"isShow": 1,
"orderby": 9,
"isLeaf": 1,
"childrenTreeNodes": null
}
]
},
{
"id": "1-2",
"name": "移动开发",
"label": "移动开发",
"parentid": "1",
"isShow": 1,
"orderby": 2,
"isLeaf": 0,
"childrenTreeNodes": [
{
"id": "1-2-1",
"name": "微信开发",
"label": "微信开发",
"parentid": "1-2",
"isShow": 1,
"orderby": 1,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-2-2",
"name": "iOS",
"label": "iOS",
"parentid": "1-2",
"isShow": 1,
"orderby": 2,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-2-3",
"name": "手游开发",
"label": "手游开发",
"parentid": "1-2",
"isShow": 1,
"orderby": 3,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-2-4",
"name": "Swift",
"label": "Swift",
"parentid": "1-2",
"isShow": 1,
"orderby": 4,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-2-5",
"name": "Android",
"label": "Android",
"parentid": "1-2",
"isShow": 1,
"orderby": 5,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-2-6",
"name": "ReactNative",
"label": "ReactNative",
"parentid": "1-2",
"isShow": 1,
"orderby": 6,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-2-7",
"name": "Cordova",
"label": "Cordova",
"parentid": "1-2",
"isShow": 1,
"orderby": 7,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-2-8",
"name": "其它",
"label": "其它",
"parentid": "1-2",
"isShow": 1,
"orderby": 8,
"isLeaf": 1,
"childrenTreeNodes": null
}
]
},
{
"id": "1-3",
"name": "编程开发",
"label": "编程开发",
"parentid": "1",
"isShow": 1,
"orderby": 3,
"isLeaf": 0,
"childrenTreeNodes": [
{
"id": "1-3-1",
"name": "C/C++",
"label": "C/C++",
"parentid": "1-3",
"isShow": 1,
"orderby": 1,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-3-2",
"name": "Java",
"label": "Java",
"parentid": "1-3",
"isShow": 1,
"orderby": 2,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-3-3",
"name": ".NET",
"label": ".NET",
"parentid": "1-3",
"isShow": 1,
"orderby": 3,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-3-4",
"name": "Objective-C",
"label": "Objective-C",
"parentid": "1-3",
"isShow": 1,
"orderby": 4,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-3-5",
"name": "Go语言",
"label": "Go语言",
"parentid": "1-3",
"isShow": 1,
"orderby": 5,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-3-6",
"name": "Python",
"label": "Python",
"parentid": "1-3",
"isShow": 1,
"orderby": 6,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-3-7",
"name": "Ruby/Rails",
"label": "Ruby/Rails",
"parentid": "1-3",
"isShow": 1,
"orderby": 7,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-3-8",
"name": "其它",
"label": "其它",
"parentid": "1-3",
"isShow": 1,
"orderby": 8,
"isLeaf": 1,
"childrenTreeNodes": null
}
]
},
{
"id": "1-5",
"name": "人工智能",
"label": "人工智能",
"parentid": "1",
"isShow": 1,
"orderby": 5,
"isLeaf": 0,
"childrenTreeNodes": [
{
"id": "1-5-1",
"name": "机器学习",
"label": "机器学习",
"parentid": "1-5",
"isShow": 1,
"orderby": 1,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-5-2",
"name": "深度学习",
"label": "深度学习",
"parentid": "1-5",
"isShow": 1,
"orderby": 2,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-5-3",
"name": "语音识别",
"label": "语音识别",
"parentid": "1-5",
"isShow": 1,
"orderby": 3,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-5-4",
"name": "计算机视觉",
"label": "计算机视觉",
"parentid": "1-5",
"isShow": 1,
"orderby": 4,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-5-5",
"name": "NLP",
"label": "NLP",
"parentid": "1-5",
"isShow": 1,
"orderby": 5,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-5-6",
"name": "强化学习",
"label": "强化学习",
"parentid": "1-5",
"isShow": 1,
"orderby": 6,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-5-7",
"name": "其它",
"label": "其它",
"parentid": "1-5",
"isShow": 1,
"orderby": 7,
"isLeaf": 1,
"childrenTreeNodes": null
}
]
},
{
"id": "1-9",
"name": "智能硬件/物联网",
"label": "智能硬件/物联网",
"parentid": "1",
"isShow": 1,
"orderby": 9,
"isLeaf": 0,
"childrenTreeNodes": [
{
"id": "1-9-1",
"name": "无线通信",
"label": "无线通信",
"parentid": "1-9",
"isShow": 1,
"orderby": 1,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-10",
"name": "物联网技术",
"label": "物联网技术",
"parentid": "1-9",
"isShow": 1,
"orderby": 10,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-11",
"name": "其它",
"label": "其它",
"parentid": "1-9",
"isShow": 1,
"orderby": 11,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-2",
"name": "电子工程",
"label": "电子工程",
"parentid": "1-9",
"isShow": 1,
"orderby": 2,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-3",
"name": "Arduino",
"label": "Arduino",
"parentid": "1-9",
"isShow": 1,
"orderby": 3,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-4",
"name": "体感技术",
"label": "体感技术",
"parentid": "1-9",
"isShow": 1,
"orderby": 4,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-5",
"name": "智能硬件",
"label": "智能硬件",
"parentid": "1-9",
"isShow": 1,
"orderby": 5,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-6",
"name": "驱动/内核开发",
"label": "驱动/内核开发",
"parentid": "1-9",
"isShow": 1,
"orderby": 6,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-7",
"name": "单片机/工控",
"label": "单片机/工控",
"parentid": "1-9",
"isShow": 1,
"orderby": 7,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-8",
"name": "WinCE",
"label": "WinCE",
"parentid": "1-9",
"isShow": 1,
"orderby": 8,
"isLeaf": 1,
"childrenTreeNodes": null
},
{
"id": "1-9-9",
"name": "嵌入式",
"label": "嵌入式",
"parentid": "1-9",
"isShow": 1,
"orderby": 9,
"isLeaf": 0,
"childrenTreeNodes": [
{
"id": "1-9-9-1",
"name": "嵌入式基础",
"label": "嵌入式基础",
"parentid": "1-9-9",
"isShow": 1,
"orderby": 1,
"isLeaf": 1,
"childrenTreeNodes": null
}
]
}
]
}
]- 由于返回的是一个List集合，因此需要定义一个DTO类表示分类信息的模型类
@Data
public class CourseCategoryTreeDto extends CourseCategory {
//子节点
List<CourseCategoryTreeDto> childrenTreeNodes;
}- 定义Controller类
@Resource
private CourseCategoryService categoryService;
@GetMapping("/course-category/tree-nodes")
public List<CourseCategoryTreeDto> queryCatTreeNodes(){
return categoryService.queryCatTreeNodes("1"); //此处1为根节点
}- 定义Mapper
public interface CourseCategoryMapper extends BaseMapper<CourseCategory> {
public List<CourseCategoryTreeDto> selectTreeNodes(String id);
}写递归SQL（用于不确定有几级层级）
<!-- SQL递归查询数据库树形结构 -->
<select id="selectTreeNodes" parameterType="String" resultType="com.xuecheng.content.model.dto.CourseCategoryTreeDto">
WITH recursive t1 as(
SELECT * from course_category WHERE id = #{id}
UNION ALL
SELECT c2.* from course_category c2 INNER JOIN t1 on t1.id = c2.parentid
)
SELECT * from t1 order by t1.id, t1.orderby
</select>内连接查询（确定有多少级层级）
select
one.id one_id,
one.name one_name,
one.parentid one_parentid,
one.orderby one_orderby,
one.label one_label,
two.id two_id,
two.name two_name,
two.parentid two_parentid,
two.orderby two_orderby,
two.label two_label
from course_category one
inner join course_category two on one.id = two.parentid
where one.parentid = 1
and one.is_show = 1
and two.is_show = 1
order by one.orderby,
two.orderby- Service和其实现类
//Service类
public interface CourseCategoryService extends IService<CourseCategory> {
List<CourseCategoryTreeDto> queryCatTreeNodes(String id);
}
//实现类
@Service
public class CourseCategoryServiceImpl extends ServiceImpl<CourseCategoryMapper, CourseCategory> implements CourseCategoryService {
@Resource
CourseCategoryMapper courseCategoryMapper;
/**
* 根据id获取课程分类列表
* @param id 根节点id
* @return 课程分类列表
*/
@Override
public List<CourseCategoryTreeDto> queryCatTreeNodes(String id) {
//执行SQL，获取查询数据
List<CourseCategoryTreeDto> treeDtos = courseCategoryMapper.selectTreeNodes(id);
//将结果转为map，便于后续找根节点，同时可以过滤当前id节点
//.filter(item -> !id.equals(item.getId()))不显示根节点
Map<String, CourseCategoryTreeDto> map = treeDtos.stream().filter(item -> !id.equals(item.getId())).collect(Collectors.toMap(CourseCategory::getId, value -> value, (key1, key2) -> key2));
List<CourseCategoryTreeDto> list = new ArrayList<>();
//遍历返回最终结果,排除根节点
treeDtos.stream().filter(item -> !id.equals(item.getId())).forEach(item ->{
//先将根节点的一级子节点放入数组
if (item.getParentid().equals(id)){
list.add(item);
}
//如果不为一级节点，就找到它的父节点，通过之前保存的map
CourseCategoryTreeDto treeParent = map.get(item.getParentid());
//如果存在父节点就将其放入它的父节点的list中
if (treeParent != null){
//如果它的父节点的子节点列表为空，就新建一个对象存储
if (treeParent.getChildrenTreeNodes() == null){
treeParent.setChildrenTreeNodes(new ArrayList<>());
}
//不为空就放入子节点中
//注意这里是需要获取子节点列表然后插入进入
treeParent.getChildrenTreeNodes().add(item);
}
});
return list;
}
}
- 前端显示示例
评论