java多线程批量插入数据报错

Question

问题是这样的，我们有一个业务场景支持excel 批量导入上千条数据，且需要支持整批同时成功或失败，于是我打算将连接改成不自动提交，以手工开启事务形式处理，代码如下

 ExecutorService service = Executors.newFixedThreadPool(5);
    
    @Resource
    SqlSession sqlSession;
    
    @Test
    public void batchInsert() throws SQLException {
        Connection connection = sqlSession.getConnection();
        try {
            // 设置手动提交
            connection.setAutoCommit(false);
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            //执行子线程
            Student stu1 = new Student("张三");
            Student stu2 = new Student("李四");
            Student stu3 = new Student("王五");
            List<Student> students= Arrays.asList(stu1,stu2,stu3);
            List<Callable<Integer>> callableList  = new ArrayList<>();
            for (Student student : students) {
                Callable<Integer> callable=()-> studentMapper.addStudent(student);
                callableList.add(callable);
            }
            List<Future<Integer>> futures = service.invokeAll(callableList);
            for (Future<Integer> future:futures) {
                if (future.get()<=0){
                    connection.rollback();
                    return;
                }
            }
            connection.commit();
            System.out.println("添加完毕");
        }catch (Exception e){
            connection.rollback();
            log.info("error",e);
            throw new RuntimeException("002出现异常");
        }
        Scanner scanner=new Scanner(System.in);
        scanner.next();
    }

mysq和mybatis 配置如下

 @Bean("masterDataSource")
    @Primary
    public DataSource masterDatasource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        dataSource.setRemoveAbandonedTimeoutMillis(10*1000);
        dataSource.setRemoveAbandoned(true);
        dataSource.setTimeBetweenConnectErrorMillis(10*1000);
        return dataSource;
    }
    
    /***
     * 管理事务
     *
     * */
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDatasource());
    }
    
    /***
     * 创建sqlsessionFactory
     *
     * */
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();
        config.setMapUnderscoreToCamelCase(true);
        sessionFactory.setConfiguration(config);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MAPPER_LOCAL));
        sessionFactory.setPlugins(new SqlCostInterceptor(),new GlobalInterceptor());
        return sessionFactory.getObject();
    }

但现实很残酷，报错了
java.sql.SQLException: connection holder is null
at com.alibaba.druid.pool.DruidPooledConnection.checkStateInternal(DruidPooledConnection.java:1157)
at com.alibaba.druid.pool.DruidPooledConnection.checkState(DruidPooledConnection.java:1148)
at com.alibaba.druid.pool.DruidPooledConnection.commit(DruidPooledConnection.java:743)

Answer 1

如果你是springboot项目，像你说的那个批量处理数据什么的，你就用他自带的多线程方法ThreadPoolTaskExecutor，去处理，最后.join的方式加入到主线程，你只需要处理数据网数据库查的操作就行，ThreadPoolTaskExecutor的使用方式b站上有《SpringBoot项目如何使用线程池提升业务速度？》里面有这个的使用方式，是一个大佬老杨做的视频，视频时长2分零7秒，可以学习使用一下，像多线程挂钩的，很多现在都封装好的方式，没必要自己写代码，只需要完成业务逻辑就行