引自:https://blog.csdn.net/u014079773/article/details/53338116
业务要求:
查询公司下所有子部门及子部门:
原型:
1.创建公司和部门实体:
CompanyVos:
[java] view plain copy
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; 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
getDeptVos() { return deptVos;
}
public void setDeptVos(List
deptVos) { this.deptVos = deptVos;
}
public String toString() {
return "CompanyVos [companyId=" + companyId + ", companyName=" + companyName +", deptVos="+"]";
}
}
DeptVos:
[java] view plain copy
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
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
getSubDeptVos() { return subDeptVos;
}
public void setSubDeptVos(List
subDeptVos) { this.subDeptVos = subDeptVos;
}
public String toString() {
return "DeptVos [deptId=" + deptId + ", deptName=" + deptName
+ ", subDeptVos=" + subDeptVos + "]";
}
}
2.查询所有公司和该公司下所有部门及子部门:
[html] view plain copy
<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层:
[java] view plain copy
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
findCompanys(Map<String, Object> param); List
findAllDepts(Map<String, Object> param); }
service层:
[java] view plain copy
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
findCompanyAndDepts(Map<String, Object> param); }
serviceImpl层:
[java] view plain copy
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
findCompanyAndDepts(Map<String, Object> param){ List
list=new ArrayList (); List
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
getDepts(Map<String, Object> param){ List
deptVosList=new ArrayList (); List
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层:
[java] view plain copy
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
roles=pue.getRoles(); Map<String, Object> paramMap=new HashMap<String, Object>();
if(!roles.contains(Constants.ADMIN)){
paramMap.put("userId", userId);
}
List
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:
[java] view plain copy
package com.suwei.sysMng.vo;
import java.util.List;
public class ComDeptVo {
private Integer companyId;
private String companyName;
//父级公司id
private Integer parentId;
private List
subCompany; private List
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
getSubCompany() { return subCompany;
}
public void setSubCompany(List
subCompany) { this.subCompany = subCompany;
}
public List
getDeptVos() { return deptVos;
}
public void setDeptVos(List
deptVos) { this.deptVos = deptVos;
}
public String toString() {
return "ComDeptVo [companyId=" + companyId + ", companyName=" + companyName +",parentId="+parentId
+",subCompany="+subCompany+", deptVos="+"]";
}
}
备注:部门实体创建跟上诉一样
2.通过父级公司查询所有子公司:
[html] view plain copy
- <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层:
[java] view plain copy
- List
findCompanyDept(Map<String, Object> param);
service层:
[java] view plain copy
- public List
findCompanyDept(Map<String, Object> param);
serviceImpl层:
[java] view plain copy
/**
* @descript:递归公司,在公司里面递归部门
* @param paramMap
* @return
*/
public List
findCompanyDept(Map<String, Object> paramMap) { List
list=new ArrayList (); //查询公司
List
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
getDepts(Map<String, Object> param){ List
deptVosList=new ArrayList (); List
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层:
[java] view plain copy
- @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
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
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递归查询