SpringBoot、mybatis返回树结构的数据

公司有个业务需要查出所有的用户权限分类，并将最后一层类别所包含的权限查出来。

数据库说明，有一个parent_id 字段是最好的：

parent_id的值就是上级的id，一般的话，最顶级的parent_id是设置为0。

先看看表结构：

 下面不说废话，直接上代码：

定义的vo类：

    @ApiModelProperty("id")
    private Long id;

    @ApiModelProperty("父ID")
    private Long parentId;

    
    @ApiModelProperty("名称")
    private String name;
	
	@ApiModelProperty("子节点")
    private List<UserVo> children;

 获取列表

    List<UserVo>  userList userService.findUsersAndChildrenList(User);
    List<UserVo> users = new ArrayList<>();
        for (UserVo r : userList) {
            UserVo user = new UserVo();
            user.setId(r.getId());
            user.setParentId(r.getParentId());
            user.setName(r.getName());
            List<UserVo>  children = this.getChildrenList(r.getId(), status);
            user.setChildren(children);
            users.add(user);
     }

    public List<UserVo> getChildrenList(Long cid){
        List<UserVo> users=  userService.findUserChildrenByParentId(cid);
        List<UserVo> userList= new ArrayList<>();
        if(users){
            for (UserVo u : users) {
                UserVo user = new UserVo();
                user.setId(u.getId());
                user.setName(u.getName());
                user.setParentId(u.getParentId());
                List<UserVo >  children = this.getChildrenList(u.getId());
                user.setChildren(children);
                userList.add(user);
            }
        }
        return  userList;
    }

 mybatis查询：

<select id="findUserChildrenList" resultMap="BaseResultMap">
        SELECT *
        FROM user
        WHERE parent_id=#{id}
</select>

最终的数据结构：

{
    "message":'获取成功',
    "data":{
        "num":1,
        "pageSize":20,
        "total":1,
        "list":[
            {
                "id":6,
                "name":"测试",
                "parent_id":1,
                "children":[
                    {
                        "id":9,
						"name":"测试1",
						"parent_id":6,
                        "children":[
                            {
                                "id":20,
								"name":"测试2",
								"parent_id":9,
                                "children":[
                                    {
                                        "id":21,
										"name":"测试3",
										"parent_id":20,
                                    },
                                    {
                                        "id":22,
										"name":"测试4",
										"parent_id":20,
                                    },
                                    {
                                         "id":23,
										"name":"测试5",
										"parent_id":20,
                                    }
                                ], 
                            }
                        ], 
                    }, 
                ], 
            }
        ]
    },
    "code":200
}

 如果要查某个节点的所有父节点：

 mybatis查询改为 ：

<select id="findUserParentListById" resultMap="BaseResultMap">
        SELECT *
        FROM user
        WHERE id=#{id}
</select>

    public List<UserVo> getParentList(Long cid){
        List<UserVo> users=  userService.findUserParentListById(cid);
        List<UserVo> userList= new ArrayList<>();
        if(users){
            for (UserVo u : users) {
                UserVo user = new UserVo();
                user.setId(u.getId());
                user.setName(u.getName());
                user.setParentId(u.getParentId());
                List<UserVo >  children = this.getParentList(u.getParentId());
                user.setChildren(children);
                userList.add(user);
            }
        }
        return  userList;
    }