请问如何根据子类id查询父类id

Question

现在目录有四层，需要根据子类的id去查询到最顶层，把所有的层级name全部拿到，他有可能给第四层的id也可能给第二层的，我应该怎样写？

Answer 1

最好是存在tree_path：存放着每一个节点的路径，查询通过这个字段就可以。
倘若不存在，那就通过parent_id去递归查找父类。
递归实现路径字段：

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    static class Recursion {
        private Integer id;
        private String path;
        private Integer parentId;
    }

    public static void main(String[] args) {
        List<Recursion> allList = Lists.newArrayList(
                new Recursion(3, "3", 2),
                new Recursion(1, "1", null),
                new Recursion(5, "5", null),
                new Recursion(2, "2", 1),
                new Recursion(4, "4", 3));

        List<Recursion> rootList = allList.stream().filter(item -> Objects.isNull(item.getParentId())).collect(Collectors.toList());
        recursionMethod(rootList, allList);
        // [{"id":3,"parentId":2,"path":"1,2,3"},{"id":1,"path":"1"},{"id":5,"path":"5"},{"id":2,"parentId":1,"path":"1,2"},{"id":4,"parentId":3,"path":"1,2,3,4"}]
        System.out.println(JSON.toJSONString(allList));

    }

    private static void recursionMethod(List<Recursion> rootList, List<Recursion> allList) {
        for (Recursion root : rootList) {
            List<Recursion> childList = allList.stream().filter(item -> Objects.equals(root.getId(), item.getParentId())).collect(Collectors.toList());
            if (CollectionUtils.isNotEmpty(childList)) {
                childList.forEach(child -> child.setPath(root.getPath().concat(Constant.comma).concat(child.getPath())));
                recursionMethod(childList, allList);
            }
        }

    }

Answer 2

不给数据库都是耍流氓，如果是mysql的话，8听说已经支持with的写法了，如果是7或者更低的，

这个方法很舒服，
oracle pg都支持递归查询