博客

判断需要插入的数据是否存在，如果不存在就更新

<insert id=”saveBatch” parameterType=”java.util.List” >



MERGE INTO 表名 T



USING (



<foreach collection=”list” item=”item” index=”index” separator=”union” >



SELECT SYS_GUID() AS CODE,



#{item.createDate,jdbcType=VARCHAR} AS CREATE_DATE,



#{item.createBy,jdbcType=VARCHAR} AS CREATE_BY,



#{item.flagDel,jdbcType=CHAR} AS FLAG_DEL,



#{item.flagDisplay,jdbcType=CHAR} AS FLAG_DISPLAY,



#{item.sort,jdbcType=DECIMAL} AS SORT FROM DUAL



</foreach>) T1



ON (T1./*判断数据是否重复的字段*/= T./*判断数据是否重复的字段*/ AND T1./*判断数据是否重复的字段*/ = T./*判断数据是否重复的字段*/)



/*数据存在*/



WHEN MATCHED THEN



UPDATE SET T./*需要更新的字段*/=T1/*需要更新的字段*/



/*数据不存在*/



WHEN NOT MATCHED THEN



INSERT



(CODE,CREATE_DATE,CREATE_BY,FLAG_DEL,FLAG_DISPLAY,SORT)



VALUES



(T1.CODE,T1.CREATE_DATE,T1.CREATE_BY,T1.FLAG_DEL,T1.FLAG_DISPLAY,T1.SORT)

</insert>

<insert id="batchSave" parameterType="java.util.List">
    MERGE INTO RES_SCHOOL_CLUB t
    USING (
      <foreach collection="list" item="item" index="index" separator="union" >
        select #{item.id,jdbcType=VARCHAR} ID,
        #{item.clsSchoolId,jdbcType=VARCHAR} CLS_SCHOOL_ID,
        #{item.originSchoolId,jdbcType=VARCHAR} ORIGIN_SCHOOL_ID,
        #{item.resourceId,jdbcType=VARCHAR} RESOURCE_ID,
        #{item.clsClubId,jdbcType=VARCHAR} CLS_CLUB_ID,
        #{item.baseAreaId,jdbcType=VARCHAR} BASE_AREA_ID,
        #{item.createTime,jdbcType=TIMESTAMP} CREATE_TIME
        from dual
      </foreach>) t1
    ON (t.CLS_SCHOOL_ID =  t1.CLS_SCHOOL_ID AND t.RESOURCE_ID = t1.RESOURCE_ID)
    WHEN MATCHED THEN
    UPDATE SET t.CREATE_TIME = t1.CREATE_TIME
    WHEN NOT MATCHED THEN
      INSERT(ID, CLS_SCHOOL_ID, ORIGIN_SCHOOL_ID, RESOURCE_ID, CLS_CLUB_ID, BASE_AREA_ID, CREATE_TIME)
      VALUES (t1.ID, t1.CLS_SCHOOL_ID, t1.ORIGIN_SCHOOL_ID, t1.RESOURCE_ID, t1.CLS_CLUB_ID, t1.BASE_AREA_ID, t1.CREATE_TIME)
</insert>