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

一、测试数据准备

/*
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 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 代表会拿父节点 id ，作为参数获取 next 对象
• javaType 代表 next 对象是个列表，其实可以省略不写
• ofType 用来区分 JavaBean 属性类型和集合包含的类型
• select 是用来执行循环哪个 SQL

<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>  

五、具体调用结果如下：

[
{
"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": []
}
]
}
]
}
]
}
]