Mybatis+Oracle批量插入方法总结

最近因为处理Oracle 12c GBK字符集情况下特殊符号（如Ø等）的问题，无法使用mabatisPlus的saveBatch方法，需要在mapper.xml中手写方法，又受框架所限，有些语法不支持，耗费了几天时间，最终通过下面地第一种方式实现，特在此总结一下。

乱码问题解决方法参考拙作：

Oracle 12c gbk字符集下解决特殊符号乱码问题_Chrisf Zhang的博客-CSDN博客 

方式一（可解决问题）

语法

insert into TableA(column1,column2)

(

select #{column1},#{column2} from dual

union all

select #{column1},#{column2} from dual

...

)

初次测试这种方式时，创建简单表可以实现，但在实际业务应用时，发现问题。union 的两侧要求字段数量一致，因为实际业务的不同，同一次批量插入的两条记录字段数量及具体的字段可能都不同，所以应用后报错。所以考虑手动创建session，循环插入后再关闭的方式处理。后来解决了union两侧字段数量不一致的问题，所以就没再使用手动创建session的方式，具体代码如下：

controller

    @ApiOperation("测试新增特殊符号-批量(方式七)")
    @PostMapping("/setCodes7")
    public void setCodes7(@RequestBody List<ZzCode> list){
        //方式七 用nvarcharHandler处理
        zzCodeMapper.setCodes7(list);
    }

 mapper

void setCodes7(List<ZzCode> list);

mapper.xml

    <!-- 方式七 (insert into select from union all select from...)，指定处理jdbcTypeHandler-->
    <insert id="setCodes8">
        INSERT INTO ZZ_CODE(CODE, REMARK, CODE_NO)
        <foreach collection="list" item="zzCode" separator="union all" open="(" close=")">
            select
            <trim suffixOverrides=",">
                <choose>
                    <when test="zzCode.code != null and zzCode.code !=''">
                        #{zzCode.code},
                    </when>
                    <otherwise>
                        NULL,
                    </otherwise>
                </choose>
                <choose>
                    <when test="zzCode.remark != null and zzCode.remark !=''">
                        #{zzCode.remark, jdbcType=NVARCHAR},
                    </when>
                    <otherwise>
                        NULL,
                    </otherwise>
                </choose>
                <choose>
                    <when test="zzCode.codeNo != null">
                        #{zzCode.codeNo},
                    </when>
                    <otherwise>
                        NULL,
                    </otherwise>
                </choose>
            </trim>
         from dual
        </foreach>
    </insert>

 这里insert into 后面列出所有字段，select后面通过<choose>标签，判断空值插入NULL,来保证union两侧字段一致。

值得注意的是，if test判断语句中，字符串需要判断!=null && != '',数字类型则只需要!=null，加上后面的判断会导致参数为0是错误。

测试表结构如下：

 以上已经可以解决批量插入的问题，在查阅资料的时候，还学到一种从

SqlSessionTemplate中取sqlSession，并手动处理事务的写法，也再次记录一下，以备后用。

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    @ApiOperation("测试新增特殊符号-批量(方式八)")
    @PostMapping("/setCodes8")
    public void setCodes8(@RequestBody List<ZzCode> list){
        //方式八 手动开启连接
        // 批量插入，手动控制事务
        SqlSession batchSqlSession = null;
        try {
            batchSqlSession = sqlSessionTemplate.getSqlSessionFactory()
                    .openSession(ExecutorType.BATCH, false);// 获取批量方式的sqlsession
            int batchCount = 1000;// 每批commit的个数
            int batchLastIndex = batchCount - 1;// 每批最后一个的下标
            for (int index = 0; index < list.size();) {
                if (batchLastIndex > list.size()) {
                    batchLastIndex = list.size();
                    batchSqlSession.insert(
                            "org.jeecg.modules.material.business.demand.mapper.ZzCodeMapper.setCodes",
                            list.subList(index, batchLastIndex));
                    batchSqlSession.commit();
                    break;// 数据插入完毕,退出循环
                } else {
                    batchSqlSession.insert(
                            "org.jeecg.modules.material.business.demand.mapper.ZzCodeMapper.setCodes8",
                            list.subList(index, batchLastIndex));
                    batchSqlSession.commit();
                    index = batchLastIndex;// 设置下一批下标
                    batchLastIndex = index + (batchCount - 1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            batchSqlSession.close();
        }
    }

方式二 （最终未能解决问题）

语法

begin

insert into TableA(column1,column2) values (#{column1},#{column2});

insert into TableA(column1,column2) values (#{column1},#{column2});

end;

因为该语法在之前的框架上是可以用的，因此也尝试的次数最多，在配置文件中配置“default-executor-type”参数后，短期内解决了问题，第二天重启后又不可用。配置useGeneratedKeys="false"参数，处理pageHelper与mybatis的jsqlparser依赖冲突等方法均未解决问题，应该是跟mybatis的版本有关，mybatis会自动把拼接在end后面的分号去掉，oracle报缺失分号的错误。很遗憾，最终也未能解决问题，后续有时间再通过更改mybatis版本的方式测试，先把过程中代码写在此处，该方式在部分框架或者版本中应该是可用的。具体代码如下：

controller

    @ApiOperation("测试新增特殊符号-批量（方式一）")
    @PostMapping("/setCodes")
    public void setCodes(@RequestBody List<ZzCode> list){
        zzCodeMapper.setCodes(list);
    }

mapper

void setCodes(List<ZzCode> list);

mapper.xml

    <!-- 方式一 （begin insert into ... end;）可用-->
    <insert id="setCodes" useGeneratedKeys="false">
        begin
        <foreach collection="list" item="zzCode">
            INSERT INTO ZZ_CODE
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="zzCode.code != null">CODE,</if>
                <if test="zzCode.remark != null">REMARK,</if>
            </trim>
            <trim prefix="values (" suffix=");" suffixOverrides=",">
                <if test="zzCode.code != null">#{zzCode.code},</if>
                <if test="zzCode.remark != null">utl_raw.cast_to_nvarchar2(#{zzCode.remark})</if>
            </trim>
        </foreach>
        end;
    </insert>

补充一下配置

 yml文件中的配置

mybatis-plus:
  configuration:
    # 支持begin insert into end方式批量插入
    default-executor-type: batch

值得注意的是，加上该配置之后，会导致insert及update返回的int值丢失。

方式三（最终未能解决问题）

语法

insert all

into TableA(column1,column2) values (#{column1},#{column2})

into TableA(column1,column2) values (#{column1},#{column2})

...

select 1 from dual

    <!-- 方式二 (insert all into into ... select)不可用-->
    <insert id="setCodes2">
        INSERT ALL
        <foreach collection="list" item="zzCode">
            INTO ZZ_CODE(CODE, REMARK) VALUES(#{zzCode.code}, utl_raw.cast_to_nvarchar2(#{zzCode.remark}))
        </foreach>
        select 1 from dual
    </insert>

该方式最终也没能解决问题，这里不再赘述。