之前项目组有个需求，定时同步机构的信息。已知三方接口由于返回数据量很大，所以最后需要三方提供一个可根据机构编号获取当前机构及子机构信息的接口。而不是一次性返回全部机构信息！
由于这次需求也用到了递归，所以记录下！
Java程序递归查询
pom.xml文件
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.73</version>
</dependency>数据库
organization机构表
表结构sql
DROP TABLE IF EXISTS `organization`;
CREATE TABLE `organization` (
`id` int(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`org_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '机构编号',
`org_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '机构名称',
`parent_id` int(20) NULL DEFAULT NULL COMMENT '父级机构id',
`parent_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '父级机构编码',
`parent_all_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '所有父级code',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 50 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
organization_record 机构记录表
将机构表数据及原始三方接口数据以子节点形式存储到记录表中
CREATE TABLE `organization_record` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '主键',
`organization_info` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '机构数据的json串存储',
`organization_source_info` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '原始机构数据的json串存储',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;机构实体类 Organization
package com.example.demo.entity;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
@Data
public class Organization implements Serializable {
private int id;
//机构编号
private String orgCode;
//机构名称
private String orgName;
//父级id
private int parentId;
//父级机构编号
private String parentCode;
//所有父级code
private String parentAllCode;
private List<Organization> children;
}
mapper
机构mapper
package com.example.demo.mapper;
import com.example.demo.entity.Organization;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
@Mapper
public interface OrganizationMapper {
//添加组织机构
int insertOrganization(Organization organization);
//根据组织编号查询信息
Organization queryOrganizationByCode(String code);
//修改组织机构信息
int updateOrganization(Organization organization);
//根据code查询对应的组织机构
List<Organization> queryOrganizationByParentId(@Param("parentId") String parentId);
}
机构记录mapper
package com.example.demo.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@Mapper
public interface OrganizationRecordMapper {
//添加
void insertOrganizationRecord(@Param("organizationInfo") String organizationInfo,
@Param("organizationSourceInfo") String organizationSourceInfo);
}
SQL
机构sql
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.OrganizationMapper">
<resultMap id="organizationMap" type="com.example.demo.entity.Organization">
<result property="id" column="id"/>
<result property="orgCode" column="org_code"/>
<result property="orgName" column="org_name"/>
<result property="parentId" column="parent_id"/>
<result property="parentCode" column="parent_code"/>
<result property="parentAllCode" column="parent_all_code"/>
</resultMap>
<!--新增-->
<insert id="insertOrganization" parameterType="com.example.demo.entity.Organization">
INSERT INTO organization (org_code,org_name,parent_id,parent_code,parent_all_code)
VALUE (#{orgCode},#{orgName},#{parentId},#{parentCode},#{parentAllCode})
</insert>
<!--根据code查询对应的组织机构-->
<select id="queryOrganizationByParentId" resultMap="organizationMap">
select * from organization
<where>
<if test="parentId!='-1'">
and parent_id=#{parentId}
</if>
</where>
</select>
<!--根据组织编号查询机构信息-->
<select id="queryOrganizationByCode" parameterType="string" resultMap="organizationMap">
select * from organization where org_code=#{code} limit 0,1
</select>
<!--修改-->
<update id="updateOrganization" parameterType="com.example.demo.entity.Organization">
UPDATE organization
SET org_name = #{orgName},
parent_id = #{parentId},
parent_code = #{parentCode},
parent_all_code = #{parentAllCode}
WHERE org_code = #{orgCode}
</update>
</mapper>机构记录sql
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.OrganizationRecordMapper">
<!--添加-->
<insert id="insertOrganizationRecord" >
insert into organization_record(organization_info,organization_source_info)
VALUES(#{organizationInfo},#{organizationSourceInfo})
</insert>
</mapper>业务逻辑service
package com.example.demo.service;
import com.alibaba.fastjson.JSONArray;
import com.example.demo.entity.Organization;
import com.example.demo.mapper.OrganizationMapper;
import com.example.demo.mapper.OrganizationRecordMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
@Slf4j
@Service
public class TestDiGuiService {
@Autowired
private OrganizationMapper organizationMapper;
@Autowired
private OrganizationRecordMapper organizationRecordMapper;
public HashMap<String, Object> syncOrganization() {
HashMap<String, Object> resultMap = new HashMap<>();
List<HashMap<String, Object>> sourceList = new ArrayList<>(); //原始机构信息集合
//1.模拟请求三方接口获取信息 TODO
Map emp = new HashMap();
List<HashMap<String, Object>> mapList = new ArrayList<>();
HashMap<String, Object> hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0001");
hashMap.put("ORG_NAME", "中国工商银行");
mapList.add(hashMap);
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0002");
hashMap.put("ORG_NAME", "北京银行");
mapList.add(hashMap);
emp.put("result", mapList);
emp.put("status", "200");
String code = (String) emp.get("status");
if (!"200".equals(code)) {
resultMap.put("code", "500");
return resultMap;
}
List<HashMap<String, Object>> list = (List<HashMap<String, Object>>) emp.get("result");
sourceList.addAll(list);
//2.对数据进行逻辑处理
if (list.size() != 0) {
for (HashMap<String, Object> object : list) {
//2.1 对信息封装为组织机构代码对象
Organization organization = conversionOrg("0", object);
//2.2 新增/修改机构信息
disposeOrg(organization);
//2.3 递归遍历
recursive(organization, sourceList);
}
}
resultMap.put("code", "200");
//3.查询出全部机构信息，整理为json串
queryOrganization(sourceList);
return resultMap;
}
//封装成对象
public Organization conversionOrg(String orgCode, HashMap<String, Object> map) {
Organization o = new Organization();
String code = (String) map.get("ORG_CODE");
String name = (String) map.get("ORG_NAME");
log.info("组织机构名称={},机构编号={}", name, code);
o.setOrgCode(code);
o.setOrgName(name);
Organization organization = organizationMapper.queryOrganizationByCode(orgCode);
if (organization == null) {
o.setParentAllCode("0,");
} else {
String parentAllCode = StringUtils.isEmpty(organization.getParentAllCode()) ? "0," : organization.getParentAllCode() + orgCode + ",";
o.setParentAllCode(parentAllCode);
o.setParentId(organization.getId());
o.setParentCode(organization.getOrgCode());
}
return o;
}
//逻辑处理 机构若存在该机构代码，则进行修改；否则进行新增
public void disposeOrg(Organization organization) {
Organization org = organizationMapper.queryOrganizationByCode(organization.getOrgCode());
if (org == null || "".equals(org.getOrgCode()) || !organization.getOrgCode().equals(org.getOrgCode())) {
organizationMapper.insertOrganization(organization);
log.info("新增完成!机构编号={},组织机构名称={}", organization.getOrgCode(), organization.getOrgName());
} else {
organizationMapper.updateOrganization(organization);
log.info("修改完成!机构编号={},组织机构名称={}", organization.getOrgCode(), organization.getOrgName());
}
}
//递归遍历机构下面的子机构信息
public void recursive(Organization organization, List<HashMap<String, Object>> sourceList) {
try {
Thread.currentThread().sleep(2000);
} catch (Exception e) {
e.printStackTrace();
}
//模拟请求三方接口中二级机构及其子机构的信息 TODO
Map emp = new HashMap();
List<HashMap<String, Object>> mapList = new ArrayList<>();
HashMap<String, Object> hashMap = new HashMap<>();
if ("0001".equals(organization.getOrgCode())) {
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0011");
hashMap.put("ORG_NAME", "丰台区");
mapList.add(hashMap);
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0021");
hashMap.put("ORG_NAME", "海淀区");
mapList.add(hashMap);
}
if ("0002".equals(organization.getOrgCode())) {
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0012");
hashMap.put("ORG_NAME", "丰台区");
mapList.add(hashMap);
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0022");
hashMap.put("ORG_NAME", "大兴区");
mapList.add(hashMap);
}
if ("0011".equals(organization.getOrgCode())) {
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0031");
hashMap.put("ORG_NAME", "马家堡");
mapList.add(hashMap);
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0041");
hashMap.put("ORG_NAME", "角门西");
mapList.add(hashMap);
}
if ("0021".equals(organization.getOrgCode())) {
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0051");
hashMap.put("ORG_NAME", "白堆子");
mapList.add(hashMap);
}
if ("0012".equals(organization.getOrgCode())) {
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0032");
hashMap.put("ORG_NAME", "岳各庄");
mapList.add(hashMap);
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0042");
hashMap.put("ORG_NAME", "大红门");
mapList.add(hashMap);
}
if ("0022".equals(organization.getOrgCode())) {
hashMap = new HashMap<>();
hashMap.put("ORG_CODE", "0052");
hashMap.put("ORG_NAME", "圆明园");
mapList.add(hashMap);
}
emp.put("result", mapList);
emp.put("status", "200");
String code = (String) emp.get("status");
if (!"200".equals(code)) {
return;
}
List<HashMap<String, Object>> list = (List<HashMap<String, Object>>) emp.get("result");
sourceList.addAll(list);
if (list.size() != 0) {
for (HashMap<String, Object> object : list) {
Organization conversionOrg = conversionOrg(organization.getOrgCode(), object);
disposeOrg(conversionOrg);
recursive(conversionOrg, sourceList);
}
}
}
public List<Organization> queryOrganization(List<HashMap<String, Object>> sourceList) {
List<Organization> organizationList = organizationMapper.queryOrganizationByParentId("-1");
List<Organization> parentList = organizationList.stream()
.filter(item -> item.getParentId() == 0)
.collect(Collectors.toList());
for (Organization organization : parentList) {
List<Organization> children = getChildren(organization, organizationList);
organization.setChildren(children);
}
String json = JSONArray.toJSONString(parentList);
String sourceJson = JSONArray.toJSONString(sourceList);
organizationRecordMapper.insertOrganizationRecord(json,sourceJson);
return parentList;
}
//获取当前节点的所有子节点
public List<Organization> getChildren(Organization organization, List<Organization> organizationList) {
List<Organization> list = organizationList.stream()
.filter(item -> item.getParentId() == organization.getId())
.collect(Collectors.toList());
if (CollectionUtils.isEmpty(list)) {
return null;
}
for (Organization org : list) {
org.setChildren(getChildren(org, organizationList));
}
return list;
}
}controller类
package com.example.demo.controller;
import com.example.demo.service.TestDiGuiService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
@RestController
@RequestMapping("/digui")
public class DiGuiController {
@Autowired
TestDiGuiService testDiGuiService;
@RequestMapping("syncOrg")
public HashMap<String, Object> synchronousOrganization() {
return testDiGuiService.syncOrganization();
}
}
请求结果
postman调用接口
机构表
机构记录表
mybatis递归查询
也可通过mybatis查询属性结构信息，一般数据量少的可以通过SQL实现
OrganizationMapper文件
package com.example.demo.mapper;
import com.example.demo.entity.Organization;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* 组织机构mapper
*/
@Mapper
public interface OrganizationMapper {
//查询全部数据
List<Organization> queryAll(@Param("code") String code);
}
SQL
<collection property="children" column="org_code" select="getChildrenTreeByParentCode"/>的column设置的是父节点SQL的返回结果的列名。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.OrganizationMapper">
<resultMap id="orgResultMap" type="com.example.demo.entity.Organization">
<id property="id" column="id"/>
<result property="orgCode" column="org_code"/>
<result property="orgName" column="org_name"/>
<result property="parentCode" column="parent_code"/>
<result property="parentId" column="parent_id"/>
<result property="parentAllCode" column="parent_all_code"/>
<collection property="children" column="org_code" select="getChildrenTreeByParentCode"></collection>
</resultMap>
<!--级联查询父节点-->
<select id="queryAll" resultMap="orgResultMap" parameterType="String">
select *
from organization
where parent_code=#{code}
</select>
<!--级联查询子节点-->
<select id="getChildrenTreeByParentCode" resultMap="orgResultMap">
select *
from organization
where parent_code=#{org_code}
</select>
</mapper>controller
package com.example.demo.controller;
import com.example.demo.entity.Organization;
import com.example.demo.mapper.OrganizationMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.List;
@RestController
@RequestMapping("/digui")
public class DiGuiController {
@Autowired
OrganizationMapper organizationMapper;
@RequestMapping("test")
public List<Organization> test(@RequestParam("code")String code) {
List<Organization> organizationList = organizationMapper.queryAll(code);
return organizationList;
}
}
调用结果
可看出执行顺序：先执行父节点SQL，后根据每条返回的结果SQL的org_code列作为入参递归查询子节点SQL。
递归能力欠缺，请各位大佬提出意见及错误！
