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
