Oracle,PostgreSQL存在即更新，不存在则插入Sql

PostgreSQL: INSERT INTO ... ON conflict(,) DO UPDATE SET ...(conflict里面的字段必须建唯一索引或联合唯一索引)

@Insert("<script>" +
            "   INSERT INTO CSP_PHASE_CODE_HISTORY(ACCOUNT_ID,PHASE_CODE,END_TIME,DESCRIPTION" +
            "   ,BANK_CODE)" +
            "   VALUES(#{accountId},#{phaseCode},#{startTime},#{endTime},#{description}," +
            "   #{bankCode})" +
            "   ON conflict(APPLY_NO,PHASE_CODE)" +
            "   DO UPDATE SET " +
            "  <if test=\"phaseCodeEndTime != null\">" +
            "        END_TIME = #{endTime}," +
            "   </if>" +
            "  <if test=\"phaseCodeStartTime != null\">" +
            "       START_TIME = #{startTime}," +
            "   </if>" +
            "  <if test=\"accountId != null\">" +
            "        ACCOUNT_ID = #{accountId}," +
            "   </if>" +
            "  <if test=\"bankCode != null\">" +
            "        BANK_CODE = #{bankCode}," +
            "   </if>" +
            "  <if test=\"phaseCodeDescription != null\">" +
            "       DESCRIPTION = #{description}," +
            "   </if>" +
            "  date_updated = now() " +
            "</script>" )
    void insertPhaseCodeHistory(Map<String,Object> map);

Oracle：使用存储过程：

写法一：

 @InsertProvider(type = BreakPointRecordMapperSql.class,method = "insertBreakPointRecordIfNotUpdate")
    public void insertBreakPointRecordIfNotUpdate(TieCardNodeDTO bean);


    public class BreakPointRecordMapperSql {

        public String insertBreakPointRecordIfNotUpdate(TieCardNodeDTO bean) {
                StringBuilder sb = new StringBuilder();
                sb.append("BEGIN ").append("\n");
                sb.append(updateBreakPointRecord(bean)).append(";").append("\n");
                sb.append("IF SQL%NOTFOUND THEN ").append("\n");
                sb.append(insertBreakPointRecord(bean)).append(";").append("\n");
                sb.append("END IF;").append("\n");
                sb.append("END;").append("\n");
                return sb.toString();
        }

        public String insertBreakPointRecord(TieCardNodeDTO bean) {
            return new SQL() {{
                INSERT_INTO("CSP_BREAKPOINT_RECORD");
                if(StringUtils.isNotBlank(bean.getAccountId()))     {   VALUES("ACCOUNT_ID", "#{accountId,jdbcType=VARCHAR}");}
                if(StringUtils.isNotBlank(bean.getToken()))         {   VALUES("TOKEN", "#{token,jdbcType=VARCHAR}");}
                if(StringUtils.isNotBlank(bean.getCurrentNode()))   {   VALUES("CURRENT_NODE", "#{currentNode,jdbcType=VARCHAR}");}
                if(StringUtils.isNotBlank(bean.getParamStr()))      {   VALUES("PARAM_STR", "#{paramStr,jdbcType=VARCHAR}");}
                if(StringUtils.isNotBlank(bean.getCreatedBy()))     {   VALUES("CREATED_BY", "#{createdBy,jdbcType=VARCHAR}");}
                if(StringUtils.isNotBlank(bean.getDateCreated()))   {   VALUES("DATE_CREATED", "#{createdDate,jdbcType=DATE}");}
                if(StringUtils.isNotBlank(bean.getUpdatedBy()))     {   VALUES("UPDATED_BY", "#{updatedBy,jdbcType=VARCHAR}");}
                if(StringUtils.isNotBlank(bean.getDateUpdated()))   {   VALUES("DATE_UPDATED", "#{updatedDate,jdbcType=DATE}");}
            }}.toString();
        }

        public String updateBreakPointRecord(TieCardNodeDTO bean) {
            return new SQL() {{
                UPDATE("CSP_BREAKPOINT_RECORD");
                SET("TOKEN = #{token,jdbcType=VARCHAR}");
                SET("CURRENT_NODE = #{currentNode,jdbcType=VARCHAR}");
                SET("PARAM_STR = #{paramStr,jdbcType=VARCHAR}");
                WHERE("ACCOUNT_ID = #{accountId}");
            }}.toString();
        }
    }

写法二：

@Update("<script>" +
            " declare" +
            " begin" +
            " update code_map set attribute1 = #{attribute1}" +
            " where code_id = #{codeId} ;" +
            " if sql%notfound then" +
            " insert into code_map(code_id,attribute1) values(#{codeId},#{attribute1});" +
            " end if;" +
            " end;" +
            "</script>")
public void updateData(Map map);

注意语法细节，不要漏了分号；