mybatis三表联合查询

首先三个表的实体如下：全部是从下至上三个表都是一对多的关系

package com.dxn.dxnproject.bean;

import com.fasterxml.jackson.annotation.JsonProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @time 2019-12-10-10:35
 * 参数数据实体类
 */

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ParaData {

    @JsonProperty(value = "paraDataId")
    private Integer paraDataId;

    @JsonProperty(value = "paraDataName")
    private String paraDataName;

    @JsonProperty(value = "paraDataCode")
    private String paraDataCode;

    @JsonProperty(value = "paraDataDescription")
    private String paraDataDescription;

    @JsonProperty(value = "paraDataPrice")
    private BigDecimal paraDataPrice;

    @JsonProperty(value = "paraDataRemark")
    private String paraDataRemark;


    @JsonProperty(value = "paraTypeId")
    private Integer paraTypeId;

    @JsonProperty(value = "paraTypeName")
    private String paraTypeName;


    @JsonProperty(value = "paraDataCreateTime")
    private Date paraDataCreateTime;


    @JsonProperty(value = "paraDataUpdateTime")
    private Date paraDataUpdateTime;

}

package com.dxn.dxnproject.vo;

import com.dxn.dxnproject.bean.ParaData;
import com.fasterxml.jackson.annotation.JsonProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;
import java.util.List;

/**
 * @time 2019-12-10-15:01
 *
 * 参数类型和参数数据映射视图, 供三表查询使用
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ParaTypeDataVo {

    @JsonProperty(value = "paraTypeId")
    private Integer paraTypeId;

    @JsonProperty(value = "spuId")
    private Integer spuId;

    @JsonProperty(value = "spuName")
    private String spuName;

    @JsonProperty(value = "paraTypeName")
    private String paraTypeName;

    @JsonProperty(value = "paraTypeNameDescription")
    private String paraTypeNameDescription;

    @JsonProperty(value = "paraTypeCreateTime")
    private Date paraTypeCreateTime;

    @JsonProperty(value = "paraTypeUpdateTime")
    private Date paraTypeUpdateTime;

    @JsonProperty(value = "paraDataList")
    private List<ParaData> paraDataList;




}

package com.dxn.dxnproject.vo;

import com.dxn.dxnproject.bean.ParaType;
import com.fasterxml.jackson.annotation.JsonProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * @time 2019-12-10-15:04
 * 产品型号和参数类型试图,供三表查询使用
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SpuParaTypeVo {

    // 产品型号主键
    @JsonProperty(value = "spuId")
    private Integer spuId;

    // 产品型号名称
    @JsonProperty(value = "spuName")
    private String spuName;

    // 产品型号唯一表示码(以后用于生成二维码)
    @JsonProperty(value = "spuUniqueCode")
    private String spuUniqueCode;

    // 产品型号介绍
    @JsonProperty(value = "spuDescription")
    private String spuDescription;

    // 产品型号基础价格
    @JsonProperty(value = "spuPrice")
    private BigDecimal spuPrice;

    // 产品供货期
    @JsonProperty(value = "spuSupplyPeriod")
    private String spuSupplyPeriod;

    // 产品质保期
    @JsonProperty(value = "spuQualityPeriod")
    private String spuQualityPeriod;

    // 产品图片(一张url)
    @JsonProperty(value = "spuImage")
    private String spuImage;

    // 产品pdf文档,多个pdf
    @JsonProperty(value = "spuDocument")
    private String spuDocument;

    // 产品所属标签组合,以#分隔
    @JsonProperty(value = "spuTagGroup")
    private String spuTagGroup;

    // 产品上线状态(0:下线; 1:即将下线; 2:在售)
    @JsonProperty(value = "spuOnlineStatus")
    private Integer spuOnlineStatus;

    // 产品型号的互斥关系,字符串
    @JsonProperty(value = "spuMutexRelationship")
    private String spuMutexRelationship;

    // 产品型号预留字段1
    @JsonProperty(value = "spuReservedField1")
    private String spuReservedField1;

    // 产品型号预留字段2
    @JsonProperty(value = "spuReservedField2")
    private String spuReservedField2;

    // 产品型号创建时间
    @JsonProperty(value = "spuCreateTime")
    private Date spuCreateTime;

    // 产品型号更新时间
    @JsonProperty(value = "spuUpdateTime")
    private Date spuUpdateTime;

    @JsonProperty(value = "paraTypeList")
    private List<ParaType> paraTypeList;


}

mapper.xml

<!-- 三表联合查询 -->

<resultMap type="com.dxn.dxnproject.vo.SpuParaTypeVo" id="spuInfo">
    <result column="spu_id" property="spuId"/>
    <result column="spu_name" property="spuName"/>
    <result column="spu_unique_code" property="spuUniqueCode"/>
    <result column="spu_description" property="spuDescription"/>
    <result column="spu_price" property="spuPrice"/>
    <result column="spu_supply_period" property="spuSupplyPeriod"/>
    <result column="spu_quality_period" property="spuQualityPeriod"/>
    <result column="spu_image" property="spuImage"/>
    <result column="spu_document" property="spuDocument"/>
    <result column="spu_tag_group" property="spuTagGroup"/>
    <result column="spu_online_status" property="spuOnlineStatus"/>
    <result column="spu_mutex_relationship" property="spuMutexRelationship"/>
    <result column="spu_reserved_field1" property="spuReservedField1"/>
    <result column="spu_reserved_field2" property="spuReservedField2"/>
    <result column="spu_create_time" property="spuCreateTime"/>
    <result column="spu_update_time" property="spuUpdateTime"/>
    <collection property="paraTypeList" ofType="com.dxn.dxnproject.vo.ParaTypeDataVo">
        <result column="para_type_id" property="paraTypeId"/>
        <result column="spu_id" property="spuId"/>
        <result column="spu_name" property="spuName"/>
        <result column="para_type_name" property="paraTypeName"/>
        <result column="para_type_name_description" property="paraTypeNameDescription"/>
        <result column="para_type_create_time" property="paraTypeCreateTime"/>
        <result column="para_type_update_time" property="paraTypeUpdateTime"/>
        <collection property="paraDataList" ofType="com.dxn.dxnproject.bean.ParaData">
            <result column="para_data_id" property="paraDataId"/>
            <result column="para_data_name" property="paraDataName"/>
            <result column="para_data_code" property="paraDataCode"/>
            <result column="para_data_description" property="paraDataDescription"/>
            <result column="para_data_price" property="paraDataPrice"/>
            <result column="para_data_remark" property="paraDataRemark"/>
            <result column="para_type_id" property="paraTypeId"/>
            <result column="para_type_name" property="paraTypeName"/>
            <result column="para_data_create_time" property="paraDataCreateTime"/>
            <result column="para_data_update_time" property="paraDataUpdateTime"/>
        </collection>
    </collection>
</resultMap>

<select id="querySpuInfo" resultMap="spuInfo">
 select s.spu_id,s.spu_name,s.spu_unique_code,s.spu_description,s.spu_price,s.spu_supply_period,s.spu_quality_period,
 s.spu_image,s.spu_document,s.spu_tag_group,s.spu_online_status,s.spu_mutex_relationship,s.spu_reserved_field1,s.spu_reserved_field2,s.spu_create_time,s.spu_update_time,
 pt.para_type_id,pt.spu_id,pt.spu_name,pt.para_type_name,pt.para_type_name_description,pt.para_type_create_time,pt.para_type_update_time,
 pd.para_data_id,pd.para_data_name,pd.para_data_code,pd.para_data_description,pd.para_data_price,pd.para_data_remark,pd.para_type_id,pd.para_type_name,
 pd.para_data_create_time,pd.para_data_update_time
 from `spu` s
 left join `para_type` pt
 on s.spu_id = pt.spu_id
 left join `para_data` pd
 on pd.para_type_id = pt.para_type_id
 where s.spu_id=#{spuId}
</select>

mapper接口如下

// 8. 三表联合查询,给定spuId,查询该产品型号的所有参数类型以及参数数据
List<SpuParaTypeVo> querySpuInfo(Integer spuId);