第一次在项目中使用left join right join记录。+myBatis selective方法(sql)使用加深理解记录。

最新推荐文章于 2024-08-13 13:02:56 发布

互联网cjfw

最新推荐文章于 2024-08-13 13:02:56 发布

阅读量959

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/qq_20597149/article/details/80008085

版权

SQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

=====①以前很少见。对left join right join存在的意义印象不是很深刻。

===right join尝试。【是用哪个，试试就行了】

===left join 效果ok

====实现：

<!--slot index方法new-->
    <select id="selectAdSlotList" resultType="com.ybl.td.resadmin.adslot.bean.AdSlotDTO">
  SELECT
   rm.media_name  mediaName,
   t4.dict_value mediaPlatformDictValue, /*  add wuhao： 显示广告位 所属的媒体平台 */
   t1.dict_value slotTypeDictValue,
   c.channel_type  channelType,
   t2.dict_value terminalDictValue ,
   t3.dict_value statusDictValue,
  res_ad_slot.id id, slot_name slotName, res_media_id resMediaId,
   slot_type  slotType, channel_id  channelId, terminal  terminal, STATUS STATUS,
   exposure_amount exposureAmount, click_amount  clickAmount,
   res_ad_slot.is_deleted  deleted,

    res_ad_slot.gmt_create  gmtCreate, res_ad_slot.gmt_modified  gmtModified,
    standard  standard, remark  remark,slot_json  slotJson
  FROM res_ad_slot
  INNER JOIN res_media rm ON rm.id=res_ad_slot.res_media_id AND rm.is_deleted=0
    INNER JOIN channel c ON  c.id=res_ad_slot.channel_id    AND c.is_deleted=0
    INNER JOIN td_dict t1 ON t1.dict_key=res_ad_slot.slot_type AND t1.dict_type='ad_slot_type' AND t1.is_deleted=0
    INNER JOIN td_dict t2 ON t2.dict_key=res_ad_slot.terminal AND t2.dict_type='terminal'  AND t2.is_deleted=0
    INNER JOIN td_dict t3 ON t3.dict_key=res_ad_slot.`status` AND t3.dict_type='status'  AND t3.is_deleted=0

  LEFT JOIN td_dict t4 ON
  t4.dict_key=res_ad_slot.`media_platform_id` AND t4.dict_type='media_platform'  AND t4.is_deleted=0
   /* add 回显媒体下媒体平台名称：比较特殊：【增加条件约束。确定是 dict的媒体平台的记录 和其他表关联】。
   条件不全导致显示 媒体平台 数据有误！*/
		AND t4.parent_id IS NOT NULL  AND t4.parent_id = rm.media_code /* 数据*/
/* 最后slotis_deleted 条件没加。在添加结果集的is_deleted=0约束【注意不是res_ad_slot.is_deleted】
res_ad_slot.is_deleted=0不生效。SQL不报错*/

  GROUP BY res_ad_slot.id HAVING  is_deleted=0
  </select>

====效果：

=====②额外：selective方法的使用。加深。

 <update id="updateByPrimaryKeySelective" parameterType="com.ybl.td.resadmin.adslot.bean.AdSlotDO">
        update res_ad_slot
        <set>
            <if test="slotName != null">
                slot_name = #{slotName,jdbcType=VARCHAR},
            </if>
            <if test="resMediaId != null">
                res_media_id = #{resMediaId,jdbcType=INTEGER},
            </if>

            /*  对于非必填字段。不要加if判断就行了。
            必须要用selective方法。可以避免很多 数据丢失导致错误可能性。 */
                media_platform_id = #{mediaPlatformId,jdbcType=TINYINT},

            <if test="slotType != null">
                slot_type = #{slotType,jdbcType=TINYINT},
            </if>
            <if test="channelId != null">
                channel_id = #{channelId,jdbcType=INTEGER},
            </if>
            <if test="terminal != null">
                terminal = #{terminal,jdbcType=TINYINT},
            </if>
            <if test="status != null">
                status = #{status,jdbcType=TINYINT},
            </if>
            <if test="exposureAmount != null">
                exposure_amount = #{exposureAmount,jdbcType=INTEGER},
            </if>
            <if test="clickAmount != null">
                click_amount = #{clickAmount,jdbcType=INTEGER},
            </if>
            gmt_modified =NOW(),
            <if test="standard != null">
                standard = #{standard,jdbcType=LONGVARCHAR},
            </if>
            <if test="remark != null">
                remark = #{remark,jdbcType=LONGVARCHAR},
            </if>
            <if test="slotJson != null">
                slot_json = #{slotJson,jdbcType=LONGVARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>