表结构（三个表）
汽车品牌表
汽车型号表 父级是汽车品牌表主键
汽车款式表 父级是汽车型号表主键id
业务层就不写了主要是xml层
实体类接受格式 一级套一级
public class ZqCarBrand implements Serializable {
private static final long serialVersionUID = 1L;
/** * 汽车品牌id */ private Long id;
/** * 汽车品牌名称 */ private String name;
/** * 汽车品牌字母 */ private String brandLetter;
/** * 车型list */ private List<ZqCarModel> children;
}
public class ZqCarModel implements Serializable {
private static final long serialVersionUID = 1L;
/** * 车型主键id */ private Long id;
/** * 汽车品牌id */ private Long parentId;
/** * model_name */ private String name;
/** * 汽车年份list */ private List<ZqCarYear> children;
}
public class ZqCarYear implements Serializable {
private static final long serialVersionUID = 1L;
/** * 汽车年份id */ private Long id;
/** * 汽车型号id */ private Long parentId;
/** * 车款名称 */ private String name;
}
下面的是最主要的：
<?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.ruoyi.zq.mapper.ZqCarBrandMapper"> <!-- 汽车年份实体 --> <resultMap id="CarYearResultMap" type="com.ruoyi.zq.domain.base.ZqCarYear" > <result column="year_id" property="id" /> <result column="model_id" property="parentId" /> <result column="name" property="name" /> </resultMap> <!-- 汽车型号 --> <resultMap id="CarModelResultMap" type="com.ruoyi.zq.domain.base.ZqCarModel" > <result column="model_id" property="id" /> <result column="brand_id" property="parentId" /> <result column="model_name" property="name" /> <collection property="children" ofType="CarYearResultMap" javaType="java.util.List" column="model_id" select="queryCarYearListByModelId"> </collection> </resultMap> <!-- 汽车品牌 --> <resultMap id="CarBrandtResultMap" type="com.ruoyi.zq.domain.base.ZqCarBrand" > <result column="brand_id" property="id" /> <result column="brand_name" property="name" /> <result column="brand_letter" property="brandLetter" /> <collection property="children" ofType="CarModelResultMap" javaType="java.util.List" column="brand_id" select="queryCarModelByBrandId"> </collection> </resultMap> <!-- 查询汽车品牌实体 --> <select id="selectCarList" resultMap="CarBrandtResultMap"> select * from zq_car_brand where is_del=0 order by brand_letter asc </select> <!--通过品牌id查询对应的车型 --> <select id="queryCarModelByBrandId" parameterType="java.lang.Long" resultMap="CarModelResultMap"> select * from zq_car_model where is_del=0 and brand_id=#{brandId} </select> <!--通过车型id查询对应的年份 --> <select id="queryCarYearListByModelId" parameterType="java.lang.Long" resultMap="CarYearResultMap"> select year_id,model_id,CONCAT_WS('款',car_year,name) as name from zq_car_year where is_del=0 and model_id=#{modelId} order by car_year desc </select> </mapper>
查询出来的格式是：
{ "id": 2, "name": "ARCFOX" "brandLetter": "A", "children": [{ "id": 3, "name": "ARCFOX LITE", "parentId": 2 "children": [{ "id": 7, "name": "2019款R300 原力版", "parentId": 3 }, { "id": 8, "name": "2019款R300 引力版", "parentId": 3 }, { "id": 9, "name": "2019款R300 魔力版", "parentId": 3 }, { "id": 10, "name": "2017款原力版", "parentId": 3 }, { "id": 11, "name": "2017款引力版", "parentId": 3 }], }, { "id": 4, "name": "ARCFOX αT", "parentId": 2 "children": [{ "id": 12, "name": "2021款480S", "parentId": 4 }, { "id": 13, "name": "2021款480S+", "parentId": 4 }, { "id": 14, "name": "2021款653S", "parentId": 4 }, { "id": 15, "name": "2021款653S+", "parentId": 4 }, { "id": 16, "name": "2021款四驱性能版 H", "parentId": 4 }], }], }
2.如果需要查询的是三级联动是在同一个表中，同理
实体如上一级套一级
<!-- 区实体 --> <resultMap id="areaResultMap" type="com.ruoyi.zq.domain.base.District" > <result column="area_id" property="id" /> <result column="pid" property="parentId" /> <result column="name" property="name" /> </resultMap> <!-- 市实体 --> <resultMap id="cityModelResultMap" type="com.ruoyi.zq.domain.base.City" > <result column="area_id" property="id" /> <result column="pid" property="parentId" /> <result column="name" property="name" /> <collection property="children" ofType="areaResultMap" javaType="java.util.List" column="area_id" select="queryAreaListByPid"> </collection> </resultMap> <!-- 省实体 --> <resultMap id="provinceResultMap" type="com.ruoyi.zq.domain.base.Province" > <result column="area_id" property="id" /> <result column="name" property="name" /> <collection property="children" ofType="cityModelResultMap" javaType="java.util.List" column="area_id" select="queryCityListByPid"> </collection> </resultMap> <!-- 查询省列表 --> <select id="selectProvincesList" resultMap="provinceResultMap"> select area_id,name from zq_area where level=1 </select> <!--通过省id查询对应的市 --> <select id="queryCityListByPid" parameterType="java.lang.Long" resultMap="cityModelResultMap"> select area_id,pid,name from zq_area where level=2 and pid=#{pid} </select> <!--通过市id查询对应的区域 --> <select id="queryAreaListByPid" parameterType="java.lang.Long" resultMap="areaResultMap"> select area_id,name,pid from zq_area where level=3 and pid=#{pid} </select>
这种查询起来的速度并不优秀 一般像三级联动或者省市区的三级联动 第一次可以使用嵌套查询 存入redis里面去 下次直接从redis里面去取数据 效率还是很优秀的
