Mybatis批量更新注解形式(Mysql和Oracle)

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);


}

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值