Batch Updates With JdbcTemplate

There may come a time when you are using JdbcTemplate and want to use a PreparedStatement for a batch update. In the example below, we will explore how to insert thousands of records into a MySQL database using batchUpdate.

First, we must configure the datasource to use in our application.properties.

spring.datasource.url=jdbc:mysql://localhost:3306/myurl
spring.datasource.username=root
spring.datasource.password=mypassword
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

Now in order to use this datasource in our Service.class, we need to make it Autowired. Below, I place the @Autowired annotation above the declaration of the DataSource, but depending on your preferences, it could also be placed over the method constructor.

@Autowired
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
 this.dataSource = dataSource;
 this.jdbcTemplate = new JdbcTemplate(dataSource);
}

In my example below, I have the method set to Private because the method should not be used outside of the class. However, depending on your own needs, you may make this Public.

Here is the full code. Below, I will explain the code line-by-line.

private void loadTheContent(String[] rows) throws SQLException {
 String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";
 JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
 jdbcTemplate.batchUpdate(Query, new BatchPreparedStatementSetter() {
  @Override
  public void setValues(PreparedStatement ps, int i) throws SQLException {
   String[] col = new String[4];
   col = rows[i].split("-");
   ps.setString(1, col[0]);
   ps.setString(2, col[1]);
   ps.setString(3, col[2]);
   ps.setString(4, col[3]);
  }

  @Override
  public int getBatchSize() {
   return rows.length;
  }
 });
 return;
}

An an array of Strings is passed into the loadTheContent method:

(String[] rows)

In my example, they are from a processed XML file. Covering XML processing is out of scope for this post, but will be covered in the future.

In order to successfully execute the batch prepared statement, we need to know the batch size — meaning how many records will be processed.

getBatchSize()

This is a request for the batch size. With the code below:

  return rows.length;

...we instruct the program to measure the size of the rows array in order to calculate the batch size.

Next, the query is defined.

String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";

Take note that the query String cannot have a keyword such as final next to it as it is not immutable. Also, as this is not using a NamedParameterJdbcTemplate, we must use ? placeholders for the values.

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

The above instantiation of jdbcTemplate allows us to execute the SQL to our correct database. Since dataSource is autowired, this will execute correctly.

Next, we actually define the batchUpdate. You may notice that the batchUpdate looks somewhat similar to a regular jdbcTemplate update.

BatchPreparedStatmentSetter provides an interface to set values on the PreparedStatement.

jdbcTemplate.batchUpdate(Query, new BatchPreparedStatementSetter() {
   @Override
   public void setValues(PreparedStatement ps, int i) throws SQLException {

The Query parameter is already defined as a String, which I discussed above. The int i is the current row that is being processed.

With this in mind, we set values for each column in the record. Below, you see that I create an Array of four values since I know that each record has four columns.

String[] col = new String[4];
col = rows[i].split("-");
ps.setString(1, col[0]);
ps.setString(2, col[1]);
ps.setString(3, col[2]);
ps.setString(4, col[3]);

I define each value in the col array by splitting at the - character in rows[i]. Next, we use each value in the col array to set the values for the SQL query.

This means that the ? placeholders below are replaced by col[0], etc.

String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";

For reference on JdbcTemplate.batchUpdate, see this documentation.

If you have any questions, ask in the comments and I will try to help!