表结构(三个表)
汽车品牌表
汽车型号表 父级是汽车品牌表主键
汽车款式表 父级是汽车型号表主键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:
<?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>
controller层直接调用 selectCarList这个方法就可以了 ,实现三级嵌套查询
查询出来的格式是:
{
"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>
controller层直接调用 selectProvincesList这个方法就可以了 ,实现三级嵌套查询
这种查询起来的速度并不优秀 一般像三级联动或者省市区的三级联动 第一次可以使用嵌套查询 存入redis里面去 下次直接从redis里面去取数据 效率还是很优秀的
最优的方法:下期公布 不过这种方法足够用了 应为这些不涉及到平凡的查询 直接做缓存就行可