java mybatis多层collection嵌套查询

                                                                                                                                                    java mybatis多层collection嵌套查询

1.实体

package com.humi.iem.common.model.equipment;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

/*设备树返回参数*/
@Data
public class EquipmentTreeResponse {
    @ApiModelProperty(value="主键")
    private String id;

    @ApiModelProperty(value="设备类型名称")
    private String equipmentTypeName;

    @ApiModelProperty(value="下级")
    private List<EquipmentFirmResponse> childDic;

}

@Data
public class EquipmentFirmResponse {
    @ApiModelProperty(value="主键")
    private String id;

    @ApiModelProperty(value="设备厂商名称")
    private String equipmentFirmName;

    @ApiModelProperty(value="下级")
    private List<EquipmentBrandResponse> childDic;

}

@Data
public class EquipmentBrandResponse {
    @ApiModelProperty(value="主键")
    private String id;

    @ApiModelProperty(value="设备品牌名称")
    private String equipmentBrandName;

    @ApiModelProperty(value="下级")
    private List<EquipmentSpecificationResponse> childDic;

}

@Data
public class EquipmentSpecificationResponse {
    @ApiModelProperty(value="主键")
    private String id;

    @ApiModelProperty(value="设备规格名称")
    private String equipmentSpecificationName;

}

 

2.mapper映射层

<?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.humi.iem.common.dao.EquipmentTypeMapper">

    <resultMap id="EquipmentTreeMap" type="com.humi.iem.common.model.equipment.EquipmentTreeResponse">
        <id property="id" column="etid" />
        <result property="equipmentTypeName" column="equipment_type_name" />
        <collection property="childDic" ofType="com.humi.iem.common.model.equipment.EquipmentFirmResponse" resultMap="firmList" />
    </resultMap>

    <resultMap id="firmList" type="com.humi.iem.common.model.equipment.EquipmentFirmResponse">
        <id property="id" column="efid" />
        <result property="equipmentFirmName" column="equipment_firm_name" />
        <collection property="childDic" ofType="com.humi.iem.common.model.equipment.EquipmentBrandResponse" resultMap="brandList" />
    </resultMap>

    <resultMap id="brandList" type="com.humi.iem.common.model.equipment.EquipmentBrandResponse">
        <id property="id" column="ebid" />
        <result property="equipmentBrandName" column="equipment_brand_name" />
        <collection property="childDic" ofType="com.humi.iem.common.model.equipment.EquipmentSpecificationResponse" resultMap="specificationList" />
    </resultMap>

    <resultMap id="specificationList" type="com.humi.iem.common.model.equipment.EquipmentSpecificationResponse">
        <id property="id" column="esid" />
        <result property="equipmentSpecificationName" column="equipment_specification_name" />
    </resultMap>


    <select id="queryEquipmentTree" resultMap="EquipmentTreeMap">
        select
        et.id etid
       ,et.equipment_type_name
       ,ef.id efid
       ,ef.equipment_firm_name
       ,eb.id ebid
       ,eb.equipment_brand_name
       ,es.id esid
       ,es.equipment_specification_name
        from
        equipment_type et
        left join equipment_firm ef on et.id=ef.type_id
        left join equipment_brand eb on ef.id=eb.firm_id
        left join equipment_specification es on eb.id=es.brand_id
    </select>
</mapper>