MySQL存储树形结构

在学习谷粒mall过程中发现一个问题，mysql存储三级菜单时，发现主方法（listWithTree()）和递归方法（getChildrens()）中都要用sorted()方法，本以为在listWithTree（）用一次sorted()即可，然后做了测试模拟场景

    @Override
    public List listWithTree() {
        //1.查出所有分类
        List entities = baseMapper.selectList(null);
        //2.组装成父子的树形结构
            //1)找到一级分类
        List level1Menus = entities.stream().filter(categoryEntity ->
            categoryEntity.getParentCid() == 0
        ).map(menu->{
            menu.setChildren(getChildrens(menu,entities));
            return menu;
        //排序
        }).sorted((menu1,menu2)->{
            return (menu1.getSort()==null?0:menu1.getSort())-(menu2.getSort()==null?0:menu2.getSort());
        }).collect(Collectors.toList());


        return level1Menus;
    }
    //递归查找菜单的子菜单
    private List getChildrens(CategoryEntity root,List all){

        List children = all.stream().filter(CategoryEntity -> {
            return CategoryEntity.getParentCid() == root.getCatId();
        }).map(CategoryEntity->{
            //找到子菜单
            CategoryEntity.setChildren(getChildrens(CategoryEntity,all));
            return CategoryEntity;
        //排序    
        }).sorted((menu1,menu2)->{
            return (menu1.getSort()==null?0:menu1.getSort())-(menu2.getSort()==null?0:menu2.getSort());
        }).collect(Collectors.toList());

        return children;
    }

这里对db查询后的集合数据进行处理，所有流都进行排序，结果正常

    private static void test() {
        //模拟数据
        Node e0 = new Node().setId(1L).setPid(0L).setSort(7);
        Node e1 = new Node().setId(2L).setPid(1L).setSort(6);
        Node e2 = new Node().setId(3L).setPid(1L).setSort(5);
        Node e3 = new Node().setId(4L).setPid(2L).setSort(4);
        Node e4 = new Node().setId(5L).setPid(2L).setSort(3);
        Node e5 = new Node().setId(6L).setPid(3L).setSort(2);
        Node e6 = new Node().setId(7L).setPid(3L).setSort(1);
        ArrayList e2_chilren = new ArrayList<>();
        e2_chilren.add(e5);
        e2_chilren.add(e6);
        e2.setChildrenList(e2_chilren);
        ArrayList e1_chilren = new ArrayList<>();
        e1_chilren.add(e3);
        e1_chilren.add(e4);
        e1.setChildrenList(e1_chilren);
        ArrayList e0_chilren = new ArrayList<>();
        e0_chilren.add(e1);
        e0_chilren.add(e2);
        e0.setChildrenList(e0_chilren);

        ArrayList nodes = new ArrayList<>();
        nodes.add(e0);
        nodes.add(e1);
        nodes.add(e2);
        nodes.add(e3);
        nodes.add(e4);
        nodes.add(e5);
        nodes.add(e6);

        //查看模拟数据是否正确
//        for (Entity entity : entities) {
//            System.out.println(entity);
//        }
        
        List result = nodes.stream().filter(node ->
                node.getPid() == 0
        ).map(node->{
            node.setChildrenList(getChildrens(node,nodes));
            return node;
        }).sorted((node1,node2)->{
            return (node1.getSort()==null?0:node1.getSort())-(node2.getSort()==null?0:node2.getSort());
        }).collect(Collectors.toList());
    
        //打印排序后的结果
        System.out.println(result);
        }

    //递归查找菜单的子菜单
    private static List getChildrens(Node root,List all) {

        List children = all.stream().filter(node -> {
            return node.getPid() == root.getId();
        }).map(node -> {
            //找到子菜单
            node.setChildrenList(getChildrens(node, all));
            return node;
        }).sorted((node1,node2)->{
            //菜单排序
            return (node1.getSort()==null?0:node1.getSort())-(node2.getSort()==null?0:node2.getSort());
        }).collect(Collectors.toList());

        return children;
    }

结果为

[Entity(id=1, pid=0, sort=7, children=[
  		Entity(id=2, pid=1, sort=6, children=[
  					Entity(id=4, pid=2, sort=4, children=[]),
                    Entity(id=5, pid=2, sort=3, children=[])]), 
  		Entity(id=3, pid=1, sort=5, children=[
  					Entity(id=6, pid=3, sort=2, children=[]),
                    Entity(id=7, pid=3, sort=1, children=[])])])]

展示成xml

	
		
		
	
	
		
		
	

1 - 2 - 4
      - 5
  - 3 - 6
      - 7

结果显示正常。
接着注释以下代码后运行

    //递归查找菜单的子菜单
    private static List getChildrens(Node root,List all) {

        List children = all.stream().filter(node -> {
            return node.getPid() == root.getId();
        }).map(node -> {
            //找到子菜单
            node.setChildrenList(getChildrens(node, all));
            return node;
        })
        .collect(Collectors.toList());

        return children;
    }

结果为

[Node(id=1, pid=0, sort=7, children=[
		Node(id=3, pid=1, sort=5, children=[
					Node(id=7, pid=3, sort=1, children=[]),	
					Node(id=6, pid=3, sort=2, children=[])]), 
		Node(id=2, pid=1, sort=6, children=[
					Node(id=5, pid=2, sort=3, children=[]), 
					Node(id=4, pid=2, sort=4, children=[])])])]

展示为xml

	
		
		
	
	
		
		
	

1 - 3 - 7
      - 6
  - 2 - 5
      - 4

结果显示没有完成排序，说明每次使用流都要进行排序。