Mybatis大批量文件插入

测试类:

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class ToolBoxApplicationTest {
    Logger logger = LoggerFactory.getLogger(ToolBoxApplicationTest.class);

    @Autowired
    private TestNumberMapper testNumberMapper;

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

}

SQL:

CREATE TABLE `test_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `number` int(11) NOT NULL COMMENT '数量',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `time` datetime DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='数量测试';

XML:

    <insert id="insertList">
        INSERT INTO test_number
        (number, name, time)
        VALUES
        <foreach collection ="list" item="TestNumber" separator =",">
            (#{TestNumber.number}, #{TestNumber.name}, #{TestNumber.time})
        </foreach >
    </insert>

1. t1:循环单条插入 

@Test
    public void t1() {
        Long l1 = System.currentTimeMillis();
        // 10万
        for (int i = 1; i <= 100000; i++) {
            TestNumber testNumber = new TestNumber();
            testNumber.setNumber(i);
            testNumber.setName(String.valueOf(System.currentTimeMillis()));
            testNumber.setTime(new Date());
            testNumberMapper.insert(testNumber);
        }
        Long l2 = System.currentTimeMillis();
        logger.info("插入10万完成耗时: {}", l2 - l1);
    }

耗时较久

2. t2:单条批量插入 

    /**
     * t2:单条批量插入
     */
    @Test
    public void t2() {
        Long l1 = System.currentTimeMillis();
        // 10万
        List<TestNumber> list = new ArrayList<>();
        for (int i = 1; i <= 100000; i++) {
            TestNumber testNumber = new TestNumber();
            testNumber.setNumber(i);
            testNumber.setName(String.valueOf(System.currentTimeMillis()));
            testNumber.setTime(new Date());
            list.add(testNumber);
        }
        testNumberMapper.insertList(list);
        Long l2 = System.currentTimeMillis();
        logger.info("插入10万完成耗时: {}", l2 - l1);
    }

超长报错了 

3. t3:批处理单条插入

   /**
     * t3:批处理单条插入
     */
    @Test
    public void t3() {
        Long l1 = System.currentTimeMillis();
        // 10万
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
        for (int i = 1; i <= 100000; i++) {
            TestNumber testNumber = new TestNumber();
            testNumber.setNumber(i);
            testNumber.setName(String.valueOf(System.currentTimeMillis()));
            testNumber.setTime(new Date());

            testNumberMapper.insert(testNumber);
            //每1000条提交一次防止内存溢出
            if ((i) % 600 == 0) {
                session.commit();
                session.clearCache();
            }
        }
        // 以防取余遗漏最后,最后提交
        session.commit();
        session.clearCache();
        Long l2 = System.currentTimeMillis();
        logger.info("插入10万完成耗时: {}", l2 - l1);
    }

速度很快 

4. t4:批处理多条插入

    /**
     * t4:批处理多条插入
     */
    @Test
    public void t4() {
        Long l1 = System.currentTimeMillis();
        // 10万
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
        List<TestNumber> list = new ArrayList<>();
        for (int i = 1; i <= 100000; i++) {
            TestNumber testNumber = new TestNumber();
            testNumber.setNumber(i);
            testNumber.setName(String.valueOf(System.currentTimeMillis()));
            testNumber.setTime(new Date());
            list.add(testNumber);

            // 每1000条提交一次防止内存溢出
            if ((i) % 600 == 0) {
                testNumberMapper.insertList(list);
                session.commit();
                session.clearCache();
                // 清空list
                list.clear();
            }
        }
        // 以防取余遗漏,最后提交,插入剩余的数据
        testNumberMapper.insertList(list);
        session.commit();
        session.clearCache();
        Long l2 = System.currentTimeMillis();
        logger.info("插入10万完成耗时: {}", l2 - l1);
    }

速度最快