java递归查询公司下所有部门及子部门
引自:https://blog.csdn.net/u014079773/article/details/53338116
业务要求:
查询公司下所有子部门及子部门:
原型:
1.创建公司和部门实体:
CompanyVos:
- package com.suwei.sysMng.vo;
- import java.io.Serializable;
- import java.util.List;
- public class CompanyVos implements Serializable{
- /**
- *
- */
- private static final long serialVersionUID = 5617344073236043292L;
- private Long companyId;
- private String companyName;
- private List<DeptVos> deptVos;
- public Long getCompanyId() {
- return companyId;
- }
- public void setCompanyId(Long companyId) {
- this.companyId = companyId;
- }
- public String getCompanyName() {
- return companyName;
- }
- public void setCompanyName(String companyName) {
- this.companyName = companyName;
- }
- public List<DeptVos> getDeptVos() {
- return deptVos;
- }
- public void setDeptVos(List<DeptVos> deptVos) {
- this.deptVos = deptVos;
- }
- public String toString() {
- return "CompanyVos [companyId=" + companyId + ", companyName=" + companyName +", deptVos="+"]";
- }
- }
DeptVos:
- package com.suwei.sysMng.vo;
- import java.io.Serializable;
- import java.util.List;
- public class DeptVos implements Serializable{
- /**
- *
- */
- private static final long serialVersionUID = 7648136453963080696L;
- private Long companyId;
- private Long fDeptId;
- private Long deptId;
- private String deptName;
- private List<DeptVos> subDeptVos;
- public Long getCompanyId() {
- return companyId;
- }
- public void setCompanyId(Long companyId) {
- this.companyId = companyId;
- }
- public Long getfDeptId() {
- return fDeptId;
- }
- public void setfDeptId(Long fDeptId) {
- this.fDeptId = fDeptId;
- }
- public Long getDeptId() {
- return deptId;
- }
- public void setDeptId(Long deptId) {
- this.deptId = deptId;
- }
- public String getDeptName() {
- return deptName;
- }
- public void setDeptName(String deptName) {
- this.deptName = deptName;
- }
- public List<DeptVos> getSubDeptVos() {
- return subDeptVos;
- }
- public void setSubDeptVos(List<DeptVos> subDeptVos) {
- this.subDeptVos = subDeptVos;
- }
- public String toString() {
- return "DeptVos [deptId=" + deptId + ", deptName=" + deptName
- + ", subDeptVos=" + subDeptVos + "]";
- }
- }
2.查询所有公司和该公司下所有部门及子部门:
- <select id="findCompanys" parameterType="map" resultType="com.suwei.sysMng.vo.CompanyVos">
- select sc.id AS "companyId" , sc.company_name as "companyName"
- from SJK_COMPANY sc
- inner join SJK_USER_CATALOG uc on sc.tree_node_id = uc.tree_node_id
- where sc.is_del=1 and uc.is_del=1 and uc.type=1
- <if test="userId!=null and userId!='' ">
- and uc.user_id=#{userId}
- </if>
- </select>
- <select id="findAllDepts" parameterType="map" resultType="com.suwei.sysMng.vo.DeptVos">
- select pd.dept_id as "deptId",pd.dept_name as "deptName" ,pd.fdept_id as "fDeptId" , sc.id as "companyId"
- from PT_DEPT pd
- inner join SJK_COMPANY sc on pd.company_id=sc.id
- where 1=1
- and sc.is_del=1
- and pd.fdept_id=#{fDeptId} and pd.company_id=#{companyId}
- </select>
3.dao层:
- package com.suwei.sysMng.dao;
- import java.util.List;
- import java.util.Map;
- import com.suwei.sysMng.vo.CompanyVos;
- import com.suwei.sysMng.vo.DeptVos;
- public interface BorrowSubOrderDao {
- List<CompanyVos> findCompanys(Map<String, Object> param);
- List<DeptVos> findAllDepts(Map<String, Object> param);
- }
service层:
- package com.suwei.sysMng.service;
- import java.util.List;
- import java.util.Map;
- import java.util.Set;
- import com.alibaba.fastjson.JSONObject;
- import com.suwei.sysMng.util.ModelResults;
- import com.suwei.sysMng.vo.CompanyDeptVo;
- import com.suwei.sysMng.vo.CompanyVos;
- import com.suwei.sysMng.vo.DeptVos;
- public interface BorrowSubOrderService {
- public List<CompanyVos> findCompanyAndDepts(Map<String, Object> param);
- }
serviceImpl层:
- package com.suwei.sysMng.service.impl;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.Iterator;
- import java.util.List;
- import java.util.Map;
- import java.util.Set;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
- import com.alibaba.fastjson.JSONObject;
- import com.github.pagehelper.PageHelper;
- import com.github.pagehelper.PageInfo;
- import com.suwei.sysMng.bean.esp.UserCatalog;
- import com.suwei.sysMng.dao.BorrowSubOrderDao;
- import com.suwei.sysMng.dao.CompanyDao;
- import com.suwei.sysMng.dao.DeptDao;
- import com.suwei.sysMng.dao.EspShareDao;
- import com.suwei.sysMng.dao.UserCatalogDao;
- import com.suwei.sysMng.service.BorrowSubOrderService;
- import com.suwei.sysMng.util.Constants;
- import com.suwei.sysMng.util.ModelResults;
- import com.suwei.sysMng.util.basicUtils.ValidateUtil;
- import com.suwei.sysMng.util.msgUTils.MsgInfo;
- import com.suwei.sysMng.vo.BorrowSubOrderVo;
- import com.suwei.sysMng.vo.CompanyVos;
- import com.suwei.sysMng.vo.DeptVos;
- @Service
- public class BorrowSubOrderServiceImpl implements BorrowSubOrderService {
- @Autowired
- private BorrowSubOrderDao borrowSubOrderDao;
- public List<CompanyVos> findCompanyAndDepts(Map<String, Object> param){
- List<CompanyVos> list=new ArrayList<CompanyVos>();
- List<CompanyVos> companyVosList=borrowSubOrderDao.findCompanys(param);
- if(ValidateUtil.isNotEmpty(companyVosList)){
- for(CompanyVos companyVo:companyVosList){
- CompanyVos companyVo2=new CompanyVos();
- companyVo2.setCompanyId(companyVo.getCompanyId());
- companyVo2.setCompanyName(companyVo.getCompanyName());
- Map<String, Object> paramMap=new HashMap<String, Object>();
- paramMap.put("companyId", companyVo.getCompanyId());
- paramMap.put("fDeptId", 0);
- companyVo2.setDeptVos(getDepts(paramMap));
- list.add(companyVo2);
- }
- }
- return list;
- }
- /**
- * @descript:递归部门
- * @param param
- * @return
- */
- public List<DeptVos> getDepts(Map<String, Object> param){
- List<DeptVos> deptVosList=new ArrayList<DeptVos>();
- List<DeptVos> deptVos=borrowSubOrderDao.findAllDepts(param);
- if(ValidateUtil.isNotEmpty(deptVos)){
- for(DeptVos deptVo:deptVos){
- DeptVos deptVo2=new DeptVos();
- deptVo2.setDeptId(deptVo.getDeptId());
- deptVo2.setDeptName(deptVo.getDeptName());
- Map<String, Object> paramMap=new HashMap<String, Object>();
- paramMap.put("fDeptId", deptVo.getDeptId());
- paramMap.put("companyId",deptVo.getCompanyId());
- deptVo2.setSubDeptVos(getDepts(paramMap));
- deptVosList.add(deptVo2);
- }
- }
- return deptVosList;
- }
- }
controller层:
- package com.suwei.sysMng.controller;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import java.util.Set;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.log4j.Logger;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Controller;
- import org.springframework.web.bind.annotation.RequestMapping;
- import org.springframework.web.bind.annotation.RequestMethod;
- import com.alibaba.fastjson.JSONObject;
- import com.github.pagehelper.PageHelper;
- import com.github.pagehelper.PageInfo;
- import com.suwei.sysMng.bean.PtUser;
- import com.suwei.sysMng.bean.PtUserExt;
- import com.suwei.sysMng.service.BorrowOrderService;
- import com.suwei.sysMng.service.BorrowSubOrderService;
- import com.suwei.sysMng.util.CommonParamUtils;
- import com.suwei.sysMng.util.Constants;
- import com.suwei.sysMng.util.ModelResults;
- import com.suwei.sysMng.util.basicUtils.ValidateUtil;
- import com.suwei.sysMng.util.msgUTils.MsgInfo;
- import com.suwei.sysMng.vo.CompanyVos;
- @Controller
- @RequestMapping("/borrow")
- public class BorrowController extends BaseController {
- // logger日志
- private final static Logger logger = Logger.getLogger(BorrowController.class);
- @Autowired
- private BorrowSubOrderService borrowSubOrderService;
- @RequestMapping(value = "/findCompanyAndDepts")
- public void findCompanyAndDepts(HttpServletRequest req, HttpServletResponse resp) {
- ModelResults results = new ModelResults();
- try {
- PtUserExt pue = (PtUserExt) req.getSession().getAttribute(CommonParamUtils.LOGIN_USER_SESSION_NAME);
- Long userId = pue.getUserId();
- Set<String> roles=pue.getRoles();
- Map<String, Object> paramMap=new HashMap<String, Object>();
- if(!roles.contains(Constants.ADMIN)){
- paramMap.put("userId", userId);
- }
- List<CompanyVos> list=borrowSubOrderService.findCompanyAndDepts(paramMap);
- results.setData(list);
- results.setCode(MsgInfo.a_suc_code);
- results.setMessage(MsgInfo.a_suc_msg);
- }catch (Exception e) {
- e.printStackTrace();
- results.setCode(MsgInfo.a_error_code);
- results.setMessage(MsgInfo.a_error_msg);
- results.printJson(results, resp, "");
- }
- results.printJson(results, resp, "");}
- }
运行结果:
{"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:
- package com.suwei.sysMng.vo;
- import java.util.List;
- public class ComDeptVo {
- private Integer companyId;
- private String companyName;
- //父级公司id
- private Integer parentId;
- private List<ComDeptVo> subCompany;
- private List<DeptVos> deptVos;
- public Integer getCompanyId() {
- return companyId;
- }
- public void setCompanyId(Integer companyId) {
- this.companyId = companyId;
- }
- public String getCompanyName() {
- return companyName;
- }
- public void setCompanyName(String companyName) {
- this.companyName = companyName;
- }
- public Integer getParentId() {
- return parentId;
- }
- public void setParentId(Integer parentId) {
- this.parentId = parentId;
- }
- public List<ComDeptVo> getSubCompany() {
- return subCompany;
- }
- public void setSubCompany(List<ComDeptVo> subCompany) {
- this.subCompany = subCompany;
- }
- public List<DeptVos> getDeptVos() {
- return deptVos;
- }
- public void setDeptVos(List<DeptVos> deptVos) {
- this.deptVos = deptVos;
- }
- public String toString() {
- return "ComDeptVo [companyId=" + companyId + ", companyName=" + companyName +",parentId="+parentId
- +",subCompany="+subCompany+", deptVos="+"]";
- }
- }
备注:部门实体创建跟上诉一样
2.通过父级公司查询所有子公司:
- <!-- 获取公司部门信息 -->
- <select id="findCompanyDept" parameterType="Map" resultType="com.suwei.sysMng.vo.ComDeptVo">
- select sc.id AS "companyId" , sc.company_name as "companyName", sc.parent_id as "parentId"
- from SJK_COMPANY sc
- inner join SJK_USER_CATALOG uc on sc.tree_node_id = uc.tree_node_id
- where sc.is_del=1 and uc.is_del=1 and uc.type=1
- and sc.parent_id=#{parentId}
- <if test="userId!=null and userId!='' ">
- and uc.user_id=#{userId}
- </if>
- </select>
dao层:
- List<ComDeptVo> findCompanyDept(Map<String, Object> param);
service层:
- public List<ComDeptVo> findCompanyDept(Map<String, Object> param);
serviceImpl层:
- /**
- * @descript:递归公司,在公司里面递归部门
- * @param paramMap
- * @return
- */
- public List<ComDeptVo> findCompanyDept(Map<String, Object> paramMap) {
- List<ComDeptVo> list=new ArrayList<ComDeptVo>();
- //查询公司
- List<ComDeptVo> comDeptVosList=borrowSubOrderDao.findCompanyDept(paramMap);
- if(ValidateUtil.isNotEmpty(comDeptVosList)){
- for(ComDeptVo comDeptVo:comDeptVosList){
- ComDeptVo vo=new ComDeptVo();
- vo.setCompanyId(comDeptVo.getCompanyId());
- vo.setCompanyName(comDeptVo.getCompanyName());
- vo.setParentId(comDeptVo.getParentId());
- Map<String, Object> dataMap=new HashMap<String, Object>();
- dataMap.put("parentId", comDeptVo.getCompanyId());
- //通过父级公司递归公司
- vo.setSubCompany(findCompanyDept(dataMap));
- Map<String, Object> dataDeptMap=new HashMap<String, Object>();
- dataDeptMap.put("companyId", comDeptVo.getCompanyId());
- dataDeptMap.put("fDeptId", 0);
- //通过父级部门递归部门
- vo.setDeptVos(getDepts(dataDeptMap));
- list.add(vo);
- }
- }
- return list;
- }
- /**
- * @descript:递归部门
- * @param param
- * @return
- */
- public List<DeptVos> getDepts(Map<String, Object> param){
- List<DeptVos> deptVosList=new ArrayList<DeptVos>();
- List<DeptVos> deptVos=borrowSubOrderDao.findAllDepts(param);
- if(ValidateUtil.isNotEmpty(deptVos)){
- for(DeptVos deptVo:deptVos){
- DeptVos deptVo2=new DeptVos();
- deptVo2.setDeptId(deptVo.getDeptId());
- deptVo2.setDeptName(deptVo.getDeptName());
- Map<String, Object> paramMap=new HashMap<String, Object>();
- paramMap.put("fDeptId", deptVo.getDeptId());
- paramMap.put("companyId",deptVo.getCompanyId());
- deptVo2.setSubDeptVos(getDepts(paramMap));
- deptVosList.add(deptVo2);
- }
- }
- return deptVosList;
- }
controller层:
- @RequestMapping(value = "/findCompanyDept")
- public void findCompanyDept(HttpServletRequest req, HttpServletResponse resp) {
- ModelResults results = new ModelResults();
- try {
- PtUserExt pue = (PtUserExt) req.getSession().getAttribute(CommonParamUtils.LOGIN_USER_SESSION_NAME);
- Long userId = pue.getUserId();
- Set<String> roles=pue.getRoles();
- Map<String, Object> paramMap=new HashMap<String, Object>();
- if(!roles.contains(Constants.ADMIN)){
- paramMap.put("userId", userId);
- }
- paramMap.put("parentId", 0);
- List<ComDeptVo> list=borrowSubOrderService.findCompanyDept(paramMap);
- results.setData(list);
- results.setCode(MsgInfo.a_suc_code);
- results.setMessage(MsgInfo.a_suc_msg);
- } catch (Exception e) {
- e.printStackTrace();
- results.setCode(MsgInfo.a_error_code);
- results.setMessage(MsgInfo.a_error_msg);
- results.printJson(results, resp, "");
- }
- results.printJson(results, resp, "");
- }
运行效果:
{"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递归查询