数据库树形结构怎么查 四种方法解决

1.使SQL实现树形查询

1.1 树形结构固定，即固定几层结构，可以采用数据库连接查询，这里以两张表为例：

select one.id,
       one.label,
       two.id,
       two.label
from course_category one
         inner join course_category two on two.parentid = one.id
where one.parentid = '1'
  and one.is_show = '1'
  and two.is_show = '1'
order by one.orderby, two.orderby

2.2 树形结构可能变化，采用数据库的递归进行查询

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

2.Java代码实现

1.递归查询数据库

    public List<Menu> findMenu(long parentID) {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        //查询条件
        queryWrapper.eq("parentid", parentID);
        //这里传入的值为父节点的id，比如第一次进入网页这里一定是根节点的id 如果点击展开的话就是要展开节点的id 即子节点的父id

        List<Menu> list = baseMapper.selectList(queryWrapper);
        for (Menu menu : list) {
            //递归子类数据
            menu.setChildMenu(findMenu(menu.getID()));
        }
        return list;
    }

2.一次性全部查询出来，用Java代码实现数据的树形分解

public class TreeDemo{
       public static void main(String[] args) {
        List<TreeEntity> treeList = init();

        List<TreeEntity> collect = treeList.stream()
                .filter(item -> item.getPid() == 0)//构造最外层节点，即id=0的节点
                .map(item -> {
                    item.setChildrenList(getChildren(item, treeList));//id=0的节点就为他设置孩子节点
                    return item;
                }).
                collect(Collectors.toList());
        System.out.println(new Gson().toJsonTree(collect));
    }

 
                         //获得孩子节点
    private static List<TreeEntity> getChildren(TreeEntity treeEntity, List<TreeEntity> treeEntityList) {
        List<TreeEntity> collect = treeEntityList.stream()
                .filter(item -> item.getPid().equals(treeEntity.getId()))//判断当前节点的父id是不是要设置节点的id
                .map(item -> {
                    item.setChildrenList(getChildren(item, treeEntityList));//如果是 为其设置孩子节点 通过递归 为每个除了最外层节点的节点设置孩子节点
                    return item;
                })
                .collect(Collectors.toList());
        return collect;
    }
 
}

注：以上Java代码粘贴自网络