HelloWorld
登录

java递归查询公司下所有部门及子部门

Wesley13

2021-10-11

1234 1 0

引自：https://blog.csdn.net/u014079773/article/details/53338116

业务要求：

查询公司下所有子部门及子部门：

原型：

1.创建公司和部门实体：

CompanyVos：

[java]  view plain copy

  1. package com.suwei.sysMng.vo;

  2. import java.io.Serializable;

  3. import java.util.List;

  4. public class CompanyVos  implements Serializable{

  5.     /**

  6. *

  7. */

  8.     private static final long serialVersionUID = 5617344073236043292L;

  9.     private Long companyId;

  10.     private String companyName;

  11.     private List<DeptVos> deptVos;

  12.     public Long getCompanyId() {

  13.         return companyId;

  14.     }  

  15.     public void setCompanyId(Long companyId) {

  16.         this.companyId = companyId;

  17.     }  

  18.     public String getCompanyName() {

  19.         return companyName;

  20.     }  

  21.     public void setCompanyName(String companyName) {

  22.         this.companyName = companyName;

  23.     }  

  24.     public List<DeptVos> getDeptVos() {

  25.         return deptVos;

  26.     }  

  27.     public void setDeptVos(List<DeptVos> deptVos) {

  28.         this.deptVos = deptVos;

  29.     }  

  30.     public String toString() {

  31.         return "CompanyVos [companyId=" + companyId + ", companyName=" + companyName +", deptVos="+"]";

  32.     }  

  33. }  

DeptVos：

[java]  view plain copy

  1. package com.suwei.sysMng.vo;

  2. import java.io.Serializable;

  3. import java.util.List;

  4. public class DeptVos  implements Serializable{

  5.     /**

  6. *

  7. */

  8.     private static final long serialVersionUID = 7648136453963080696L;

  9.     private Long companyId;

  10.     private Long fDeptId;

  11.     private Long deptId;

  12.     private String deptName;

  13.     private List<DeptVos> subDeptVos;

  14.     public Long getCompanyId() {

  15.         return companyId;

  16.     }  

  17.     public void setCompanyId(Long companyId) {

  18.         this.companyId = companyId;

  19.     }  

  20.     public Long getfDeptId() {

  21.         return fDeptId;

  22.     }  

  23.     public void setfDeptId(Long fDeptId) {

  24.         this.fDeptId = fDeptId;

  25.     }  

  26.     public Long getDeptId() {

  27.         return deptId;

  28.     }  

  29.     public void setDeptId(Long deptId) {

  30.         this.deptId = deptId;

  31.     }  

  32.     public String getDeptName() {

  33.         return deptName;

  34.     }  

  35.     public void setDeptName(String deptName) {

  36.         this.deptName = deptName;

  37.     }  

  38.     public List<DeptVos> getSubDeptVos() {

  39.         return subDeptVos;

  40.     }  

  41.     public void setSubDeptVos(List<DeptVos> subDeptVos) {

  42.         this.subDeptVos = subDeptVos;

  43.     }  

  44.     public String toString() {

  45.         return "DeptVos [deptId=" + deptId + ", deptName=" + deptName

  46.                 + ", subDeptVos=" + subDeptVos + "]";

  47.     }  

  48. }  

2.查询所有公司和该公司下所有部门及子部门：

[html]  view plain copy

  1. <select id="findCompanys"   parameterType="map"  resultType="com.suwei.sysMng.vo.CompanyVos">

  2.         select sc.id AS "companyId" , sc.company_name as "companyName"  

  3.         from SJK_COMPANY sc  

  4.         inner  join SJK_USER_CATALOG uc on sc.tree_node_id = uc.tree_node_id

  5.         where sc.is_del=1 and uc.is_del=1 and uc.type=1

  6.         <if test="userId!=null and userId!=''  ">

  7.             and uc.user_id=#{userId}

  8.         </if>

  9.     </select>

  10.     <select id="findAllDepts"   parameterType="map"  resultType="com.suwei.sysMng.vo.DeptVos">

  11.         select pd.dept_id as "deptId",pd.dept_name as "deptName" ,pd.fdept_id as "fDeptId" , sc.id as "companyId"  

  12.         from PT_DEPT    pd  

  13.         inner join SJK_COMPANY sc on pd.company_id=sc.id

  14.         where  1=1

  15.          and  sc.is_del=1

  16.          and  pd.fdept_id=#{fDeptId}  and pd.company_id=#{companyId}

  17.     </select>

3.dao层：

[java]  view plain copy

  1. package com.suwei.sysMng.dao;

  2. import java.util.List;

  3. import java.util.Map;

  4. import com.suwei.sysMng.vo.CompanyVos;

  5. import com.suwei.sysMng.vo.DeptVos;

  6. public interface BorrowSubOrderDao {

  7.     List<CompanyVos> findCompanys(Map<String, Object> param);  

  8.     List<DeptVos> findAllDepts(Map<String, Object> param);  

  9. }  

service层：

[java]  view plain copy

  1. package com.suwei.sysMng.service;

  2. import java.util.List;

  3. import java.util.Map;

  4. import java.util.Set;

  5. import com.alibaba.fastjson.JSONObject;

  6. import com.suwei.sysMng.util.ModelResults;

  7. import com.suwei.sysMng.vo.CompanyDeptVo;

  8. import com.suwei.sysMng.vo.CompanyVos;

  9. import com.suwei.sysMng.vo.DeptVos;

  10. public interface BorrowSubOrderService {

  11.        public List<CompanyVos> findCompanyAndDepts(Map<String, Object> param);

  12. }  

serviceImpl层：

[java]  view plain copy

  1. package com.suwei.sysMng.service.impl;

  2. import java.util.ArrayList;

  3. import java.util.HashMap;

  4. import java.util.Iterator;

  5. import java.util.List;

  6. import java.util.Map;

  7. import java.util.Set;

  8. import org.springframework.beans.factory.annotation.Autowired;

  9. import org.springframework.stereotype.Service;

  10. import com.alibaba.fastjson.JSONObject;

  11. import com.github.pagehelper.PageHelper;

  12. import com.github.pagehelper.PageInfo;

  13. import com.suwei.sysMng.bean.esp.UserCatalog;

  14. import com.suwei.sysMng.dao.BorrowSubOrderDao;

  15. import com.suwei.sysMng.dao.CompanyDao;

  16. import com.suwei.sysMng.dao.DeptDao;

  17. import com.suwei.sysMng.dao.EspShareDao;

  18. import com.suwei.sysMng.dao.UserCatalogDao;

  19. import com.suwei.sysMng.service.BorrowSubOrderService;

  20. import com.suwei.sysMng.util.Constants;

  21. import com.suwei.sysMng.util.ModelResults;

  22. import com.suwei.sysMng.util.basicUtils.ValidateUtil;

  23. import com.suwei.sysMng.util.msgUTils.MsgInfo;

  24. import com.suwei.sysMng.vo.BorrowSubOrderVo;

  25. import com.suwei.sysMng.vo.CompanyVos;

  26. import com.suwei.sysMng.vo.DeptVos;

  27. @Service

  28. public class BorrowSubOrderServiceImpl implements BorrowSubOrderService {

  29.     @Autowired

  30.     private BorrowSubOrderDao borrowSubOrderDao;

  31.     public List<CompanyVos> findCompanyAndDepts(Map<String, Object> param){

  32.         List<CompanyVos> list=new ArrayList<CompanyVos>();

  33.         List<CompanyVos> companyVosList=borrowSubOrderDao.findCompanys(param);  

  34.         if(ValidateUtil.isNotEmpty(companyVosList)){

  35.             for(CompanyVos companyVo:companyVosList){

  36.                 CompanyVos companyVo2=new CompanyVos();

  37.                 companyVo2.setCompanyId(companyVo.getCompanyId());  

  38.                 companyVo2.setCompanyName(companyVo.getCompanyName());  

  39.                 Map<String, Object> paramMap=new HashMap<String, Object>();

  40.                 paramMap.put("companyId", companyVo.getCompanyId());

  41.                 paramMap.put("fDeptId", 0);

  42.                 companyVo2.setDeptVos(getDepts(paramMap));  

  43.                 list.add(companyVo2);  

  44.             }  

  45.         }  

  46.         return list;

  47.     }  

  48.     /**

  49. * @descript:递归部门

  50. * @param param

  51. * @return

  52. */

  53.     public List<DeptVos> getDepts(Map<String, Object> param){

  54.         List<DeptVos> deptVosList=new ArrayList<DeptVos>();

  55.         List<DeptVos> deptVos=borrowSubOrderDao.findAllDepts(param);  

  56.         if(ValidateUtil.isNotEmpty(deptVos)){

  57.             for(DeptVos deptVo:deptVos){

  58.                 DeptVos deptVo2=new DeptVos();

  59.                 deptVo2.setDeptId(deptVo.getDeptId());  

  60.                 deptVo2.setDeptName(deptVo.getDeptName());  

  61.                 Map<String, Object> paramMap=new HashMap<String, Object>();

  62.                 paramMap.put("fDeptId", deptVo.getDeptId());

  63.                 paramMap.put("companyId",deptVo.getCompanyId());

  64.                 deptVo2.setSubDeptVos(getDepts(paramMap));  

  65.                 deptVosList.add(deptVo2);  

  66.             }  

  67.         }  

  68.         return deptVosList;

  69.     }  

  70. }  

controller层：

[java]  view plain copy

  1. package com.suwei.sysMng.controller;

  2. import java.util.HashMap;

  3. import java.util.List;

  4. import java.util.Map;

  5. import java.util.Set;

  6. import javax.servlet.http.HttpServletRequest;

  7. import javax.servlet.http.HttpServletResponse;

  8. import org.apache.log4j.Logger;

  9. import org.springframework.beans.factory.annotation.Autowired;

  10. import org.springframework.stereotype.Controller;

  11. import org.springframework.web.bind.annotation.RequestMapping;

  12. import org.springframework.web.bind.annotation.RequestMethod;

  13. import com.alibaba.fastjson.JSONObject;

  14. import com.github.pagehelper.PageHelper;

  15. import com.github.pagehelper.PageInfo;

  16. import com.suwei.sysMng.bean.PtUser;

  17. import com.suwei.sysMng.bean.PtUserExt;

  18. import com.suwei.sysMng.service.BorrowOrderService;

  19. import com.suwei.sysMng.service.BorrowSubOrderService;

  20. import com.suwei.sysMng.util.CommonParamUtils;

  21. import com.suwei.sysMng.util.Constants;

  22. import com.suwei.sysMng.util.ModelResults;

  23. import com.suwei.sysMng.util.basicUtils.ValidateUtil;

  24. import com.suwei.sysMng.util.msgUTils.MsgInfo;

  25. import com.suwei.sysMng.vo.CompanyVos;

  26. @Controller

  27. @RequestMapping("/borrow")

  28. public class BorrowController extends BaseController {

  29.     // logger日志

  30.     private final static Logger logger = Logger.getLogger(BorrowController.class);

  31.     @Autowired

  32.     private BorrowSubOrderService borrowSubOrderService;

  33.     @RequestMapping(value = "/findCompanyAndDepts")

  34.     public void  findCompanyAndDepts(HttpServletRequest req, HttpServletResponse resp) {

  35.         ModelResults results = new ModelResults();

  36.         try {

  37.             PtUserExt pue = (PtUserExt) req.getSession().getAttribute(CommonParamUtils.LOGIN_USER_SESSION_NAME);  

  38.             Long userId = pue.getUserId();  

  39.             Set<String> roles=pue.getRoles();  

  40.             Map<String, Object> paramMap=new HashMap<String, Object>();

  41.             if(!roles.contains(Constants.ADMIN)){

  42.                               paramMap.put("userId", userId);

  43.                         }  

  44.                         List<CompanyVos> list=borrowSubOrderService.findCompanyAndDepts(paramMap);  

  45.                         results.setData(list);  

  46.                         results.setCode(MsgInfo.a_suc_code);  

  47.                         results.setMessage(MsgInfo.a_suc_msg);  

  48.                  }catch (Exception e) {

  49.                     e.printStackTrace();  

  50.                     results.setCode(MsgInfo.a_error_code);  

  51.                     results.setMessage(MsgInfo.a_error_msg);  

  52.                    results.printJson(results, resp, "");

  53.                 }  

  54.                  results.printJson(results, resp, "");}

  55.        }  

运行结果：

{"code":"200","data":[{"companyId":156,"companyName":"苏微","deptVos":[{"companyId":"","deptId":101,"deptName":"1部门1","subDeptVos":[{"companyId":"","deptId":102,"deptName":"1部门2","subDeptVos":[]},{"companyId":"","deptId":103,"deptName":"1部门2","subDeptVos":[]}]}]},{"companyId":157,"companyName":"毅泽","deptVos":[]},{"companyId":158,"companyName":"多伦","deptVos":[]},{"companyId":159,"companyName":"华为南京分部1","deptVos":[]},{"companyId":601,"companyName":"华为南京分部2","deptVos":[]},{"companyId":602,"companyName":"华为南京分部3","deptVos":[]},{"companyId":603,"companyName":"华为","deptVos":[]},{"companyId":1101,"companyName":"天一","deptVos":[]},{"companyId":1102,"companyName":"天二","deptVos":[]}],"end":"","message":"请求成功","page":"","pageSize":"","param":"","permissions":"","resultsCount":"","rowCount":"","rows":"","sql":"","start":""}

业务二：查询公司及子公司，部门及子部门

原型：

则跟上诉一样，只是递归公司，在公司里面再递归部门

1.创建公司实体（在公司的实体中添加部门的对象集合）和部门实体

ComDeptVo：

[java]  view plain copy

  1. package com.suwei.sysMng.vo;

  2. import java.util.List;

  3. public class ComDeptVo {

  4.     private Integer companyId;

  5.     private String companyName;

  6.     //父级公司id

  7.     private Integer parentId;

  8.     private List<ComDeptVo> subCompany;

  9.     private List<DeptVos> deptVos;

  10.     public Integer getCompanyId() {

  11.         return companyId;

  12.     }  

  13.     public void setCompanyId(Integer companyId) {

  14.         this.companyId = companyId;

  15.     }  

  16.     public String getCompanyName() {

  17.         return companyName;

  18.     }  

  19.     public void setCompanyName(String companyName) {

  20.         this.companyName = companyName;

  21.     }  

  22.     public Integer getParentId() {

  23.         return parentId;

  24.     }  

  25.     public void setParentId(Integer parentId) {

  26.         this.parentId = parentId;

  27.     }  

  28.     public List<ComDeptVo> getSubCompany() {

  29.         return subCompany;

  30.     }  

  31.     public void setSubCompany(List<ComDeptVo> subCompany) {

  32.         this.subCompany = subCompany;

  33.     }  

  34.     public List<DeptVos> getDeptVos() {

  35.         return deptVos;

  36.     }  

  37.     public void setDeptVos(List<DeptVos> deptVos) {

  38.         this.deptVos = deptVos;

  39.     }  

  40.     public String toString() {

  41.         return "ComDeptVo [companyId=" + companyId + ", companyName=" + companyName +",parentId="+parentId

  42.                 +",subCompany="+subCompany+", deptVos="+"]";

  43.     }    

  44. }  

备注：部门实体创建跟上诉一样

2.通过父级公司查询所有子公司：

[html]  view plain copy

  1. <!-- 获取公司部门信息 -->
  2.   <select id="findCompanyDept" parameterType="Map"  resultType="com.suwei.sysMng.vo.ComDeptVo">
  3.         select sc.id AS "companyId" , sc.company_name as "companyName", sc.parent_id as "parentId"  
  4.         from SJK_COMPANY sc  
  5.         inner  join SJK_USER_CATALOG uc on sc.tree_node_id = uc.tree_node_id
  6.         where sc.is_del=1 and uc.is_del=1 and uc.type=1
  7.         and sc.parent_id=#{parentId}
  8.         <if test="userId!=null and userId!=''  ">
  9.             and uc.user_id=#{userId}
  10.         </if>
  11.   </select>

dao层：

[java]  view plain copy

  1. List<ComDeptVo> findCompanyDept(Map<String, Object> param);  

service层：

[java]  view plain copy

  1. public List<ComDeptVo> findCompanyDept(Map<String, Object> param);

serviceImpl层：

[java]  view plain copy

  1. /**

  2. * @descript:递归公司，在公司里面递归部门

  3. * @param paramMap

  4. * @return

  5. */

  6.     public List<ComDeptVo> findCompanyDept(Map<String, Object> paramMap) {

  7.         List<ComDeptVo> list=new ArrayList<ComDeptVo>();

  8.         //查询公司

  9.         List<ComDeptVo> comDeptVosList=borrowSubOrderDao.findCompanyDept(paramMap);  

  10.         if(ValidateUtil.isNotEmpty(comDeptVosList)){

  11.             for(ComDeptVo comDeptVo:comDeptVosList){

  12.                 ComDeptVo vo=new ComDeptVo();

  13.                 vo.setCompanyId(comDeptVo.getCompanyId());  

  14.                 vo.setCompanyName(comDeptVo.getCompanyName());  

  15.                 vo.setParentId(comDeptVo.getParentId());  

  16.                 Map<String, Object> dataMap=new HashMap<String, Object>();

  17.                 dataMap.put("parentId", comDeptVo.getCompanyId());

  18.                 //通过父级公司递归公司

  19.                 vo.setSubCompany(findCompanyDept(dataMap));  

  20.                 Map<String, Object> dataDeptMap=new HashMap<String, Object>();

  21.                 dataDeptMap.put("companyId", comDeptVo.getCompanyId());

  22.                 dataDeptMap.put("fDeptId", 0);

  23.                 //通过父级部门递归部门

  24.                 vo.setDeptVos(getDepts(dataDeptMap));  

  25.                 list.add(vo);  

  26.             }  

  27.         }  

  28.         return list;

  29.     }  

  30.     /**

  31. * @descript:递归部门

  32. * @param param

  33. * @return

  34. */

  35.     public List<DeptVos> getDepts(Map<String, Object> param){

  36.         List<DeptVos> deptVosList=new ArrayList<DeptVos>();

  37.         List<DeptVos> deptVos=borrowSubOrderDao.findAllDepts(param);  

  38.         if(ValidateUtil.isNotEmpty(deptVos)){

  39.             for(DeptVos deptVo:deptVos){

  40.                 DeptVos deptVo2=new DeptVos();

  41.                 deptVo2.setDeptId(deptVo.getDeptId());  

  42.                 deptVo2.setDeptName(deptVo.getDeptName());  

  43.                 Map<String, Object> paramMap=new HashMap<String, Object>();

  44.                 paramMap.put("fDeptId", deptVo.getDeptId());

  45.                 paramMap.put("companyId",deptVo.getCompanyId());

  46.                 deptVo2.setSubDeptVos(getDepts(paramMap));  

  47.                 deptVosList.add(deptVo2);  

  48.             }  

  49.         }  

  50.         return deptVosList;

  51.     }  

controller层：

[java]  view plain copy

  1. @RequestMapping(value = "/findCompanyDept")
  2.     public void  findCompanyDept(HttpServletRequest req, HttpServletResponse resp) {
  3.         ModelResults results = new ModelResults();
  4.         try {
  5.             PtUserExt pue = (PtUserExt) req.getSession().getAttribute(CommonParamUtils.LOGIN_USER_SESSION_NAME);  
  6.             Long userId = pue.getUserId();  
  7.             Set<String> roles=pue.getRoles();  
  8.             Map<String, Object> paramMap=new HashMap<String, Object>();
  9.             if(!roles.contains(Constants.ADMIN)){
  10.                 paramMap.put("userId", userId);
  11.             }  
  12.             paramMap.put("parentId", 0);
  13.             List<ComDeptVo> list=borrowSubOrderService.findCompanyDept(paramMap);  
  14.             results.setData(list);  
  15.             results.setCode(MsgInfo.a_suc_code);  
  16.             results.setMessage(MsgInfo.a_suc_msg);  
  17.         } catch (Exception e) {
  18.             e.printStackTrace();  
  19.             results.setCode(MsgInfo.a_error_code);  
  20.             results.setMessage(MsgInfo.a_error_msg);  
  21.             results.printJson(results, resp, "");
  22.         }  
  23.         results.printJson(results, resp, "");
  24.     }  

运行效果：
{"code":"200","data":[{"companyId":156,"companyName":"苏微","deptVos":[{"companyId":"","deptId":101,"deptName":"1部门1","subDeptVos":[{"companyId":"","deptId":102,"deptName":"1部门2","subDeptVos":[]},{"companyId":"","deptId":103,"deptName":"1部门2","subDeptVos":[]}]}],"parentId":0,"subCompany":[{"companyId":157,"companyName":"毅泽","deptVos":[],"parentId":156,"subCompany":[{"companyId":159,"companyName":"华为南京分部1","deptVos":[],"parentId":157,"subCompany":[]}]},{"companyId":158,"companyName":"多伦","deptVos":[],"parentId":156,"subCompany":[{"companyId":601,"companyName":"华为南京分部2","deptVos":[],"parentId":158,"subCompany":[]}]},{"companyId":602,"companyName":"华为南京分部3","deptVos":[],"parentId":156,"subCompany":[]}]},{"companyId":603,"companyName":"华为","deptVos":[],"parentId":0,"subCompany":[]},{"companyId":1101,"companyName":"天一","deptVos":[],"parentId":0,"subCompany":[{"companyId":1102,"companyName":"天二","deptVos":[],"parentId":1101,"subCompany":[]}]}],"end":"","message":"请求成功","page":"","pageSize":"","param":"","permissions":"","resultsCount":"","rowCount":"","rows":"","sql":"","start":""}

备注：

1.首先一定要弄清楚公司和部门之间的关系，公司和子公司之间的关系。注意数据格式如：

公司表：

部门表：

2.在数据库设计中树形结构顶级目录id为0，故要查找子级，则根据顶级id=0递归查询

点赞
收藏

评论区

加载中...

相关推荐

MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1

文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s

Oracle 分组与拼接字符串同时使用

SELECTT.,ROWNUMIDFROM(SELECTT.EMPLID,T.NAME,T.BU,T.REALDEPART,T.FORMATDATE,SUM(T.S0)S0,MAX(UPDATETIME)CREATETIME,LISTAGG(TOCHAR(

MySQL部分从库上面因为大量的临时表tmp_table造成慢查询

背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_

皕杰报表之UUID

​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为

手写Java HashMap源码

HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22

MySQL总结(十一)子查询

!(https://oscimg.oschina.net/oscnet/upa344f41e81d3568e3310b5da00c57ced8ea.png)子查询1\.什么是子查询需求：查询开发部中有哪些员工selectfromemp;通