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

Wesley13
• 阅读 717

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

业务要求:

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

原型:

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

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;

  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 getDeptVos() {

  25.         return deptVos;

  26.     }  

  27.     public void setDeptVos(List 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 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 getSubDeptVos() {

  39.         return subDeptVos;

  40.     }  

  41.     public void setSubDeptVos(List 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 findCompanys(Map<String, Object> param);  

  8.     List 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 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 findCompanyAndDepts(Map<String, Object> param){

  32.         List list=new ArrayList();

  33.         List 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 getDepts(Map<String, Object> param){

  54.         List deptVosList=new ArrayList();

  55.         List 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 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 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":""}

业务二:查询公司及子公司,部门及子部门

原型:

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

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

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 subCompany;

  9.     private List 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 getSubCompany() {

  29.         return subCompany;

  30.     }  

  31.     public void setSubCompany(List subCompany) {

  32.         this.subCompany = subCompany;

  33.     }  

  34.     public List getDeptVos() {

  35.         return deptVos;

  36.     }  

  37.     public void setDeptVos(List 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.   <select id="findCompanyDept" parameterType="Map"  resultType="com.suwei.sysMng.vo.ComDeptVo">
  2.         select sc.id AS "companyId" , sc.company_name as "companyName", sc.parent_id as "parentId"  
  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.         and sc.parent_id=#{parentId}
  7.         <if test="userId!=null and userId!=''  ">
  8.             and uc.user_id=#{userId}
  9.         </if>
  10.   </select>

dao层:

[java]  view plain copy

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

  

service层:

[java]  view plain copy

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

serviceImpl层:

[java]  view plain copy

  1. /**

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

  3. * @param paramMap

  4. * @return

  5. */

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

  7.         List list=new ArrayList();

  8.         //查询公司

  9.         List 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 getDepts(Map<String, Object> param){

  36.         List deptVosList=new ArrayList();

  37.         List 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 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 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.首先一定要弄清楚公司和部门之间的关系,公司和子公司之间的关系。注意数据格式如:

公司表:

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

部门表:

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

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

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
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
Easter79 Easter79
3年前
swap空间的增减方法
(1)增大swap空间去激活swap交换区:swapoff v /dev/vg00/lvswap扩展交换lv:lvextend L 10G /dev/vg00/lvswap重新生成swap交换区:mkswap /dev/vg00/lvswap激活新生成的交换区:swapon v /dev/vg00/lvswap
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Wesley13 Wesley13
3年前
MySQL总结(十一)子查询
!(https://oscimg.oschina.net/oscnet/upa344f41e81d3568e3310b5da00c57ced8ea.png)子查询1\.什么是子查询需求:查询开发部中有哪些员工selectfromemp;通
Wesley13 Wesley13
3年前
MySQL中实现递归查询
对于数据库中的树形结构数据,如部门表,有时候,我们需要知道某部门的所有下属部分或者某部分的所有上级部门,这时候就需要用到mysql的递归查询1、创建表按CtrlC复制代码按CtrlC复制代码2、初始数据!复制代码(https://oscimg.oschina.net/oscnet/435661e1cc6d1
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
3年前
MySQL 实战
项目七:各部门工资最高的员工(难度:中等)创建Employee 表,包含所有员工信息,每个员工有其对应的 Id,salary和departmentId。|Id|Name|Salary|DepartmentId|
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这