问题是这样的，我们有一个业务场景支持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)
