Mybatis+Oracle数据增量Sql(有则忽略,没有则新增)

  接口：

Integer batchSave(@Param("areaPointVOS")List<AreaPointVO> areaPointVOS);

 实现：

    把所有对象的属性放到临时表B中，然后通过ON来匹配条件，注意在WHEN NOT MATCHED THEN中的插入语句并不是我们平时习惯的insert into 表名  这种写法。

<insert id="batchSave" parameterType="java.util.List">
        MERGE INTO 表名 A
        USING (<foreach collection="areaPointVOS" item="item" index="index" separator="union">
                select #{item.id} id,#{item.workId} workId,#{item.point} pointId,#{item.area} areaCode,
                    #{item.areaName} areaName,#{item.proId} proId,#{item.proName} proName,#{item.createBy} createBy,
                    #{item.createPostId} createPostId,#{item.createByOrg} createByOrg,#{item.createLoginName} createLoginName,
                    #{item.createTime} createTime
                from dual
            </foreach>) B
        ON (A.CHECK_WORK_ID=B.workId and A.CHECK_POINT_ID=B.pointId and A.REVIEW_AREA_CODE=B.areaCode)
        WHEN NOT MATCHED THEN
        insert
        (ID,
        CHECK_WORK_ID,
        CHECK_POINT_ID,
        REVIEW_PROV_CODE,
        REVIEW_PROV_NAME,
        REVIEW_AREA_CODE,
        REVIEW_AREA_NAME,
        CREATE_BY,
        CREATE_LOGIN_NAME,
        CREATE_POST_ID,
        CREATE_BY_ORG,
        CREATE_TIME)
        values
        (B.id, B.workId, B.pointId, B.proId, B.proName, B.areaCode, B.areaName, B.createBy,
        B.createLoginName, B.createPostId, B.createByOrg,B.createTime)
    </insert>