很多人在用 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(); } } }
在上面例子中,在 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
由于在 Spring 集成的情况下,事务连接由 Spring 管理( SpringManagedTransaction
),所以这里不需要手动关闭 sqlSession
,在这里手动提交( commit
)或者回滚( rollback
)也是无效的。
批量提交只能应用于 insert, update, delete。
并且在批量提交使用时,如果在操作 同一SQL 时中间插入了其他数据库操作,就会让批量提交方式变成普通的执行方式,所以在使用批量提交时,要控制好 SQL 执行顺序。