在開發中，層級資料（樹狀結構）的獲取往往可能是我們一大難點，我現在將自己獲取的樹狀結構資料方法總結如下,希望能給有需要的小夥伴有所幫助！
/* 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;
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; }
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(); }
<?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": [] } ] } ] } ] } ]