mybatis使用ExecutorType.BATCH方式批量插入数据

有从excel中导入大量数据到数据库这种需求,如果一条一条insert就会很慢,mybatis默认是ExecutorType.SIMPLE方式.
1.service代码

    @Autowired
    private SqlSessionFactory sqlSessionFactory;
    /**
     * 批量插入
     * @param entityList
     * @return
     */
    public int batchInsertEntity(List<Entity> entityList){
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try{
            EntityMapper mapper = session.getMapper(EntityMapper.class);
            entityList.forEach(item->{
                mapper.mergeInsertOneEntity(item);
            });
            session.commit();
        }
        finally {
            session.close();
        }
        return entityList.size();
    }

2.mapper.xml文件,我这里是写的是插入或更新语句,数据库是oracle,所以用merge into这种写法,判断条件,如果流水号存在,则更新,否则插入,mysql则是insert on duplicate key update 那种写法,如果这里不需要插入或更新,那就写普通的insert语句就可以了.

 <insert id="mergeInsertOneEntity"
            parameterType="Entity">
        MERGE INTO business_entity T1 USING (SELECT
        <trim prefix="" suffix="" suffixOverrides=",">
        <if test="payTime != null  and payTime != ''">#{payTime} as pay_time,</if>
        <if test="bankserialNumber != null  and bankserialNumber != ''">#{bankserialNumber} as bankserial_number,</if>
        </trim>
         FROM dual) T2
        ON ( T1.bankserial_number = T2.bankserial_number)
        WHEN MATCHED THEN
        UPDATE SET
        <trim prefix="" suffix="" suffixOverrides=",">
            <if test="payTime != null  and payTime != ''">T1.pay_time=T2.pay_time,</if>
        </trim>
        WHEN NOT MATCHED THEN
        INSERT
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="payTime != null  and payTime != ''">pay_time,</if>
            <if test="bankserialNumber != null  and bankserialNumber != ''">bankserial_number,</if>
        </trim>
        VALUES
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="payTime != null  and payTime != ''">T2.pay_time,</if>
            <if test="bankserialNumber != null  and bankserialNumber != ''">T2.bankserial_number,</if>
        </trim>
    </insert>

oracle的merge into 写法

MERGE INTO business_entity T1
USING (SELECT '0' AS BANKSERIAL_NUMBER,'2' as PAY_TIME FROM dual) T2
ON ( T1.BANKSERIAL_NUMBER = T2.BANKSERIAL_NUMBER)
WHEN MATCHED THEN
  UPDATE SET T1.PAY_TIME = T2.PAY_TIME
WHEN NOT MATCHED THEN 
  INSERT (BANKSERIAL_NUMBER, PAY_TIME) VALUES(T2.BANKSERIAL_NUMBER, T2.PAY_TIME);

3.经过测试,导入五千条数据,几秒钟就可以插入完成.
参考资料:https://mp.weixin.qq.com/s/AlhAXtHa3s1gBGWoDNajpg