专业做网站推广的公司,如何分析网站关键词,低代码平台,仿站源码之前项目组有个需求#xff0c;定时同步机构的信息。已知三方接口由于返回数据量很大#xff0c;所以最后需要三方提供一个可根据机构编号获取当前机构及子机构信息的接口。而不是一次性返回全部机构信息#xff01;
由于这次需求也用到了递归#xff0c;所以记录下#…之前项目组有个需求定时同步机构的信息。已知三方接口由于返回数据量很大所以最后需要三方提供一个可根据机构编号获取当前机构及子机构信息的接口。而不是一次性返回全部机构信息
由于这次需求也用到了递归所以记录下
Java程序递归查询
pom.xml文件
dependencygroupIdcom.alibaba/groupIdartifactIdfastjson/artifactIdversion1.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;//父级idprivate int parentId;//父级机构编号private String parentCode;//所有父级codeprivate String parentAllCode;private ListOrganization 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查询对应的组织机构ListOrganization 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 version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.example.demo.mapper.OrganizationMapperresultMap idorganizationMap typecom.example.demo.entity.Organizationresult propertyid columnid/result propertyorgCode columnorg_code/result propertyorgName columnorg_name/result propertyparentId columnparent_id/result propertyparentCode columnparent_code/result propertyparentAllCode columnparent_all_code//resultMap!--新增--insert idinsertOrganization parameterTypecom.example.demo.entity.OrganizationINSERT INTO organization (org_code,org_name,parent_id,parent_code,parent_all_code)VALUE (#{orgCode},#{orgName},#{parentId},#{parentCode},#{parentAllCode})/insert!--根据code查询对应的组织机构--select idqueryOrganizationByParentId resultMaporganizationMapselect * from organizationwhereif testparentId!-1and parent_id#{parentId}/if/where/select!--根据组织编号查询机构信息--select idqueryOrganizationByCode parameterTypestring resultMaporganizationMapselect * from organization where org_code#{code} limit 0,1/select!--修改--update idupdateOrganization parameterTypecom.example.demo.entity.OrganizationUPDATE organizationSET org_name #{orgName},parent_id #{parentId},parent_code #{parentCode},parent_all_code #{parentAllCode}WHERE org_code #{orgCode}/update
/mapper
机构记录sql
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.example.demo.mapper.OrganizationRecordMapper!--添加--insert idinsertOrganizationRecord 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 {Autowiredprivate OrganizationMapper organizationMapper;Autowiredprivate OrganizationRecordMapper organizationRecordMapper;public HashMapString, Object syncOrganization() {HashMapString, Object resultMap new HashMap();ListHashMapString, Object sourceList new ArrayList(); //原始机构信息集合//1.模拟请求三方接口获取信息 TODOMap emp new HashMap();ListHashMapString, Object mapList new ArrayList();HashMapString, 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;}ListHashMapString, Object list (ListHashMapString, Object) emp.get(result);sourceList.addAll(list);//2.对数据进行逻辑处理if (list.size() ! 0) {for (HashMapString, 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, HashMapString, 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, ListHashMapString, Object sourceList) {try {Thread.currentThread().sleep(2000);} catch (Exception e) {e.printStackTrace();}//模拟请求三方接口中二级机构及其子机构的信息 TODOMap emp new HashMap();ListHashMapString, Object mapList new ArrayList();HashMapString, 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;}ListHashMapString, Object list (ListHashMapString, Object) emp.get(result);sourceList.addAll(list);if (list.size() ! 0) {for (HashMapString, Object object : list) {Organization conversionOrg conversionOrg(organization.getOrgCode(), object);disposeOrg(conversionOrg);recursive(conversionOrg, sourceList);}}}public ListOrganization queryOrganization(ListHashMapString, Object sourceList) {ListOrganization organizationList organizationMapper.queryOrganizationByParentId(-1);ListOrganization parentList organizationList.stream().filter(item - item.getParentId() 0).collect(Collectors.toList());for (Organization organization : parentList) {ListOrganization children getChildren(organization, organizationList);organization.setChildren(children);}String json JSONArray.toJSONString(parentList);String sourceJson JSONArray.toJSONString(sourceList);organizationRecordMapper.insertOrganizationRecord(json,sourceJson);return parentList;}//获取当前节点的所有子节点public ListOrganization getChildren(Organization organization, ListOrganization organizationList) {ListOrganization 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 {AutowiredTestDiGuiService testDiGuiService;RequestMapping(syncOrg)public HashMapString, 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 {//查询全部数据ListOrganization queryAll(Param(code) String code);
}SQL
collection propertychildren columnorg_code selectgetChildrenTreeByParentCode/的column设置的是父节点SQL的返回结果的列名。
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.example.demo.mapper.OrganizationMapperresultMap idorgResultMap typecom.example.demo.entity.Organizationid propertyid columnid/result propertyorgCode columnorg_code/result propertyorgName columnorg_name/result propertyparentCode columnparent_code/result propertyparentId columnparent_id/result propertyparentAllCode columnparent_all_code/collection propertychildren columnorg_code selectgetChildrenTreeByParentCode/collection/resultMap!--级联查询父节点--select idqueryAll resultMaporgResultMap parameterTypeStringselect *from organizationwhere parent_code#{code}/select!--级联查询子节点--select idgetChildrenTreeByParentCode resultMaporgResultMapselect *from organizationwhere 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 {AutowiredOrganizationMapper organizationMapper;RequestMapping(test)public ListOrganization test(RequestParam(code)String code) {ListOrganization organizationList organizationMapper.queryAll(code);return organizationList;}
}调用结果 可看出执行顺序先执行父节点SQL后根据每条返回的结果SQL的org_code列作为入参递归查询子节点SQL。 递归能力欠缺请各位大佬提出意见及错误
