Mybatis使用ExecutorType.BATCH批量插入数据

具体原理参考下方的资料
1.https://mp.weixin.qq.com/s/eyxq1kcH4i1jMr8RnyGWvA
2.https://blog.csdn.net/xlecho/article/details/102474146
3.https://blog.csdn.net/qq_40144558/article/details/93981183
4.https://blog.csdn.net/wlwlwlwl015/article/details/50246717


准备接口

public interface MybatisInsertTest {

    int manyInsertTest(TesttableEntity entity);
}
<?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.test.mapper.MybatisInsertTest">
    <insert id="manyInsertTest" parameterType="com.test.format.TesttableEntity">
      INSERT INTO testtable
        <trim prefix="(" suffix=")" suffixOverrides=",">
            birthday, birthday1
        </trim>
        values (
            <if test="birthday != null">
                #{birthday}
            </if>
            <if test="birthday1 != null">
                ,#{birthday1}
            </if>
        )
    </insert>
</mapper>

⏹service层

package com.example.demo.service;

import com.test.mapper.MybatisInsertTest;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Date;

@Service
public class MybatisInsertService {
	
	// SpringBoot工程中,SqlSessionTemplate可以直接注入
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    @Transactional
    public void testMybatisInsert() {
		
		// 准备1000条测试数据
        ArrayList<TesttableEntity> entityList = new ArrayList<>();
        TesttableEntity enntity = null;
        for (int i = 0; i < 1000; i++) {
            enntity = new TesttableEntity();
            enntity.setBirthday(new Date());
            enntity.setBirthday1(new Date());
            entityList.add(enntity);
        }

        // 如果自动提交设置为true,将无法控制提交的条数,会变成最后统一提交,有可能导致内存溢出
        // 设置为false,手动控制提交的时点
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        MybatisInsertTest mapper = session.getMapper(MybatisInsertTest.class);

        try {
            for (int i = 0; i < entityList.size(); i++) {
            	
            	// 进行数据插入(此时相当于准备插入的sql语句,并没有真正将数据入库)
                mapper.manyInsertTest(entityList.get(i));
                
                if (i % 100 == 0 || i == entityList.size() - 1) {
                    // 每100条提交一次,提交后无法回滚
                    session.commit();
                    // 刷新执行结果
                    session.flushStatements();
                    // 清理缓存,防止溢出
                    session.clearCache();
                }
            }
        } catch (Exception e) {
            // 回滚
            session.rollback();
        } finally {
            session.close();
        }
    }
}
上一篇:基于MNIST数据集的最优参数的比较


下一篇:Oracle下Mybatis Batch批量操作 vs JBCD 原生batch-prepared,及各写法效率测试。