使用mybatis-collection级联功能进行递归查询

表结构（三个表）

汽车型号表父级是汽车品牌表主键

使用mybatis-collection级联功能进行递归查询

汽车款式表父级是汽车型号表主键id

使用mybatis-collection级联功能进行递归查询

业务层就不写了主要是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>


<select id="queryCarModelByBrandId" parameterType="java.lang.Long" resultMap="CarModelResultMap">
select * from zq_car_model where is_del=0 and brand_id=#{brandId}
</select>


<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.如果需要查询的是三级联动是在同一个表中，同理

使用mybatis-collection级联功能进行递归查询

实体如上一级套一级

<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>


<select id="queryCityListByPid" parameterType="java.lang.Long" resultMap="cityModelResultMap">
select area_id,pid,name from zq_area where level=2 and pid=#{pid}
</select>


<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里面去取数据效率还是很优秀的

使用mybatis-collection级联功能进行递归查询

相关推荐