java mybatis查询数据库获取树形结构数据

数据库数据,每条数据都有code和parent_code,最顶级的parent_code为1

实体类

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 * 
 * </p>
 *
 * @author fzg
 * @since 2022-11-21
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class ChinaProvinceCity implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 自增aid
     */
    @TableId(value = "aid", type = IdType.AUTO)
    private Integer aid;

    /**
     * 市区的中文名
     */
    private String chineseName;

    /**
     * 市区的英文名
     */
    private String englishName;

    /**
     * 市区编码
     */
    private String code;

    /**
     * 市区上一级编码
     */
    private String parentCode;

    /**
     * 子节点
     */
    @TableField(exist = false)
    private List<ChinaProvinceCity> childNode;

    /**
     * 创建时间
     */
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;

    /**
     * 更新时间
     */
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;


}

在mapper接口中定义个方法

import com.fzg.entity.ChinaProvinceCity;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.util.ArrayList;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author fzg
 * @since 2022-11-21
 */
public interface ChinaProvinceCityMapper extends BaseMapper<ChinaProvinceCity> {

    public ArrayList<ChinaProvinceCity> getAllSort();

}

mapper.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.fzg.mapper.ChinaProvinceCityMapper">

    <resultMap id="BaseResultTreeMap" type="com.fzg.entity.ChinaProvinceCity">
        <id column="aid" jdbcType="INTEGER" property="aid"/>
        <result column="chinese_name" jdbcType="VARCHAR" property="chineseName"/>
        <result column="english_name" jdbcType="VARCHAR" property="englishName"/>
        <result column="code" jdbcType="VARCHAR" property="code"/>
        <result column="parent_code" jdbcType="VARCHAR" property="parentCode"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <!--使用mybatis  collection 进行集合查询-->
        <collection property="childNode" ofType="com.fzg.entity.ChinaProvinceCity" select="selectTree" column="code" javaType="java.util.ArrayList"/>
    </resultMap>

    <!--父级查询  从parent_code为1开始递归 -->
    <select id="getAllSort" resultMap="BaseResultTreeMap">
        select * from china_province_city s where s.parent_code = '1'
    </select>

    <!--关联集合查询-->
    <select id="selectTree" parameterType="String" resultMap="BaseResultTreeMap">
        select * from china_province_city s where s.parent_code=#{code}
    </select>

</mapper>

接下来在service层调用就可以了

https://files.cnblogs.com/files/blogs/699213/provinceCityCounty.json?t=1669084000