组织结构树: java递归实现 或 mysql迭代实现
前言
最近开发中遇到了查询组织机构树(五级)问题,记录并分享一下(仅供借鉴)。
实现方法===》1,mysql迭代实现
实体类
public Class SysOrg {
private String sysOrgId; //主键
private String tenantId; //租户ID
private String tenantName; //租户名称
private String orgId; //组织机构编码
private String orgName; //组织机构名称
private String orgPId; //父级组织机构编码
private String orgTopId; //顶级组织机构编码
private String orgLevel; //组织机构级别
private String orgStatus; //组织机构状态(0:无效,1;有效)
private List<SysOrg> childNodeList; //子树
}
具体库中测试数据
mapper文件
<resultMap id="orgTree" type="com.cmos.ngscsman.beans.sys.SysOrgTree">
<result column="tenant_id" jdbcType="VARCHAR" property="tenantId" />
<result column="tenant_name" jdbcType="VARCHAR" property="tenantName" />
<result column="org_id" jdbcType="VARCHAR" property="orgId" />
<result column="org_pid" jdbcType="VARCHAR" property="orgPid" />
<result column="org_name" jdbcType="VARCHAR" property="orgName" />
<result column="org_level" jdbcType="CHAR" property="orgLevel" />
<result column="org_sort" jdbcType="CHAR" property="orgSort" />
<result column="org_status" jdbcType="CHAR" property="status" />
<result column="remarks" jdbcType="VARCHAR" property="remarks" />
<collection property="childNodeList" ofType="com.cmos.ngscsman.beans.sys.SysOrgTree" column="org_id" select="getOrgChild"/>
</resultMap
<select id="getOrgChild" resultMap="orgTree">
select
org_id,
org_name,
org_level,
tenant_id,
tenant_name,
org_status,
org_sort,
org_pid,
remarks
from
sys_org
where org_pid = #{org_id} and org_status = '1';
</select>
查询当前节点及以下的树结构,当前节点以上的树结构,可for循环查出
<select id="getOrgTree" resultMap="orgTree">
select
org_id,
org_name,
org_level,
tenant_id,
tenant_name,
org_status,
org_sort,
org_pid,
remarks
from
sys_org
where org_status = '1'
<choose>
<when test="orgId == '-1'">
and org_pid = #{orgId}
</when>
<otherwise>
and org_id = #{orgId}
</otherwise>
</choose>
</select>
补全父级节点至一级-----(仅供参考)
String orgPid = orgTreeList.get(0).getOrgPid();
for(int i =1 ; i < level;i++){
SysOrgTree sysOrg = sysOrgDao.getOrgParent(orgPid);
List<SysOrgTree> childNodeList = new ArrayList<>();
childNodeList.addAll(orgTreeList);
sysOrg.setChildNodeList(childNodeList);
orgPid = sysOrg.getOrgPid();
orgTreeList.clear();
orgTreeList.add(sysOrg);
}
实现方法===》2,java递归
实体类
public Class SysOrg {
private String sysOrgId; //主键
private String tenantId; //租户ID
private String tenantName; //租户名称
private String orgId; //组织机构编码
private String orgName; //组织机构名称
private String orgPId; //父级组织机构编码
private String orgTopId; //顶级组织机构编码
private String orgLevel; //组织机构级别
private String orgStatus; //组织机构状态(0:无效,1;有效)
}
基本思路:
根据当前登录人员挂靠的组织机构编码,向下递归查询所有的子节点和向上递归查询父级节点,进行封装转换(超级管理员挂靠的组织机构编码:-1,可查询所有的组织机构树)
核心业务代码:
public List<Map<String, Object>> getOrgTreeList(String orgId) throws GeneralException {
LOGGER.info("getOrgTreeList入参:"+orgId);
//返回的tree
List<Map<String, Object>> listTree = new ArrayList<>();
List<SysOrg> listOrg = new ArrayList<>();
//判断是否是超级管理员
if("-1".equals(orgId)){
//超级管理员查询多个树
Map map = new HashMap();
map.put("orgPid",orgId);
map.put("status","1");
listOrg = sysOrgDao.selectOrgListByPid(map);
}else{
SysOrg curOrg = sysOrgDao.selectById(orgId,"1");
listOrg.add(curOrg);
}
for (SysOrg curOrg : listOrg) {
if (null == curOrg) {
break;
}
//获取顶级组织机构id
String orgTopId = "-1";
//获取有效的组织机构列表
List<SysOrg> sysOrgList = sysOrgDao.selectAllSysOrg("1");
//拼装当前节点及其子节点
List<Map<String, Object>> resList = new ArrayList<>();
Map<String, Object> map = new HashMap();
//实体转map
assembleOrgMap(map, curOrg);
//判断是否租户
if(“0”.equals(curOrg.getOrgLevel())){
orgId = curOrg.getOrgId();
}
//递归查询子级组织机构
map.put("childNodeList", buildChildOrgTree(sysOrgList, orgId));
resList.add(map);
//获取父级组织机构
List<Map<String, Object>> list = buildParentOrgTree(sysOrgList, curOrg, orgTopId, resList);
listTree.addAll(list);
}
return listTree;
}
根据当前节点向下获取所有子节点
public static List<Map<String, Object>> buildChildOrgTree(List<SysOrg> sysOrgList, String pid)
{
List<Map<String, Object>> resList = null;
for (SysOrg sysOrg : sysOrgList)
{
if (pid.equals(sysOrg.getOrgPid()))
{ if (null == resList)
resList = new ArrayList<>();
}
Map<String, Object> map = new HashMap<>();
assembleOrgMap(map, sysOrg);
map.put("childNodeList", buildChildOrgTree(sysOrgList, sysOrg.getOrgId()));
resList.add(map);
}
return resList;
}
根据当前节点向上递归获取所有父节点
/**
* 根据当前节点向上递归获取所有父节点
* @param sysOrgList 组织结构表
* @param curOrg 当前组织
* @param topOrgId 顶级组织id
* @param resList 返回结果
* @return
*/
public static List<Map<String, Object>> buildParentOrgTree(List<SysOrg> sysOrgList, SysOrg curOrg, String topOrgId, List<Map<String, Object>> resList) {
if (topOrgId.equals(curOrg.getOrgPid())) {
return resList;
}
String curOrgPid = curOrg.getOrgPid();
for (SysOrg sysOrg : sysOrgList) {
if (curOrgPid.equals(sysOrg.getOrgId())) {
List<Map<String, Object>> newList = new ArrayList<>();
Map<String, Object> map = new HashMap<>();
assembleOrgMap(map, sysOrg);
map.put("childNodeList", resList);
newList.add(map);
curOrg = sysOrg;
resList = newList;
break;
}
}
return buildParentOrgTree(sysOrgList, curOrg, topOrgId, resList);
}
装配SysOrg实体类到map
/* 装配SysOrg到map
* @param map
* @param sysOrg
*/
public static void assembleOrgMap(Map map, SysOrg sysOrg) {
map.put("orgName", sysOrg.getOrgName());
map.put("tenantId", sysOrg.getTenantId());
map.put("tenantName", sysOrg.getTenantName());
map.put("orgId", sysOrg.getOrgId());
map.put("orgLevel", sysOrg.getOrgLevel());
map.put("orgSort", sysOrg.getOrgSort());
}
相关SQL
根据父级组织机构编码查询组织机构
<select id="selectOrgListByPid" resultMap="BaseResultMap" parameterType="java.util.Map">
SELECT
<include refid="template" />
FROM sys_org WHERE org_pid= #{orgPid}
<if test="tenantId != null & tenantId != '' " >
AND tenant_id = #{tenantId,jdbcType=VARCHAR}
</if>
<if test="status != null & status != '' " >
AND org_status = #{status,jdbcType=VARCHAR}
</if>
ORDER by org_id DESC
</select>
查询所有的组织机构
<select id="selectAllSysOrg" resultMap="BaseResultMap">
select <include refid="template" />
from sys_org where org_status = #{valid, jdbcType=CHAR} ORDER by org_sort ASC
</select>
根据组织机构编码查询组织机构信息
<select id="selectById" parameterType="java.lang.String" resultMap="BaseResultMap">
select <include refid="template" />
from sys_org
where org_id = #{orgId, jdbcType=VARCHAR} and org_status = #{valid, jdbcType=CHAR}
</select>
执行结果如下:
[{
"orgName": "测试导入",
"tenantName": "测试导入",
"tenantId": "777",
"orgLevel": "0",
"orgId": "777",
"childNodeList": [{
"orgName": "河南",
"tenantName": "测试导入",
"tenantId": "777",
"orgLevel": "1",
"orgId": "777001",
"childNodeList": [
"orgName": "郑州",
"tenantName": "测试导入",
"tenantId": "777",
"orgLevel": "2",
"orgId": "777001001",
"childNodeList": []
]
]
}
]