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

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

业务要求:

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

原型:

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

CompanyVos:

[java] view plain copy
 
  1. package com.suwei.sysMng.vo;  
  2.   
  3. import java.io.Serializable;  
  4. import java.util.List;  
  5.   
  6. public class CompanyVos  implements Serializable{  
  7.   
  8.     /** 
  9.      *  
  10.      */  
  11.     private static final long serialVersionUID = 5617344073236043292L;  
  12.     private Long companyId;  
  13.     private String companyName;  
  14.     private List<DeptVos> deptVos;  
  15.     public Long getCompanyId() {  
  16.         return companyId;  
  17.     }  
  18.     public void setCompanyId(Long companyId) {  
  19.         this.companyId = companyId;  
  20.     }  
  21.     public String getCompanyName() {  
  22.         return companyName;  
  23.     }  
  24.     public void setCompanyName(String companyName) {  
  25.         this.companyName = companyName;  
  26.     }  
  27.       
  28.       
  29.     public List<DeptVos> getDeptVos() {  
  30.         return deptVos;  
  31.     }  
  32.     public void setDeptVos(List<DeptVos> deptVos) {  
  33.         this.deptVos = deptVos;  
  34.     }  
  35.       
  36.     public String toString() {  
  37.         return "CompanyVos [companyId=" + companyId + ", companyName=" + companyName +", deptVos="+"]";  
  38.     }  
  39.       
  40.       
  41.       
  42. }  


DeptVos:

[java] view plain copy
 
  1. package com.suwei.sysMng.vo;  
  2.   
  3. import java.io.Serializable;  
  4. import java.util.List;  
  5.   
  6. public class DeptVos  implements Serializable{  
  7.   
  8.     /** 
  9.      *  
  10.      */  
  11.     private static final long serialVersionUID = 7648136453963080696L;  
  12.     private Long companyId;  
  13.     private Long fDeptId;  
  14.     private Long deptId;  
  15.     private String deptName;  
  16.     private List<DeptVos> subDeptVos;  
  17.       
  18.       
  19.     public Long getCompanyId() {  
  20.         return companyId;  
  21.     }  
  22.     public void setCompanyId(Long companyId) {  
  23.         this.companyId = companyId;  
  24.     }  
  25.     public Long getfDeptId() {  
  26.         return fDeptId;  
  27.     }  
  28.     public void setfDeptId(Long fDeptId) {  
  29.         this.fDeptId = fDeptId;  
  30.     }  
  31.     public Long getDeptId() {  
  32.         return deptId;  
  33.     }  
  34.     public void setDeptId(Long deptId) {  
  35.         this.deptId = deptId;  
  36.     }  
  37.     public String getDeptName() {  
  38.         return deptName;  
  39.     }  
  40.     public void setDeptName(String deptName) {  
  41.         this.deptName = deptName;  
  42.     }  
  43.     public List<DeptVos> getSubDeptVos() {  
  44.         return subDeptVos;  
  45.     }  
  46.     public void setSubDeptVos(List<DeptVos> subDeptVos) {  
  47.         this.subDeptVos = subDeptVos;  
  48.     }  
  49.       
  50.     public String toString() {  
  51.         return "DeptVos [deptId=" + deptId + ", deptName=" + deptName  
  52.                 + ", subDeptVos=" + subDeptVos + "]";  
  53.     }  
  54.       
  55.       
  56. }  


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.       
  11.     <select id="findAllDepts"   parameterType="map"  resultType="com.suwei.sysMng.vo.DeptVos">  
  12.         select pd.dept_id as "deptId",pd.dept_name as "deptName" ,pd.fdept_id as "fDeptId" , sc.id as "companyId"  
  13.         from PT_DEPT    pd  
  14.         inner join SJK_COMPANY sc on pd.company_id=sc.id  
  15.         where  1=1  
  16.          and  sc.is_del=1  
  17.          and  pd.fdept_id=#{fDeptId}  and pd.company_id=#{companyId}  
  18.     </select>  


3.dao层:

[java] view plain copy
 
  1. package com.suwei.sysMng.dao;  
  2.   
  3. import java.util.List;  
  4. import java.util.Map;  
  5.   
  6. import com.suwei.sysMng.vo.CompanyVos;  
  7. import com.suwei.sysMng.vo.DeptVos;  
  8.   
  9. public interface BorrowSubOrderDao {  
  10.       
  11.       
  12.     List<CompanyVos> findCompanys(Map<String, Object> param);  
  13.       
  14.       
  15.     List<DeptVos> findAllDepts(Map<String, Object> param);  
  16.       
  17.       
  18. }  


service层:

[java] view plain copy
 
  1. package com.suwei.sysMng.service;  
  2.   
  3. import java.util.List;  
  4. import java.util.Map;  
  5. import java.util.Set;  
  6.   
  7. import com.alibaba.fastjson.JSONObject;  
  8. import com.suwei.sysMng.util.ModelResults;  
  9. import com.suwei.sysMng.vo.CompanyDeptVo;  
  10. import com.suwei.sysMng.vo.CompanyVos;  
  11. import com.suwei.sysMng.vo.DeptVos;  
  12.   
  13. public interface BorrowSubOrderService {  
  14.       
  15.        public List<CompanyVos> findCompanyAndDepts(Map<String, Object> param);  
  16.           
  17.       
  18. }  


serviceImpl层:

[java] view plain copy
 
  1. package com.suwei.sysMng.service.impl;  
  2.   
  3. import java.util.ArrayList;  
  4. import java.util.HashMap;  
  5. import java.util.Iterator;  
  6. import java.util.List;  
  7. import java.util.Map;  
  8. import java.util.Set;  
  9.   
  10. import org.springframework.beans.factory.annotation.Autowired;  
  11. import org.springframework.stereotype.Service;  
  12.   
  13. import com.alibaba.fastjson.JSONObject;  
  14. import com.github.pagehelper.PageHelper;  
  15. import com.github.pagehelper.PageInfo;  
  16. import com.suwei.sysMng.bean.esp.UserCatalog;  
  17. import com.suwei.sysMng.dao.BorrowSubOrderDao;  
  18. import com.suwei.sysMng.dao.CompanyDao;  
  19. import com.suwei.sysMng.dao.DeptDao;  
  20. import com.suwei.sysMng.dao.EspShareDao;  
  21. import com.suwei.sysMng.dao.UserCatalogDao;  
  22. import com.suwei.sysMng.service.BorrowSubOrderService;  
  23. import com.suwei.sysMng.util.Constants;  
  24. import com.suwei.sysMng.util.ModelResults;  
  25. import com.suwei.sysMng.util.basicUtils.ValidateUtil;  
  26. import com.suwei.sysMng.util.msgUTils.MsgInfo;  
  27. import com.suwei.sysMng.vo.BorrowSubOrderVo;  
  28. import com.suwei.sysMng.vo.CompanyVos;  
  29. import com.suwei.sysMng.vo.DeptVos;  
  30.   
  31. @Service  
  32. public class BorrowSubOrderServiceImpl implements BorrowSubOrderService {  
  33.     @Autowired  
  34.     private BorrowSubOrderDao borrowSubOrderDao;  
  35.       
  36.     public List<CompanyVos> findCompanyAndDepts(Map<String, Object> param){  
  37.         List<CompanyVos> list=new ArrayList<CompanyVos>();  
  38.         List<CompanyVos> companyVosList=borrowSubOrderDao.findCompanys(param);  
  39.         if(ValidateUtil.isNotEmpty(companyVosList)){  
  40.             for(CompanyVos companyVo:companyVosList){  
  41.                 CompanyVos companyVo2=new CompanyVos();  
  42.                 companyVo2.setCompanyId(companyVo.getCompanyId());  
  43.                 companyVo2.setCompanyName(companyVo.getCompanyName());  
  44.                 Map<String, Object> paramMap=new HashMap<String, Object>();  
  45.                 paramMap.put("companyId", companyVo.getCompanyId());  
  46.                 paramMap.put("fDeptId", 0);  
  47.                 companyVo2.setDeptVos(getDepts(paramMap));  
  48.                 list.add(companyVo2);  
  49.             }  
  50.         }  
  51.         return list;  
  52.     }  
  53.       
  54.     /** 
  55.      * @descript:递归部门 
  56.      * @param param 
  57.      * @return 
  58.      */  
  59.     public List<DeptVos> getDepts(Map<String, Object> param){  
  60.         List<DeptVos> deptVosList=new ArrayList<DeptVos>();  
  61.         List<DeptVos> deptVos=borrowSubOrderDao.findAllDepts(param);  
  62.         if(ValidateUtil.isNotEmpty(deptVos)){  
  63.             for(DeptVos deptVo:deptVos){  
  64.                 DeptVos deptVo2=new DeptVos();  
  65.                 deptVo2.setDeptId(deptVo.getDeptId());  
  66.                 deptVo2.setDeptName(deptVo.getDeptName());  
  67.                 Map<String, Object> paramMap=new HashMap<String, Object>();  
  68.                 paramMap.put("fDeptId", deptVo.getDeptId());  
  69.                 paramMap.put("companyId",deptVo.getCompanyId());  
  70.                 deptVo2.setSubDeptVos(getDepts(paramMap));  
  71.                 deptVosList.add(deptVo2);  
  72.             }  
  73.         }  
  74.         return deptVosList;  
  75.     }  
  76.       
  77.   
  78. }  


controller层:

[java] view plain copy
 
  1. package com.suwei.sysMng.controller;  
  2.   
  3. import java.util.HashMap;  
  4. import java.util.List;  
  5. import java.util.Map;  
  6. import java.util.Set;  
  7.   
  8. import javax.servlet.http.HttpServletRequest;  
  9. import javax.servlet.http.HttpServletResponse;  
  10.   
  11. import org.apache.log4j.Logger;  
  12. import org.springframework.beans.factory.annotation.Autowired;  
  13. import org.springframework.stereotype.Controller;  
  14. import org.springframework.web.bind.annotation.RequestMapping;  
  15. import org.springframework.web.bind.annotation.RequestMethod;  
  16.   
  17. import com.alibaba.fastjson.JSONObject;  
  18. import com.github.pagehelper.PageHelper;  
  19. import com.github.pagehelper.PageInfo;  
  20. import com.suwei.sysMng.bean.PtUser;  
  21. import com.suwei.sysMng.bean.PtUserExt;  
  22. import com.suwei.sysMng.service.BorrowOrderService;  
  23. import com.suwei.sysMng.service.BorrowSubOrderService;  
  24. import com.suwei.sysMng.util.CommonParamUtils;  
  25. import com.suwei.sysMng.util.Constants;  
  26. import com.suwei.sysMng.util.ModelResults;  
  27. import com.suwei.sysMng.util.basicUtils.ValidateUtil;  
  28. import com.suwei.sysMng.util.msgUTils.MsgInfo;  
  29. import com.suwei.sysMng.vo.CompanyVos;  
  30.   
  31. @Controller  
  32. @RequestMapping("/borrow")  
  33. public class BorrowController extends BaseController {  
  34.     // logger日志  
  35.     private final static Logger logger = Logger.getLogger(BorrowController.class);  
  36.     @Autowired  
  37.     private BorrowSubOrderService borrowSubOrderService;  
  38.       
  39.     @RequestMapping(value = "/findCompanyAndDepts")  
  40.     public void  findCompanyAndDepts(HttpServletRequest req, HttpServletResponse resp) {  
  41.         ModelResults results = new ModelResults();  
  42.         try {  
  43.             PtUserExt pue = (PtUserExt) req.getSession().getAttribute(CommonParamUtils.LOGIN_USER_SESSION_NAME);  
  44.             Long userId = pue.getUserId();  
  45.             Set<String> roles=pue.getRoles();  
  46.             Map<String, Object> paramMap=new HashMap<String, Object>();  
  47.             if(!roles.contains(Constants.ADMIN)){  
  48.                               paramMap.put("userId", userId);  
  49.                         }  
  50.                         List<CompanyVos> list=borrowSubOrderService.findCompanyAndDepts(paramMap);  
  51.                         results.setData(list);  
  52.                         results.setCode(MsgInfo.a_suc_code);  
  53.                         results.setMessage(MsgInfo.a_suc_msg);  
  54.                  }catch (Exception e) {  
  55.                     e.printStackTrace();  
  56.                     results.setCode(MsgInfo.a_error_code);  
  57.                     results.setMessage(MsgInfo.a_error_msg);  
  58.                    results.printJson(results, resp, "");  
  59.                 }  
  60.                  results.printJson(results, resp, "");}  
  61.        }  


运行结果:

{"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.   
  3. import java.util.List;  
  4.   
  5. public class ComDeptVo {  
  6.   
  7.     private Integer companyId;  
  8.     private String companyName;  
  9.     //父级公司id  
  10.     private Integer parentId;  
  11.     private List<ComDeptVo> subCompany;  
  12.     private List<DeptVos> deptVos;  
  13.       
  14.     public Integer getCompanyId() {  
  15.         return companyId;  
  16.     }  
  17.     public void setCompanyId(Integer companyId) {  
  18.         this.companyId = companyId;  
  19.     }  
  20.     public String getCompanyName() {  
  21.         return companyName;  
  22.     }  
  23.     public void setCompanyName(String companyName) {  
  24.         this.companyName = companyName;  
  25.     }  
  26.     public Integer getParentId() {  
  27.         return parentId;  
  28.     }  
  29.     public void setParentId(Integer parentId) {  
  30.         this.parentId = parentId;  
  31.     }  
  32.       
  33.     public List<ComDeptVo> getSubCompany() {  
  34.         return subCompany;  
  35.     }  
  36.     public void setSubCompany(List<ComDeptVo> subCompany) {  
  37.         this.subCompany = subCompany;  
  38.     }  
  39.     public List<DeptVos> getDeptVos() {  
  40.         return deptVos;  
  41.     }  
  42.     public void setDeptVos(List<DeptVos> deptVos) {  
  43.         this.deptVos = deptVos;  
  44.     }  
  45.     public String toString() {  
  46.         return "ComDeptVo [companyId=" + companyId + ", companyName=" + companyName +",parentId="+parentId  
  47.                 +",subCompany="+subCompany+", deptVos="+"]";  
  48.     }    
  49.       
  50. }  

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

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.                   
  21.                 Map<String, Object> dataDeptMap=new HashMap<String, Object>();  
  22.                 dataDeptMap.put("companyId", comDeptVo.getCompanyId());  
  23.                 dataDeptMap.put("fDeptId", 0);  
  24.                 //通过父级部门递归部门  
  25.                 vo.setDeptVos(getDepts(dataDeptMap));  
  26.                 list.add(vo);  
  27.             }  
  28.         }  
  29.         return list;  
  30.     }  
  31.       
  32.     /** 
  33.      * @descript:递归部门 
  34.      * @param param 
  35.      * @return 
  36.      */  
  37.     public List<DeptVos> getDepts(Map<String, Object> param){  
  38.         List<DeptVos> deptVosList=new ArrayList<DeptVos>();  
  39.         List<DeptVos> deptVos=borrowSubOrderDao.findAllDepts(param);  
  40.         if(ValidateUtil.isNotEmpty(deptVos)){  
  41.             for(DeptVos deptVo:deptVos){  
  42.                 DeptVos deptVo2=new DeptVos();  
  43.                 deptVo2.setDeptId(deptVo.getDeptId());  
  44.                 deptVo2.setDeptName(deptVo.getDeptName());  
  45.                 Map<String, Object> paramMap=new HashMap<String, Object>();  
  46.                 paramMap.put("fDeptId", deptVo.getDeptId());  
  47.                 paramMap.put("companyId",deptVo.getCompanyId());  
  48.                 deptVo2.setSubDeptVos(getDepts(paramMap));  
  49.                 deptVosList.add(deptVo2);  
  50.             }  
  51.         }  
  52.         return deptVosList;  
  53.     }  
  54.       

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递归查询

原文地址:https://www.cnblogs.com/xxj-bigshow/p/9089468.html