前言
在开发中,层级数据(树状结构)的获取往往可能是我们一大难点,我现在将自己获取的树状结构数据方法总结如下,希望能给有需要的小伙伴有所帮助!
一、测试数据准备
/* 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, 1服务器托管网0013, '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 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": [] } ] } ] } ] } ]
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
相关推荐: 【Python 随练】一个整数,它加上 100 后是一个完全平方数,再加上 168 又是一个完全平方数,请问该数是多少
题目: 一个整数,它加上 100 后是一个完全平方数,再加上 168 又是一个完全平方数,请问该数是多少? 简介: 在本篇博客中,我们将解决一个数学问题:找到满足一定条件的整数。我们将提供问题的解析,并给出一个完整的代码示例来找出符合条件的整数。 问题分析: …