Java以及Sql语句获取Tree型数据

1.首先是java代码实现树形结构
1.1可以直接上代码，有一般递归和JAVA8 Stream新特性实现

private List<Department> children = new ArrayList<>();  注意这个要new出来，不能直接写成 private List<Department> children； 不然JAVA8 Stream 方法会报空指针，可以试试！！！！！！！！！！

package com.example.demo.vo;

import java.util.ArrayList;
import java.util.List;

public class Department {
    private Integer id;
    private String name;
    private Integer parentId;
    private List<Department> children = new ArrayList<>();

    public List<Department> getChildren()
    {
        return children;
    }

    public void setChildren(List<Department> children)
    {
        this.children = children;
    }

    public Department(Integer id, String name, Integer parentId)
    {
        this.id = id;
        this.name = name;
        this.parentId = parentId;
    }

    public Integer getId()
    {
        return id;
    }

    public void setId(Integer id)
    {
        this.id = id;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public Integer getParentId()
    {
        return parentId;
    }

    public void setParentId(Integer parentId)
    {
        this.parentId = parentId;
    }

    @Override
    public String toString()
    {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", parentId=" + parentId +
                ", children=" + children +
                '}';
    }
}

package com.example.demo.vo;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

public class DeptTreeUtil
{
    public static List<Department> initData(){
        List<Department> departmentList = new ArrayList<>();
        departmentList.add(new Department(1, "江苏省", 0));
        departmentList.add(new Department(2, "南京", 1));
        departmentList.add(new Department(3, "无锡", 1));
        departmentList.add(new Department(4, "湖北省", 0));
        departmentList.add(new Department(5, "武汉市", 4));
        departmentList.add(new Department(6, "武昌", 5));
        departmentList.add(new Department(7, "汉口", 5));
        departmentList.add(new Department(8, "汉阳", 5));
        departmentList.add(new Department(9, "孝感市", 4));
        departmentList.add(new Department(10, "云梦县", 9));
        departmentList.add(new Department(11, "测试", 100));

        return departmentList;
    }
    //递归方法一 java8新特性
    public static List<Department> makeTree(List<Department> departmentList, Integer pId) {
        //子类
        List<Department> children = departmentList.stream().filter(x -> x.getParentId() .equals(pId) ).collect(Collectors.toList());
        //后辈中的非子类
        List<Department> successor = departmentList.stream().filter(x -> !x.getParentId() .equals(pId) ).collect(Collectors.toList());
        children.forEach(x ->
                         {
                             makeTree(successor, x.getId()).forEach(
                                     y -> x.getChildren().add(y)
                             );
                         }
        );

        return children;

    }

    //递归方法一 普通递归
    public static void getChildList(List<Department> list,List<Department> rootList){
        // 得到子节点列表
        for (Department department : rootList){
            List<Department> childRenList = new ArrayList<>();
            for (Department department1 : list){
                if(department.getId().equals(department1.getParentId())){
                    childRenList.add(department1);
                }
            }
            if(childRenList.size() > 0){
                department.setChildren(childRenList);
                getChildList(list,childRenList);
            }
        }
    }

}

controller层主要是便于接口测试看到json格式

package com.example.demo.controller;

import java.util.ArrayList;
import java.util.List;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.example.demo.vo.Department;
import com.example.demo.vo.DeptTreeUtil;

@RestController
public class TreeController
{
    @RequestMapping("/getDeptTree")
    public Object testTree(){
        //获取部门数据
        List<Department> list = DeptTreeUtil.initData();
        //java8 Stream处理后的tree行结构数据
        List<Department> tree = DeptTreeUtil.makeTree(list, 0);
        return tree;
    }

    @RequestMapping("/getDeptTree2")
    public Object getTree(){
        List<Department> list = DeptTreeUtil.initData();

        List<Department> rootList = new ArrayList<>();
        //先获取列表中parentId为0的根节点
        for (Department department : list){
            if(department.getParentId().equals(0)){
                rootList.add(department);
            }
        }
        DeptTreeUtil.getChildList(list, rootList);

        return rootList;
    }
}

测试结果可以用postman可以看到json各式如下，两个接口都是一样的结果：

[
    {
        "id": 1,
        "name": "江苏省",
        "parentId": 0,
        "children": [
            {
                "id": 2,
                "name": "南京",
                "parentId": 1,
                "children": []
            },
            {
                "id": 3,
                "name": "无锡",
                "parentId": 1,
                "children": []
            }
        ]
    },
    {
        "id": 4,
        "name": "湖北省",
        "parentId": 0,
        "children": [
            {
                "id": 5,
                "name": "武汉市",
                "parentId": 4,
                "children": [
                    {
                        "id": 6,
                        "name": "武昌",
                        "parentId": 5,
                        "children": []
                    },
                    {
                        "id": 7,
                        "name": "汉口",
                        "parentId": 5,
                        "children": []
                    },
                    {
                        "id": 8,
                        "name": "汉阳",
                        "parentId": 5,
                        "children": []
                    }
                ]
            },
            {
                "id": 9,
                "name": "孝感市",
                "parentId": 4,
                "children": [
                    {
                        "id": 10,
                        "name": "云梦县",
                        "parentId": 9,
                        "children": []
                    }
                ]
            }
        ]
    }
]

2.然后在mySql语句其实也可以实现

2.1先创建表如下，当然这是ORACLE的语法，mysql需要些存储过程，有需要可以自己去学学，目前我还不会，以后补上去

CREATE TABLE TBL_TEST
(
ID    NUMBER,
NAME  VARCHAR2(100 BYTE),
PID   NUMBER                                  DEFAULT 0
);
 
#插入测试数据：
可以自己进行插入
 
#从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
 
#从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id