使用 BatchExecutor 批处理执行器进行批量插入

使用 BatchExecutor 批处理执行器进行批量插入

    @Autowired 
    private SqlSessionTemplate sqlSessionTemplate;

    /**
     * @Description: 使用 BatchExecutor 批处理执行器进行批量插入 每一千条提交一次 不足一千插完提交
     * @Author: Zongkai.Li
     */
    public void batchInsertData(List<UrbanComponent> urbanComponentList) {
        SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        UrbanComponentMapper urbanComponentMapper = sqlSession.getMapper(UrbanComponentMapper.class);
        Integer size = urbanComponentList.size();
        try {
            for (int i = 0; i < size; i++) {
                UrbanComponent urbanComponent = urbanComponentList.get(i);
                urbanComponentMapper.insertUrbanComponent(urbanComponent);
                if (i % 1000 == 0 || i == size - 1) {
                    sqlSession.commit();
                    sqlSession.clearCache();
                }
            }
        }catch (Throwable throwable){
            sqlSession.rollback();
            throwable.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

最重要的一点就是,JDBC连接URL字符串中需要新增一个参数：rewriteBatchedStatements=true

MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。
只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL，另外这个选项对INSERT/UPDATE/DELETE都有效；

如果在某些情况下获取不到SqlSessionTemplate，可以使用这个容器工具类进行获取Bean。

链接在此

还可以使用开线程的方式进行异步插入.

            Runnable runnable = new Runnable(){
                @Override
                public void run() {
                    SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
                    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
                    UrbanComponentMapper urbanComponentMapper = sqlSession.getMapper(UrbanComponentMapper.class);
                    Integer size = urbanComponentList.size();
                    try {
                        for (int i = 0; i < size; i++) {
                            UrbanComponent urbanComponent = urbanComponentList.get(i);
                            urbanComponentMapper.insertUrbanComponent(urbanComponent);
                            if (i % 1000 == 0 || i == size - 1) {
                                sqlSession.commit();
                                sqlSession.clearCache();
                            }
                        }
                    }catch (Throwable throwable){
                        sqlSession.rollback();
                        throwable.printStackTrace();
                    }finally {
                        sqlSession.close();
                    }
                }
            };
            Thread thread = new Thread(runnable);
            thread.setName("batchInsert:城市部件");
            thread.start();