树形表/树形数据接口的开发

数据表格式

在这里插入图片描述

需要返回的json格式

点击查看json数据

 [{"childrenTreeNodes" : [{"childrenTreeNodes" : null,"id" : "1-1-1","isLeaf" : null,"isShow" : null,"label" : "HTML/CSS","name" : "HTML/CSS","orderby" : 1,"parentid" : "1-1"},{"childrenTreeNodes" : null,"id" : "1-1-5","isLeaf" : null,"isShow" : null,"label" : "AngularJS","name" : "AngularJS","orderby" : 5,"parentid" : "1-1"}],"id" : "1-1","isLeaf" : null,"isShow" : null,"label" : "前端开发","name" : "前端开发","orderby" : 1,"parentid" : "1"},{"childrenTreeNodes" : [{"childrenTreeNodes" : null,"id" : "1-2-1","isLeaf" : null,"isShow" : null,"label" : "微信开发","name" : "微信开发","orderby" : 1,"parentid" : "1-2"},{"childrenTreeNodes" : null,"id" : "1-2-2","isLeaf" : null,"isShow" : null,"label" : "iOS","name" : "iOS","orderby" : 2,"parentid" : "1-2"},{"childrenTreeNodes" : null,"id" : "1-2-8","isLeaf" : null,"isShow" : null,"label" : "其它","name" : "其它","orderby" : 8,"parentid" : "1-2"}],"id" : "1-2","isLeaf" : null,"isShow" : null,"label" : "移动开发","name" : "移动开发","orderby" : 2,"parentid" : "1"}]

数据对应的模型类要封装树形结构的子节点

@Data
public class CourseCategoryTreeDto extends CourseCategory implements Serializable {List<CourseCategoryTreeDto> childrenTreeNodes;
}

层级固定时

树的层级固定时，可以使用表的自链接查询。

比如只查询二级分类

selectone.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_labelfrom course_category oneinner join course_category two on one.id = two.parentid

层级不固定时

使用MySQL的递归实现，使用with语法

with recursive t1 as (select * from course_category where id='1'union allselect t2.* from course_category t2inner join t1on t1.id=t2.parentid)
select * from t1

MySQL为了避免无限递归默认递归次数为1000，可以通过设置cte_max_recursion_depth参数增加递归深度，还可以通过max_execution_time限制执行时间，超过此时间也会终止递归操作。

MySQL递归相当于在存储过程中执行若干次sql语句，java程序仅与数据库建立一次链接执行递归操作，所以只要控制好递归深度，控制好数据量性能就没有问题。

    @AutowiredCourseCategoryMapper categoryMapper;@Overridepublic List<CourseCategoryTreeDto> queryTreeNodes(String id) {List<CourseCategoryTreeDto> list = categoryMapper.selectTreeNodes(id);//将返回的数据行转map，用于后面添加子节点使用Map<String, CourseCategoryTreeDto> map = list.stream().filter(item -> !id.equals(item.getId())).collect(Collectors.toMap(key -> key.getId(), value -> value));//最终返回的listList<CourseCategoryTreeDto> res = new ArrayList<>();list.stream().filter(item -> !id.equals(item.getId())).forEach(item -> {// 如果当前节点的父节点是根节点，直接添加当前节点到结果集if (item.getParentid().equals(id)) {res.add(item);}//找到当前节点的父节点，把当前节点添加到父节点的孩子中CourseCategoryTreeDto categoryTreeDto = map.get(item.getParentid());if (categoryTreeDto != null) {if (categoryTreeDto.getChildrenTreeNodes() == null) {List<CourseCategoryTreeDto> childrenNodes = new ArrayList<>();categoryTreeDto.setChildrenTreeNodes(childrenNodes);}categoryTreeDto.getChildrenTreeNodes().add(item);}});return res;}