mysql 查询树类型的层级数据(父子，层层递归)

一、创建表

CREATE TABLE `sys_station` (
  `row_id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `project_id` int DEFAULT NULL,
  `name` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `code` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `alias` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `type` tinyint DEFAULT NULL,
  `latitude` double DEFAULT NULL COMMENT '纬度',
  `longitude` double DEFAULT NULL COMMENT '经度',
  `parent_id` int DEFAULT NULL,
  `img_url` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `remark` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_by` int NOT NULL COMMENT '记录创建人',
  `created_time` datetime DEFAULT NULL COMMENT '记录创建时间',
  `updated_by` int NOT NULL COMMENT '记录修改人',
  `updated_time` datetime DEFAULT NULL COMMENT '记录修改时间',
  `version` int NOT NULL COMMENT '记录修改次数',
  `is_deleted` tinyint NOT NULL COMMENT '逻辑删除标识',
  PRIMARY KEY (`row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='站点';

二、实体类

package com.basic.cloud.platform.module.system.entity.device;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.basic.cloud.platform.core.bean.BaseEntity;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.List;

/**
 * 站点表
 */
@Data
@TableName("sys_station")
@EqualsAndHashCode(callSuper = true)
public class Station extends BaseEntity<Integer, Station> {

    /**
     * 编码
     */
    private Integer projectId;
    /**
     * 站点编码
     */
    private String code;

    /**
     * 站点名称
     */
    private String name;
    /**
     * 站点别名
     */
    private String alias;
    /**
     * 编码
     */
    private Integer type;
    /**
     * 纬度
     */
    private Double latitude;
    /**
     * 经度
     */
    private Double longitude;
    /**
     * 父id
     */
    private Integer parentId;
    /**
     * 编码
     */
    private String imgUrl;
    /**
     * 描述
     */
    private String remark;

    /**
     * 子集站点信息
     */
    @TableField(exist = false)
    List<Station> subStation;
}

三、xml

<?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.basic.cloud.platform.module.system.dao.device.StationMapper">

    <resultMap id="stationMap" type="com.basic.cloud.platform.module.system.entity.device.Station">
        <id column="row_id" property="rowId"></id>
        <result column="project_id" property="projectId"></result>
        <result column="name" property="name"></result>
        <result column="code" property="code"></result>
        <result column="alias" property="alias"></result>
        <result column="type" property="type"></result>
        <result column="latitude" property="latitude"></result>
        <result column="longitude" property="longitude"></result>
        <result column="parent_id" property="parentId"></result>
        <result column="img_url" property="imgUrl"></result>
        <result column="remark" property="remark"></result>
        <collection property="subStation" column="row_id"  select="queryStationInfoByCondition"></collection>
    </resultMap>

    <select id="queryStationInfoByCondition" resultMap="stationMap">
        select * from sys_station where parent_id = #{rowId}
    </select>
</mapper>

 四、Dao层

package com.basic.cloud.platform.module.system.dao.device;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.basic.cloud.platform.module.system.entity.device.Station;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface StationMapper extends BaseMapper<Station> {

    List<Station> queryStationInfoByCondition(@Param("rowId") Integer rowId);
}

好了，到此，代码完成，可以直接调用Dao层方法去获取树型数分层据了。