During your software projects you must have come across an issue which includes bulk operations on relational database (mass updates, inserts, deletes).
As it occurs, performing single statements for bulk operations is lengthy due to the fact that each database action involves using (setting up in the worst case) a network connection, issuing the statement and waiting for results. So there must be an more optimal solution for this and of course there is such a solution, which goes by the name – “batch processing”. In this type of processing we group our statements into “batches” and send them all to the database using a single statement.
If you remember your database access techniques in Java right, then your first thought may be – “Yeah, but don’t I need to use plain, old JDBC to do batch processing?” – the good news is that – no you don’t. You can use MyBatis (https://mybatis.github.io/) – the favourite db access library at Pretius. In this post I write how to effectively use MyBatis batch processing capabilities and what types of shortcomings this approach has. If you’re into Spring and MyBatis you’ll go through this post in mere minutes.
MyBatis configuration for batch processing
First you need to configure MyBatis for batch processing:
|
1
2
3
4
5
6
7
8
9
10
|
@Bean
@Primary
public SqlSessionTemplate sqlSessionTemplate() throws Exception {
return new SqlSessionTemplate(sqlSessionFactory());
}
@Bean(name=MyBatisProperties.BATCH_SQL_SESSION_TEMPLATE)
public SqlSessionTemplate batchSqlSessionTemplate() throws Exception {
return new SqlSessionTemplate(sqlSessionFactory(), ExecutorType.BATCH);
}
|
We’re going to need two MyBatis session templates for different processing modes:
- standard – for standard / individual operations
- batch – for batch / bulk processing
Attention: In single transaction you may use only one processing mode.
With standard configuration all MyBatis mappers use by default the standard session template, so they don’t use the batch processing mode.
What we want, is to have one of the mappers to use the batch session, so we need a separate instance of the mapper:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
public final static String BATCH_FORECAST_MAPPER = “batchForecastMapper”;
@Autowired
@Qualifier(MyBatisProperties.BATCH_SQL_SESSION_TEMPLATE)
private SqlSessionTemplate batchSqlSessionTemplate;
@Bean
public MapperFactoryBean batchForecastMapper() {
MapperFactoryBean mapper = new MapperFactoryBean();
mapper.setMapperInterface(ForecastMapper.class);
mapper.setSqlSessionTemplate(batchSqlSessionTemplate);
return mapper;
}
|
Sample mapper:
|
1
2
3
4
5
6
7
8
|
public interface ForecastMapper {
void createForecast(@Param(“forecast”) Forecast forecast, @Param(“audit”) AuditData audit);
void updateForecast(@Param(“forecast”) Forecast forecast, @Param(“deleted”) boolean deleted, @Param(“audit”) AuditData audit);
@Flush
List flush();
}
|
As you can see, I’ve added an extra flush method to the mapper, so I can control when the batch is sent to the database – useful in situations when you want to control the batch size (this feature works since MyBatis 3.3.0). The result of the flush method is the number of rows affected by the batch.
flush method example call in a service class:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
@Autowired
@Qualifier(BatchConfiguration.BATCH_FORECAST_MAPPER)
private ForecastMapper batchForecastMapper;
...
if (!toUpdate.isEmpty()) {
for (ForecastUpdate forecast : toUpdate) {
batchForecastMapper.updateForecast(forecast, forecast.isDeleted(), auditData);
}
batchForecastMapper.flush();
}
for (ForecastUpdate forecast : toCreate) {
batchForecastMapper.createForecast(forecast, auditData);
// Oracle does not support useGeneratedKeys in batch update, but flush after each statement works.
batchForecastMapper.flush();
}
|
The flush method will be fired automatically with end of each transaction or with each called select statement.
Potential problems and solutions / workarounds
Using the generated keys with Oracle database
The generated keys mechanism, which allows to receive after a call to database all the keys generated through some sort of db mechanism (e.g. triggers with sequences) is a nice feature. Sometimes you’ll want to use this feature along with batch processing. Unfortunately if you’re working with Oracle database this will not be possible, as the JDBC driver for this RDBMS will throw an exception at you. To workaround this issue you’ll need to call the flush method each time you issue an insert statement. What it means is that if you’re on Oracle DB, want to use batch processing with generated keys you don’t get any advantage from batch processing approach.
Summary
If you’re looking for an efficient and developer-friendly for doing batch processing from your Java application than give MyBatis a try. It’s a fine choice when you want to have higher level of abstraction than plain, old JDBC and allows for easy setup and control of batch processing capabilities.