公司有个业务需要查出所有的用户权限分类，并将最后一层类别所包含的权限查出来。
数据库说明，有一个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;
}
