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);
注意语法细节,不要漏了分号;