Mybatis批量更新注解形式（Mysql和Oracle）

最新推荐文章于 2024-07-03 19:16:36 发布

码上得天下

最新推荐文章于 2024-07-03 19:16:36 发布

阅读量758

点赞数

分类专栏： Mybatis

本文链接：https://blog.csdn.net/meser88/article/details/102553379

版权

Mybatis 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

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


}