查询树的时候可以使用mybatis的collection递归查询
public class OrgTree{
private String orgId;// 部门代码
private String orgName;// 部门中文名称
private List<OrgTree> children;//
public String getOrgName() {
return orgName;
}
public void setOrgName(String orgName) {
this.orgName= orgName;
}
public String getOrgId() {
return orgId;
}
public void setOrgId(String orgId) {
this.orgId= orgId;
}
public List<OrgTree> getChildren() {
return children;
}
public void setChildren(List<OrgTree> children) {
this.children = children;
}
}
<resultMap id="orgZtreeMap" type="com.tyz.commons.entity.OrgTree">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="orgId" property="orgId" jdbcType="VARCHAR"/>
<result column="orgName" property="orgName" jdbcType="VARCHAR"/>
<collection column="orgId" property="children" ofType="OrgTree" javaType="java.util.ArrayList" select="findOrgByparentId"></collection>
</resultMap>
<select id="querySdpOrgAll" parameterType="map" resultMap="orgZtreeMap">
select
orgId,orgName,orgId ,orgName
from org_table where
orgId = 0
</select>
<!-- 再递归查询出一级部门下的所有子部门 -->
<select id="findOrgByparentId" resultMap="orgZtreeMap" parameterType="string">
select orgId,orgName,orgId ,orgName from org_table where superiorId= #{orgId,jdbcType=VARCHAR}
</select>
但这样数据量大的时候回比较慢
换成先查询所有，然后在java里进行转换
<sql id="org_column_list">
orgId,orgName,superiorId
</sql>
<select id="querySdpOrgAll" resultMap="MiddleOrgResultMap">
select
<include refid="org_column_list"/>
from org_table
</select>
实体
public class OrganizationRes {
private String orgId;// 部门代码
private String orgName;// 部门中文名称
private String superiorId;// 上级部门id
private List<OrganizationRes> subordinateOrg;// 下属部门（对象集合）
public String getOrgId() {
return orgId;
}
public void setOrgId(String orgId) {
this.orgId = orgId;
}
public String getOrgName() {
return orgName;
}
public void setOrgName(String orgName) {
this.orgName = orgName;
}
public String getSuperiorId() {
return superiorId;
}
public void setSuperiorId(String superiorId) {
this.superiorId = superiorId;
}
}
public List<OrganizationRes> getSubordinateOrg() {
return subordinateOrg;
}
public void setSubordinateOrg(List<OrganizationRes> subordinateOrg) {
this.subordinateOrg = subordinateOrg;
}
/**
* 获取组织树
* @return
*/
public void getMiddleOrgTree(){
//获取组织架构树
List<OrganizationRes > list=OrgDao.findAll();
List<OrganizationRes > ztree= buildTreeByRecursive(list);
}
/**
* 使用递归方法建树
* @param treeNodes
* @return
*/
public List<OrganizationRes > buildTreeByRecursive(List<OrganizationRes > treeNodes) {
List<OrganizationRes > trees = new ArrayList<OrganizationRes >();
//遍历所有的
for (OrganizationRes treeNode : treeNodes) {
//如果为null则为最顶层节点
if (StringUtils.isEmpty(treeNode.getSuperiorId())) {
trees.add(findChildren(treeNode,treeNodes));
}
}
return trees;
}
/**
* 递归查找子节点
* @param parentNode 父节点
* @param treeNodes
* @return
*/
public OrganizationRes findChildren(OrganizationRes parentNode,List<OrganizationRes> treeNodes) {
for (OrganizationRes it : treeNodes) {
//遍历到上级的id与某条数据的上级id一致时，证明该上级是这条数据的上级
if(parentNode.getOrgId().equals(it.getSuperiorId())) {
if (parentNode.getSubordinateOrg() == null) {
parentNode.setSubordinateOrg(new ArrayList<OrganizationRes>());
}
//使用递归添加下级数据
parentNode.getSubordinateOrg().add(findChildren(it,treeNodes));
}
}
return parentNode;
}
另一种方式的树形式Ztree
/**
* checked:false
* id:"CTZ"
* orgid:"CTZ"
* orgname:"中国通周"
* pId:"0"
*/
@Override
public void getOrgZtree() {
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
List<OrganizationRes> orgList = OrgDao.findAll();
Map<String,Object> map=null;
for (OrganizationRes organizationRes : orgList) {
map=new HashMap<String,Object>();
map.put("id", organizationRes.getOrgId());
map.put("pId", StringUtils.isBlank(organizationRes.getSuperiorId())?"0":organizationRes.getSuperiorId());
map.put("orgname", organizationRes.getOrgName());
map.put("orgcode", organizationRes.getOrgCode());
map.put("checked", true);//是否默认选中
resultList.add(map);
}
}
根据组织id获取下级
/**
* 根据组织id获取下级
*/
@Override
public void getOrgZtreeByOrgCode() {
String orgid = "2530";
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
List<OrganizationRes> orgList = orgDao.findAll();
OrganizationRes organization=new OrganizationRes();
for (OrganizationRes organizationRes : orgList) {
if(orgid.equals(organizationRes.getOrgId())) {
BeanUtils.copyProperties(organizationRes, organization);
//递归查找子节点
findChildren(organization, orgList);
break;
}
}
this.buildOrgByRecursive(organization, resultList, new HashMap<String,Object>(),0);
}
/**
* 将组织树转换为list 符合ztree格式
* @param organization
* @param list
*/
public void buildOrgByRecursive(OrganizationRes organization,List<Map<String,Object>> list, Map<String, Object> map,int index) {
map=new HashMap<String,Object>();
map.put("id", organization.getOrgId());
if(index>0) {
map.put("pId", organization.getSuperiorId());
}else {
map.put("pId", "0");
}
map.put("orgname", organization.getOrgName());
map.put("orgcode", organization.getOrgCode());
map.put("checked", true);
list.add(map);
List<OrganizationRes> subList = organization.getSubordinateOrg();
if(subList!=null&&subList.size()>0) {
for (OrganizationRes organizationRes : subList) {
this.buildOrgByRecursive(organizationRes, list, map,1);
}
}
}
查询上级
<select id="findSuperiorId" parameterMap="map" resultMap="MiddleOrgResultMap">
SELECT
O.*
FROM (
SELECT
@r AS _orgId,
(SELECT @r := superiorId FROM sdp_middle_org WHERE orgId = _orgId) AS 2v2,
@l := @l + 1 AS lvl
FROM
(SELECT @r := #{orgId,jdbcType=VARCHAR}) vars,
sdp_middle_org h
WHERE @r is not null) O1
JOIN sdp_middle_org O
ON O1._orgId = O.orgId
</select>
