Java程序递归及mybatis递归查询

之前项目组有个需求，定时同步机构的信息。已知三方接口由于返回数据量很大，所以最后需要三方提供一个可根据机构编号获取当前机构及子机构信息的接口。而不是一次性返回全部机构信息！

由于这次需求也用到了递归，所以记录下！

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;//父级idprivate  int parentId;//父级机构编号private  String parentCode;//所有父级codeprivate  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 mapperPUBLIC "-//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 organizationSET 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 mapperPUBLIC "-//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 {@Autowiredprivate OrganizationMapper organizationMapper;@Autowiredprivate OrganizationRecordMapper organizationRecordMapper;public HashMap<String, Object> syncOrganization() {HashMap<String, Object> resultMap = new HashMap<>();List<HashMap<String, Object>> sourceList = new ArrayList<>();  //原始机构信息集合//1.模拟请求三方接口获取信息 TODOMap 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();}//模拟请求三方接口中二级机构及其子机构的信息 TODOMap 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 {@AutowiredTestDiGuiService 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 mapperPUBLIC "-//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 organizationwhere parent_code=#{code}</select><!--级联查询子节点--><select id="getChildrenTreeByParentCode" resultMap="orgResultMap">select *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 List<Organization> test(@RequestParam("code")String code) {List<Organization> organizationList = organizationMapper.queryAll(code);return organizationList;}
}

调用结果

可看出执行顺序：先执行父节点SQL，后根据每条返回的结果SQL的org_code列作为入参递归查询子节点SQL。

递归能力欠缺，请各位大佬提出意见及错误！