MyBatis批量保存（Oracle）MyBatis批量插入、批量更新时，组装SQL过长会有问题，一定要根据批量插入数据量进行切割，再批次提交保存！！！

MyBatis批量插入（Oracle）

oracle 批量插入与mysql 的批量插入的方式不同

insert into tablename()values(),(),(); ---这个是mysql 的批量插入形式

insert all into tablename() values() into tablename() values() -------这个是Oracle批量插入形式

你可能遇到的错误：

1. 序列不存在=========> 没有创建与脚本中一致的序列号
2. 表格或者视图不存在(Cause: java.sql.SQLSyntaxErrorException: ORA-00942: 表格或視觀表不存在) =========> 数据库中的table name与脚本中的不一致
3. 命令未正确结束(ORA-00933: SQL 命令未正确结束 / ORA-00933: SQL command not properly ended ；) =========> mapper或者注解中SQL脚本不正确！
4. 字段类型对不上
5. 缺少字段
6. ...

示例批量插入（xml、注解）

xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.**.IOrderMapper">
    <resultMap id="Order" type="com.**.Order">
    </resultMap>

    <insert id="saveBatch" parameterType="com.**.Order" useGeneratedKeys="false">
        insert into TB_ORDER (
        order_id, order_no, booking_id, cif_no, bank_name_from, bank_name_to, bank_account_from, bank_account_to,
        currency_from, currency_to, order_type, amount_to, payee_username, amount_from, amount_total_from, status, remit_fee,
        remit_fee_currency)
        select SEQ_ORDER.NEXTVAL, A.* from(
        <foreach collection='list' item='item' index='index' separator='union all'>
        select
            #{item.orderNo}, #{item.bookingId}, #{item.cifNo}, #{item.bankNameFrom},
            #{item.bankNameTo}, #{item.bankAccountFrom}, #{item.bankAccountTo}, #{item.currencyFrom},
            #{item.currencyTo}, #{item.orderType}, #{item.amountTo},#{item.payeeUsername},
            #{item.amountFrom},#{item.amountTotalFrom},#{item.status},#{item.remitFee}, #{item.remitFeeCurrency}
            from dual
        </foreach>) A
    </insert>

</mapper>

注解(Update)

 @Insert("<script>" +
            "insert into TB_ORDER (order_id, order_no, booking_id, cif_no, bank_name_from, bank_name_to, bank_account_from, bank_account_to, currency_from, currency_to, order_type, amount_to, amount_from, amount_total_from, status, remit_fee, remit_fee_currency) select SEQ_ORDER.NEXTVAL, A.* from(" +
            "<foreach collection='list' item='item' separator='union all'> " +
            "select #{item.orderNo}, #{item.bookingId}, #{item.cifNo}, #{item.bankNameFrom}, #{item.bankNameTo}, #{item.bankAccountFrom}, #{item.bankAccountTo}, #{item.currencyFrom}, #{item.currencyTo}, #{item.orderType}, #{item.amountTo}, #{item.amountFrom},#{item.amountTotalFrom},#{item.status},#{item.remitFee}, #{item.remitFeeCurrency} from dual" +
            "</foreach>) A" +
            "</script>")
    int saveBatch(@Param("list") List<Order> list);

控制台打印SQL

insert into TB_ORDER (order_id, order_no, booking_id, cif_no, bank_name_from, bank_name_to, bank_account_from,
                           bank_account_to, currency_from, currency_to, order_type, amount_to, payee_username,
                           amount_from, amount_total_from, status, remit_fee, remit_fee_currency)
select SEQ_ORDER.NEXTVAL, A.*
from (select ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?
      from dual
      union all
      select ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?
      from dual
      union all
      select ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?
      from dual
      union all
      select ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?,
             ?
      from dual) A

 注意：

MyBatis批量插入时，组装SQL过长会有问题，一定要根据批量插入数据量进行切割，再批次提交保存！！！

MyBatis批量插入时，组装SQL过长会有问题，一定要根据批量插入数据量进行切割，再批次提交保存！！！

MyBatis批量插入时，组装SQL过长会有问题，一定要根据批量插入数据量进行切割，再批次提交保存！！！

MyBatis-plus 批量更新

2023-01-04 16:44:40.772 DEBUG  [d5d102af951326b2][d5d102af951326b2][] [io-30867-exec-5] c.m.c.r.d.I.updateBatch                  143 : ==>  Preparing: begin update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_ORDER set status = 0, remark = ? where order_id = ? ; update TB_CIPS_ORDER set status = 0, remark = ? where order_id = ? ;end; 
2023-01-04 16:44:40.773 DEBUG  [d5d102af951326b2][d5d102af951326b2][] [io-30867-exec-5] c.m.c.r.d.I.updateBatch                  143 : ==> Parameters: null, 666(Long), null, 668(Long), null, 669(Long), null, 670(Long), null, 686(Long), null, 743(Long), null, 301(Long), null, 302(Long), null, 695(Long), null, 526(Long)
2023-01-04 16:44:40.791 DEBUG  [d5d102af951326b2][d5d102af951326b2][] [io-30867-exec-5] o.m.s.SqlSessionUtils                    49 : Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@768d3f5f]

    @Update("<script>" +
            "begin" +
            "<foreach collection='list' item='item' separator=';'> " +
            " update TB_ORDER " +
            "set status = 0, remark = #{item.remark, jdbcType=VARCHAR} where order_id = #{item.orderId}" +
            "</foreach> ;end;" +
            "</script>")
    int updateBatch(@Param("list") List<Order> list);