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 <> 0 ) T1 JOIN department T2 ON T1._id = T2.id and T2.del_sts ='0' 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> 以上就是全部内容