Oracle:
@Mapper
public interface ContactsMapper {
/***
* @Description: 参考https://www.jb51.net/article/132823.htm (利用存储过程);
* 利用存储过程主要是因为每条更新都是一个独立的sql,用“;”分开,而Oracle只支持"一条完整的sql";如“sql1;sql2”,会报无效字符错误,而批量删除恰好是这种格式;
* Mysql和postgreSQ则没有这种问题
* 非null非空字符串则更新
* @Date: 2019-10-17
*/
@Update(
"<script>" +
"<foreach collection='list' item='item' index='index' open='begin' close=';end;' separator=';' > " +
" update csp_appl_tel_list set" +
"<if test=\"item.name!=null and item.name!= ''\">name=#{item.name},</if>" +
"<if test=\"item.telNo!=null and item.telNo!= ''\">tel_no=#{item.telNo},</if>" +
"date_updated = sysdate" +
" where apply_no = #{item.applyNo} and loan_type = #{item.loanType}" +
"</foreach> " +
"</script>"
)
public void updateDataBitchOne(@Param("list") List<Map<String, Object>> list);
/***
* @Description: 方法二 (不对入参做校验)
* @Date: 2019-10-18
*/
@UpdateProvider(type = UpdateDataBitchTwo.class, method = "updateDataBitchTwo")
public void updateDataBitchTwo(@Param("list") List<Map<String, Object>> list);
public class UpdateDataBitchTwo {
public String updateDataBitchTwo(@Param("list") List<Map<String, Object>> list) {
StringBuilder sql = new StringBuilder();
sql.append(" begin ");
for (int i = 0; i < list.size(); i++) {
String val = "update csp_appl_tel_list set ";
sql.append(val);
MessageFormat mf = new MessageFormat(
" name = #'{'list[{0}].name,jdbcType=VARCHAR},tel_no = #'{'list[{0}].telNo,jdbcType=VARCHAR},date_updated = sysdate " +
"where apply_no = #'{'list[{0}].applyNo,jdbcType=VARCHAR} and loan_type = #'{'list[{0}].loanType,jdbcType=VARCHAR} "
);
sql.append(mf.format(new Object[]{i + ""}));
sql.append(";");
}
sql.append(" end;");
return sql.toString();
}
}
/***
* @Description: 方法三 (非null非空字符串则更新)
* 结合sql构造器对入参的校验相对比较灵活
* @Date: 2019-10-18
*/
@UpdateProvider(type = UpdateDataBitchThree.class, method = "updateDataBitchThree")
public void updateDataBitchThree(@Param("list") List<Map<String, Object>> list);
public class UpdateDataBitchThree {
public String updateDataBitchThree(@Param("list") List<Map<String, Object>> list) {
StringBuilder sql = new StringBuilder();
sql.append(" begin ");
for (int i = 0; i < list.size(); i++) {
Map map = list.get(i);
String sqlsub = new SQL() {
{
UPDATE("csp_appl_tel_list");
if (StringUtils.isNotBlank((String) map.get("name"))) {
SET("name = #'{'list[{0}].name,jdbcType=VARCHAR}");
}
if (StringUtils.isNotBlank((String) map.get("telNo"))) {
SET("tel_no = #'{'list[{0}].telNo,jdbcType=VARCHAR}");
}
SET("date_updated = sysdate ");
WHERE("apply_no = #'{'list[{0}].applyNo,jdbcType=VARCHAR} ");
WHERE("loan_type = #'{'list[{0}].loanType,jdbcType=VARCHAR} ");
}
}.toString();
MessageFormat mf = new MessageFormat(sqlsub);
sql.append(mf.format(new Object[]{i + ""}));
sql.append(";");
}
sql.append(" end ; ");
return sql.toString();
}
}
/***
* @Description: 方法四:批量修改的内容一致,条件不同
* 前三种方法的场景都是修改内容和条件都不相同
* @Date: 2019-10-18
*/
@Update(
"<script>" +
"update csp_appl_tel_list b " +
"set b.loan_type = #{loanType}, date_updated = sysdate " +
"where b.apply_no in " +
"<foreach item = 'item' index = 'index' collection = 'list' open = '(' separator = ',' close = ')'>" +
"#{item}" +
"</foreach>" +
"</script>")
int updateDataBitchFour(@Param("loanType") String loan_type, @Param("list") List<String> list);
}