How to use MyBatis to effectively perform batch database operations?

6 August 2015, Andrzej Karczyński

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:

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:

Sample mapper:

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:

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.

Tagged with: , , , , , , , , , , , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!