SpringBoot获取树状结构数据-SQL处理
前言
在开发中，层级数据（树状结构）的获取往往可能是我们一大难点，我现在将自己获取的树状结构数据方法总结如下,希望能给有需要的小伙伴有所帮助！
一、测试数据准备
/* 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": [] } ] } ] } ] } ]
古今成大事者，不唯有超世之才，必有坚韧不拔之志！