在springboot项目中如何根据部门ID查询其上下级所有数据，生成树形结构的数据

 mysql数据库的部门表结构：

需求：将部门的树形结构数据已JSON形式返回给前端

解决思路：
1、先获取当前部门的所有父级节点，
2、再获取当前部门的所有子节点，
3、查询组合（父级节点、当前节点、子节点组合起来）后的节点数据
4、在java中进行遍历生成树形菜单

具体操作如下：

1、部门的实体VO

@Data
public class DepartmentVO {
    private int id;                         //部门ID
    private String parentId;               //部门父级ID
    private String title;                   //部门标题
    private List<DepartmentVO> children;    //子节点
}

2、在service层解析节点数据，生成树形结构实体对象

//根据部门ID获取其父子级数据
public List<DepartmentVO> getOneDataByDeptId(String id) {
    List<DepartmentVO> rslist=new ArrayList<>();

    List<DepartmentVO> depts = mapper.getOneDataByDeptId(id);//查询当前部门数据库中所有父子级记录
    //先将所有一级部门添加至rslist
    for(DepartmentVO model:depts){
        if(model.getParentId()==null){      //如果父级为空，则为*部门
            rslist.add(model);
        }
    }

    // 为父级部门设置子部门，getChild是递归调用的
    for (DepartmentVO departmentVO : rslist) {
        //传入父级部门Id,以及所有查询结果
        departmentVO.setChildren(getChild(departmentVO.getId(), depts));
    }

    return rslist;
}

//传进父级ID,查找其子部门
public List<DepartmentVO> getChild(int id,List<DepartmentVO> depts){

    List<DepartmentVO> childList = new ArrayList<>();

    for (DepartmentVO model : depts) {
        // 遍历所有属于父级节点的子节点
        if (StringUtils.isNotBlank(model.getParentId())) {
            if (model.getParentId().equals(id+"")) {
                childList.add(model);
            }
        }
    }

    //然后给所有子节点添加子节点
    for (DepartmentVO childmodel : childList) {
        // 遍历所有属于父级节点的子节点
        childmodel.setChildren(getChild(childmodel.getId(),depts));
    }


    // 递归退出条件
    if (childList.size() == 0) {
        return null;
    }
    return childList;
}

3、在Mapper.xml中如何查询节点的父子级数据

<!--查询节点上下級所有id,返回id集合-->
<sql id="getNoteAllParentAndSonNote">
    <!--查询当前节点的所有父节点-->
    SELECT T2.parent_id  id
    FROM (
        SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM department WHERE id = _id) AS 2v2,
        @l := @l + 1 AS lvl
        FROM
        (SELECT @r := #{queryDeptId}) vars,
        department h
        WHERE @r &lt;&gt; 0
    ) T1
    JOIN department T2
    ON T1._id = T2.id and T2.del_sts ='0' and T2.parent_id is not null

    UNION
    <!--节点本身-->
    select #{queryDeptId} from department where id=#{queryDeptId}
    UNION

    <!--查询当前节点的所有子节点-->
    SELECT c.id
    FROM
    (
        SELECT a.id,
            IF (
                FIND_IN_SET(a.parent_id ,@pids) > 0,
                IF (
                length(@pids) - length(
                REPLACE (@pids, a.parent_id, '')
                ) > 1,
                IF (
                length(@pids) - length(REPLACE(@pids, a.id, '')) > 1 ,@pids ,@pids := concat(@pids, ',', a.id)
                ) ,@pids := concat(@pids, ',', a.id)
                ),
                0
            ) AS 'plist',
            IF (
                FIND_IN_SET(a.parent_id ,@pids) > 0,
                @pids,
                0
            ) AS ischild
        FROM
        (
            SELECT
            r.id,r.parent_id
            FROM
            department r where r.del_sts ='0'
        ) a,
        (SELECT @pids := #{queryDeptId}) b
    ) c
    WHERE
    c.ischild != 0
</sql>

<!--对应Mapper中的查询方法-->
<select id="getOneDataByDeptId" resultMap="backData1">
    select id,parent_id,dept_name,manager from <include refid="tbName"/>
    <!--条件暂定-->
    <where>
        del_sts ='0'
        and id in (
           <trim suffixOverrides=",">
              <!--这里是引用上面的sql标签-->
              <include refid="getNoteAllParentAndSonNote"/>
           </trim>
        )
    </where>
</select>

以上就是全部内容