数据表格式

需要返回的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;
}

层级固定时

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

比如只查询二级分类

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

层级不固定时

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

with recursive t1 as (
    select * from course_category where id='1'
    union all
    select t2.* from course_category t2
          inner join t1
          on t1.id=t2.parentid
    )
select * from t1

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

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

    @Autowired
    CourseCategoryMapper categoryMapper;
    @Override
    public 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));
        //最终返回的list
        List<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;
    }