转载

MyBatis 批量更新

很多人在用 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

注意事项

1. 事务

由于在 Spring 集成的情况下,事务连接由 Spring 管理( SpringManagedTransaction ),所以这里不需要手动关闭 sqlSession ,在这里手动提交( commit )或者回滚( rollback )也是无效的。

2. 批量提交

批量提交只能应用于 insert, update, delete。

并且在批量提交使用时,如果在操作 同一SQL 时中间插入了其他数据库操作,就会让批量提交方式变成普通的执行方式,所以在使用批量提交时,要控制好 SQL 执行顺序。

原文  https://blog.csdn.net/isea533/article/details/80922305
正文到此结束
Loading...