MyBatis通过BATCH批量提交的方法

MyBatis通过BATCH批量提交的⽅法
很多⼈在⽤ MyBatis 或者通⽤ Mapper 时，经常会问有没有批量插⼊和批量更新的⽅法。

实际上许多时候没必要⽤<foreach> 去实现特别复杂的批量操作。

直接通过 MyBatis 的 BATCH ⽅式执⾏增删改⽅法即可。

下⾯是⼀个批量⽤法的例⼦：
@Autowired
private SqlSessionFactory sqlSessionFactory;
@Transactional(rollbackFor = Exception.class)
@Override
public void batchTest() {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
List<Country> countries = mapper.selectAll();
for (int i = 0; i < countries.size(); i++) {
Country country = countries.get(i);
country.setCountryname(country.getCountryname() + "Test");
mapper.updateByPrimaryKey(country);
//每 50 条提交⼀次
if((i + 1) % 50 == 0){
sqlSession.flushStatements();
}
}
sqlSession.flushStatements();
}
在上⾯例⼦中，在Service中直接注⼊了SqlSessionFactory，通过下⾯⽅法获取了⼀个可以批量提交的SqlSession：SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
后续通过SqlSession直接执⾏⽅法，或者获取的Mapper接⼝，都使⽤的批量提交⽅式。

上述代码执⾏过程中输出的⽇志如下：
DEBUG - Creating new transaction with name [com.isea533.mybatis.service.impl.CountryServiceImpl.batchTest]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
DEBUG - Acquired Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@752c11a2] for JDBC
transaction
DEBUG - Switching JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@752c11a2] to
manual commit
DEBUG - JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@752c11a2] will be managed by Spring
DEBUG - ==> Preparing: SELECT Id,countryname,countrycode FROM country
DEBUG - ==> Parameters:
DEBUG - <== Total: 183
DEBUG - ==> Preparing: UPDATE country SET Id = Id,countryname = ?,countrycode = ? WHERE Id = ?
DEBUG - ==> Parameters: AngolaTest(String), AO(String), 1(Integer)
DEBUG - ==> Parameters: AfghanistanTest(String), AF(String), 2(Integer)
DEBUG - ==> Parameters: AlbaniaTest(String), AL(String), 3(Integer)
==========================================
...省略中间部分参数
==========================================
DEBUG - ==> Parameters: EthiopiaTest(String), ET(String), 50(Integer)
DEBUG - ==> Preparing: UPDATE country SET Id = Id,countryname = ?,countrycode = ? WHERE Id = ?
DEBUG - ==> Parameters: FijiTest(String), FJ(String), 51(Integer)
DEBUG - ==> Parameters: FinlandTest(String), FI(String), 52(Integer)
==========================================
...省略中间部分参数
==========================================
DEBUG - ==> Parameters: MadagascarTest(String), MG(String), 98(Integer)
DEBUG - ==> Parameters: MalawiTest(String), MW(String), 99(Integer)
DEBUG - ==> Parameters: MalaysiaTest(String), MY(String), 100(Integer)
DEBUG - ==> Preparing: UPDATE country SET Id = Id,countryname = ?,countrycode = ? WHERE Id = ?
DEBUG - ==> Parameters: MaldivesTest(String), MV(String), 101(Integer)
DEBUG - ==> Parameters: MaliTest(String), ML(String), 102(Integer)
==========================================
...省略中间部分参数
==========================================
DEBUG - ==> Parameters: South AfricaTest(String), ZA(String), 149(Integer)
DEBUG - ==> Parameters: SpainTest(String), ES(String), 150(Integer)
DEBUG - ==> Preparing: UPDATE country SET Id = Id,countryname = ?,countrycode = ? WHERE Id = ?
DEBUG - ==> Parameters: Sri LankaTest(String), LK(String), 151(Integer)
DEBUG - ==> Parameters: St.LuciaTest(String), LC(String), 152(Integer)
==========================================
...省略中间部分参数
==========================================
DEBUG - ==> Parameters: ZaireTest(String), ZR(String), 182(Integer)
DEBUG - ==> Parameters: ZambiaTest(String), ZM(String), 183(Integer)
==========================================
下⾯事务⾃动提交
==========================================
DEBUG - Initiating transaction commit
DEBUG - Committing JDBC transaction on Connection
[com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@752c11a2]
DEBUG - Releasing JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@752c11a2] after
transaction
DEBUG - Returning JDBC Connection to DataSource
注意事项
1. 事务
由于在 Spring 集成的情况下，事务连接由 Spring 管理（SpringManagedTransaction），所以这⾥不需要⼿动关闭sqlSession，在这⾥⼿动提交（commit）或者回滚（rollback）也是⽆效的。

2. 批量提交
批量提交只能应⽤于 insert, update, delete。

并且在批量提交使⽤时，如果在操作同⼀SQL时中间插⼊了其他数据库操作，就会让批量提交⽅式变成普通的执⾏⽅式，所以在使⽤批量提交时，要控制好 SQL 执⾏顺序。

总结
以上就是这篇⽂章的全部内容了，希望本⽂的内容对⼤家的学习或者⼯作具有⼀定的参考学习价值，谢谢⼤家对的⽀持。

如果你想了解更多相关内容请查看下⾯相关链接。