mybatis四表联合查询

最新推荐文章于 2022-08-03 19:17:03 发布

原创最新推荐文章于 2022-08-03 19:17:03 发布 · 732 阅读

2 ·

CC 4.0 BY-SA版权

本文详细解析了MyBatis中四表联合查询的实现方法，包括如何定义resultMap映射复杂对象关系，以及如何通过左连接(left join)进行数据关联，以实现产品详细信息的全面检索。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<!-- 四表联合查询 -->
<resultMap type="com.dxn.dxnproject.vo.ProductDetailVo" 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_online_status" property="spuOnlineStatus"/>
    <result column="spu_create_time" property="spuCreateTime"/>
    <result column="spu_update_time" property="spuUpdateTime"/>
    <collection property="paraTypeVoList" ofType="com.dxn.dxnproject.vo.ParaTypeVo">
        <result column="para_type_id" property="paraTypeId"/>
        <result column="spu_id" property="spuId"/>
        <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="paraDataVoList" ofType="com.dxn.dxnproject.vo.ParaDataVo">
            <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_data_create_time" property="paraDataCreateTime"/>
            <result column="para_data_update_time" property="paraDataUpdateTime"/>
        </collection>
    </collection>
    <collection property="spuMutexVoList" ofType="com.dxn.dxnproject.vo.SpuMutexVo">
        <result column="spu_mutex_id" property="spuMutexId"/>
        <result column="spu_mutex_name" property="spuMutexName"/>
        <result column="spu_mutex_description" property="spuMutexDescription"/>
        <result column="spu_id" property="spuId"/>
    </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_online_status,s.spu_create_time,s.spu_update_time,
 pt.para_type_id,pt.spu_id,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_data_create_time,pd.para_data_update_time,sm.spu_mutex_id,sm.spu_mutex_name,sm.spu_mutex_description,sm.spu_id
 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
 left join `spu_mutex` sm
 on s.spu_id = sm.spu_id
 where s.spu_id=#{spuId}
</select>