查询ID下的树子节点

数据库表

字段	类型
id	String
parent_id	父ID
name	name

1.SQL实现

SELECT c.id ,c.parent_id FROM
 (SELECT @id idlist, @lv := @lv + 1 lv,
  (SELECT @id := GROUP_CONCAT(id SEPARATOR ',') FROM Emp WHERE FIND_IN_SET(parent_id, @id)) sub
  FROM Emp, (SELECT @id := '3', @lv := 0) vars WHERE @id IS NOT NULL
 ) t, Emp c
WHERE FIND_IN_SET(c.parent_id, t.idlist)

2.代码实现

public class Tree {

	private String code;

	private String name;

	private String parentCode;

	private Integer level;

	private Tree parent;

	private List<Tree> node;

	public Tree() {
	}

	public Tree(String code, String name, String parentCode, Integer level) {
		this.code = code;
		this.name = name;
		this.parentCode = parentCode;
		this.level = level;
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	/**
	 * 将一组无序的树组装成树形结构的数据
	 * 
	 * @param treeList
	 * @return
	 */
	public static List<Tree> buildTree(List<Tree> treeList) {
		if (treeList == null || treeList.size() == 0) {
			return null;
		}
		List<Tree> trees = getRootNode(treeList);
		trees.forEach(node -> node.setLevel(0));
		List<Tree> parentNodes = trees;
		List<Tree> subNodes;
		int level = 1;
		do {
			treeList.removeAll(parentNodes);
			subNodes = getRootNode(treeList);
			if (subNodes != null && subNodes.size() > 0) {
				for (Tree subNode : subNodes) {
					subNode.setLevel(level);
					for (Tree parentNode : parentNodes) {
						if (codeEquals(parentNode.getCode(), subNode.getParentCode())) {
							List<Tree> list = parentNode.getNode();
							if (list == null) {
								list = new ArrayList<>();
								parentNode.setNode(list);
							}
							list.add(subNode);
							subNode.setParent(parentNode);
						}
					}
				}
			}
			parentNodes = subNodes;
			level++;
		} while (subNodes != null);
		return trees;
	}

	/**
	 * 获取treeList中的顶级节点
	 * 
	 * @param treeList
	 * @return
	 */
	public static List<Tree> getRootNode(List<Tree> treeList) {
		if (treeList == null || treeList.size() == 0) {
			return null;
		}
		List<String> codes = treeList.stream().map(tree -> tree.getCode()).collect(Collectors.toList());
		List<Tree> trees = treeList.stream().filter(
				node -> !codes.contains(node.getParentCode()) || codeEquals(node.getCode(), node.getParentCode()))
				.collect(Collectors.toList());
		return trees;
	}

    public static void printTree(List<Tree> trees){
        for(Tree tree : trees){
            String prefix = "----";
            for(int i=0;tree.getLevel()!=null &&i<tree.getLevel();i++){
                prefix += "----";
            }
            System.out.println(prefix+tree);
            if(tree.getNode() != null && tree.getNode().size() > 0){
                printTree(tree.getNode());
            }
        }
    }


	/**
	 * 判断两个编码是否相同
	 * 
	 * @param code
	 * @param parentCode
	 * @return
	 */
	public static boolean codeEquals(String code, String parentCode) {
		if (code == null) {
			if (parentCode == null) {
				return true;
			}
		} else {
			if (parentCode != null && code.equals(parentCode)) {
				return true;
			}
		}
		return false;
	}

	public String getName() {
		return name;
	}

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

	public String getParentCode() {
		return parentCode;
	}

	public void setParentCode(String parentCode) {
		this.parentCode = parentCode;
	}

	public Integer getLevel() {
		return level;
	}

	public void setLevel(Integer level) {
		this.level = level;
	}

	public Tree getParent() {
		return parent;
	}

	public void setParent(Tree parent) {
		this.parent = parent;
	}

	public List<Tree> getNode() {
		return node;
	}

	public void setNode(List<Tree> node) {
		this.node = node;
	}

	@Override
	public String toString() {
		return "Tree{" + "code='" + code + '\'' + ", name='" + name + '\'' + ", parentCode='" + parentCode + '\''
				+ ", level=" + level + '}';
	}

}

测试类

public class TreeTest {

    public static void main(String[] args) {
        Tree tree0 = new Tree(null,"根节点0",null,null);
        Tree tree1 = new Tree("1","根节点1",null,null);
        Tree tree2 = new Tree("2","根节点2",null,null);

        Tree tree3 = new Tree("3","子节点-2-3","2",null);
        Tree tree4 = new Tree("4","子节点-2-3","2",null);
        Tree tree5 = new Tree("5","根节点0-5","0",null);
        Tree tree6 = new Tree("6","根节点0-6","0",null);

        Tree tree7 = new Tree("7","根节点0-6-7","6",null);
        Tree tree8 = new Tree("8","根节点0-6-7-8","7",null);
        Tree tree9 = new Tree("9","根节点0-5-9","5",null);
        Tree tree10 = new Tree("10","根节点0-5-10","5",null);
        Tree tree13 = new Tree("13","根节点0-5-10-13","10",null);
        Tree tree11 = new Tree("11","根节点0-5-10-13-11","13",null);
        Tree tree12 = new Tree("12","根节点0-5-10-13-12","13",null);

        List<Tree> treeList = new ArrayList<>();
        treeList.add(tree0);
        treeList.add(tree1);
        treeList.add(tree2);
        treeList.add(tree3);
        treeList.add(tree4);
        treeList.add(tree5);
        treeList.add(tree6);
        treeList.add(tree7);
        treeList.add(tree8);
        treeList.add(tree9);
        treeList.add(tree10);
        treeList.add(tree11);
        treeList.add(tree12);
        treeList.add(tree13);

        List<Tree> trees = Tree.buildTree(treeList);
        printTree(trees);
    }

    private static void printTree(List<Tree> trees){
        for(Tree tree : trees){
            String prefix = "----";
            for(int i=0;i<tree.getLevel();i++){
                prefix += "----";
            }
            System.out.println(prefix+tree);
            if(tree.getNode() != null && tree.getNode().size() > 0){
                printTree(tree.getNode());
            }
        }
    }