SpringBoot获取树状结构数据-SQL处理

技术分享 3年前 (2023-09-07) 0 999+

前言

在开发中，层级数据（树状结构）的获取往往可能是我们一大难点，我现在将自己获取的树状结构数据方法总结如下,希望能给有需要的小伙伴有所帮助！

一、测试数据准备

/*  Navicat Premium Data Transfer   Source Server         : 本地MySQL-local  Source Server Type    : MySQL  Source Server Version : 80100  Source Host           : localhost:33306  Source Schema         : test   Target Server Type    : MySQL  Target Server Version : 80100  File Encoding         : 65001   Date: 06/09/2023 11:21:45 */  SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;  -- ---------------------------- -- Table structure for region -- ---------------------------- DROP TABLE IF EXISTS `region`; CREATE TABLE `region`  (   `id` bigint(0) NOT NULL COMMENT '主键id',   `region_id` bigint(0) NULL DEFAULT NULL COMMENT '区域id',   `region_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '区域编码',   `region_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '区域名称',   `parent_id` bigint(0) NULL DEFAULT NULL COMMENT '父节点id',   PRIMARY KEY (`id`) USING BTREE,   UNIQUE INDEX `region_id`(`region_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '地区信息' ROW_FORMAT = Dynamic;  -- ---------------------------- -- Records of region -- ---------------------------- INSERT INTO `region` VALUES (1, 10001, 'CODEA0001', '中国', 0); INSERT INTO `region` VALUES (2, 10002, 'CODEB0001', '安徽省', 10001); INSERT INTO `region` VALUES (3, 10003, 'CODEB0002', '黑龙江省', 10001); INSERT INTO `region` VALUES (4, 10004, 'CODEB0003', '广东省', 10001); INSERT INTO `region` VALUES (5, 10005, 'CODEC0001', '合肥市', 10002); INSERT INTO `region` VALUES (6, 10006, 'CODEC0002', '淮北市', 10002); INSERT INTO `region` VALUES (7, 10007, 'CODEC0003', '哈尔滨市', 10003); INSERT INTO `region` VALUES (8, 10008, 'CODEC0004', '鹤岗市', 10003); INSERT INTO `region` VALUES (9, 10009, 'CODEC0005', '广州市', 10004); INSERT INTO `region` VALUES (10, 10010, 'CODEC0006', '深圳市', 10004); INSERT INTO `region` VALUES (11, 10011, 'CODED0001', '龙华区', 10010); INSERT INTO `region` VALUES (12, 10012, 'CODED0002', '南山区', 10010); INSERT INTO `region` VALUES (13, 10013, 'CODED0003', '天河区', 10009);  SET FOREIGN_KEY_CHECKS = 1;

二、对应表数据java实体类

import lombok.Data; import java.util.List;  /**  * @Project  * @Description  * @Author songwp  * @Date 2023/9/5 15:16  **/ @Data public class Region{     private Long id;     private Long regionId;     private String regionCode;     private String regionName;     private Long parentId;     private List<Region> children;  }

三、对应mapper的调用方法

import com.songwp.pojo.entity.Region; import org.apache.ibatis.annotations.Mapper; import java.util.List;  /**  * @Project  * @Description  在 持久层，我们只调用 getNodeTree 方法，parent_id = 0 代表顶级节点。
 * 然后通过 collection 节点继续调用 getNextNodeTree 方法进行循环调用。  * @Author songwp  * @Date 2023/9/5 15:22  **/ @Mapper public interface RegionMapper {      List<Region> getNodeTree(); }

四、对应mapper.xml的写法（重点）

column 代表会拿父节点 region_id ，作为参数获取 region对象
javaType 代表 children对象是个列表，其实可以省略不写
ofType 用来区分 JavaBean 属性类型和集合包含的类型
select 是用来执行循环哪个 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.songwp.mapper.RegionMapper">      <sql id="Base_Column_List">         id,         region_id,         parent_id,         region_code,         region_name     </sql>      <resultMap id="BaseTreeResultMap" type="com.songwp.pojo.entity.Region">         <result property="id" column="id" jdbcType="BIGINT"/>         <result property="regionId" column="region_id" jdbcType="BIGINT"/>         <result property="regionCode" column="region_code" jdbcType="VARCHAR"/>         <result property="regionName" column="region_name" jdbcType="VARCHAR"/>         <result property="parentId" column="parent_id" jdbcType="BIGINT"/>         <collection column="region_id" property="children" javaType="java.util.ArrayList"                     ofType="com.songwp.pojo.entity.Region" select="getNextNodeTree"/>     </resultMap>      <resultMap id="NextTreeResultMap" type="com.songwp.pojo.entity.Region">         <result property="id" column="id" jdbcType="BIGINT"/>         <result property="regionId" column="region_id" jdbcType="BIGINT"/>         <result property="regionCode" column="region_code" jdbcType="VARCHAR"/>         <result property="regionName" column="region_name" jdbcType="VARCHAR"/>         <result property="parentId" column="parent_id" jdbcType="BIGINT"/>         <collection column="region_id" property="children" javaType="java.util.ArrayList"                     ofType="com.songwp.pojo.entity.Region" select="getNextNodeTree"/>     </resultMap>      <select id="getNextNodeTree" resultMap="NextTreeResultMap">         SELECT         <include refid="Base_Column_List"/>         FROM region         WHERE parent_id = #{id}     </select>      <select id="getNodeTree" resultMap="BaseTreeResultMap">         SELECT         <include refid="Base_Column_List"/>         FROM region         WHERE parent_id = 0000     </select> </mapper>

五、具体调用结果如下：

[   {     "id": 1,     "regionId": 10001,     "regionCode": "CODEA0001",     "regionName": "中国",     "parentId": 0,     "children": [       {         "id": 2,         "regionId": 10002,         "regionCode": "CODEB0001",         "regionName": "安徽省",         "parentId": 10001,         "children": [           {             "id": 5,             "regionId": 10005,             "regionCode": "CODEC0001",             "regionName": "合肥市",             "parentId": 10002,             "children": []           },           {             "id": 6,             "regionId": 10006,             "regionCode": "CODEC0002",             "regionName": "淮北市",             "parentId": 10002,             "children": []           }         ]       },       {         "id": 3,         "regionId": 10003,         "regionCode": "CODEB0002",         "regionName": "黑龙江省",         "parentId": 10001,         "children": [           {             "id": 7,             "regionId": 10007,             "regionCode": "CODEC0003",             "regionName": "哈尔滨市",             "parentId": 10003,             "children": []           },           {             "id": 8,             "regionId": 10008,             "regionCode": "CODEC0004",             "regionName": "鹤岗市",             "parentId": 10003,             "children": []           }         ]       },       {         "id": 4,         "regionId": 10004,         "regionCode": "CODEB0003",         "regionName": "广东省",         "parentId": 10001,         "children": [           {             "id": 9,             "regionId": 10009,             "regionCode": "CODEC0005",             "regionName": "广州市",             "parentId": 10004,             "children": [               {                 "id": 13,                 "regionId": 10013,                 "regionCode": "CODED0003",                 "regionName": "天河区",                 "parentId": 10009,                 "children": []               }             ]           },           {             "id": 10,             "regionId": 10010,             "regionCode": "CODEC0006",             "regionName": "深圳市",             "parentId": 10004,             "children": [               {                 "id": 11,                 "regionId": 10011,                 "regionCode": "CODED0001",                 "regionName": "龙华区",                 "parentId": 10010,                 "children": []               },               {                 "id": 12,                 "regionId": 10012,                 "regionCode": "CODED0002",                 "regionName": "南山区",                 "parentId": 10010,                 "children": []               }             ]           }         ]       }     ]   } ]