Java Mybatis查询返回树结构数据

最近工作中需返回树结构数据，记录方法

1.Mybatis中实现递归查询

达梦数据库：

        
        
        
        
        
        
        
        
        
        
        
        



        SELECT p.ID,isnull(p.PARENT_ID,'0') PARENTID,p.RANK_ID RANKID,p.RANKNAME RANKNAME,
               p.ISREADONLY,isnull(p.MTRL_ID,'0') MATERIALID,isnull(p.DISPLAY_ORDER,'0') DISPLAYORDER,p.MTRL_ALIAS NAME,
               p.MTRL_ALIAS MATERIALNAME,p."LEVEL",p.SHEET_ID
        FROM(
                SELECt s.*,dd.MTRL_ALIAS
                FROM
                    (
                        SELECt mbr.*,(SELECT RANK_NAME FROM PM_RANK_T WHERe RANK_ID=mbr.RANK_ID) AS RankName,LEVEL "LEVEL" FROM MB_MTRLBALNSHEETROW_T mbr
                        WHERe SHEET_ID =#{sheetId}
                            
                              and PARENT_ID=#{parentId}
                            
                            
                            and PARENT_ID is null
                            
                        START WITH PARENT_ID IS NULL
                        CONNECT BY PARENT_ID = PRIOR ID

                    )s
                        LEFT OUTER JOIN
                    (
                        SELECt m.MTRL_ID,m.MTRL_ALIAS FROM PM_MTRL_T m
                    )dd
                    ON s.MTRL_ID = dd.MTRL_ID
            ) p
        ORDER BY "LEVEL",DISPLAY_ORDER;
 
parentid为从几级的开始查询，如从根部开始即传parentid=0，数据库中记录为null，所以对null作处理。 利用上次查询结果colliection中column的值做递归查询，查出所有子菜单，这里的返回结果必须为resultMap，并且值为上面构建的resultMap 。
 
Tip:因为通过mybatis递归查找子菜单如果菜单比较多会非常耗时，可以通过mybatis一次性把所有菜单查找出来，再通过程序构建菜单树
 
2.从数据库读取菜单，递归生成菜单树
 

        SELECT p.ID,isnull(p.PARENT_ID,'0') PARENTID,p.RANK_ID RANKID,p.RANKNAME RANKNAME,
               p.ISREADONLY,isnull(p.MTRL_ID,'0') MATERIALID,isnull(p.DISPLAY_ORDER,'0') DISPLAYORDER,p.MTRL_ALIAS NAME,
               p.MTRL_ALIAS MATERIALNAME,p."LEVEL",p.SHEET_ID
        FROM(
                SELECt s.*,dd.MTRL_ALIAS
                FROM
                    (
                        SELECt mbr.*,(SELECT RANK_NAME FROM PM_RANK_T WHERe RANK_ID=mbr.RANK_ID) AS RankName,LEVEL "LEVEL" FROM MB_MTRLBALNSHEETROW_T mbr
                        WHERe SHEET_ID =#{sheetId}
                            START WITH PARENT_ID IS NULL
                        CONNECT BY PARENT_ID = PRIOR ID
                    )s
                        LEFT OUTER JOIN
                    (
                        SELECt m.MTRL_ID,m.MTRL_ALIAS FROM PM_MTRL_T m
                    )dd
                    ON s.MTRL_ID = dd.MTRL_ID
            ) p
        ORDER BY "LEVEL",DISPLAY_ORDER;

    List balansheetrowDtoList = this.buildMenuTree(balansheetrowDao.selSheelRowList(sheetId),0l);    
    
    private List buildMenuTree(List menuList, Long pid) {
        List treeList = new ArrayList<>();
        menuList.forEach(menu -> {
            if (menu.getParentId().equals(pid)) {
                menu.setChildren(buildMenuTree(menuList, menu.getId()));
                treeList.add(menu);
            }
        });
        return treeList;
    }

参考：

Mybatis自查询递归查找子菜单