Spring JDBC - How to perform batch update?

1. What is BatchPreparedStatementSetter?

It is an interface used by JdbcTemplate to execute batch updates. This has methods to determine the batch size and method to set parameters in the PreparedStatement. Using this, JdbcTemplate will run only execute single batch based on the batch size returned by implementation this interface.

2. How to use BatchPreparedStatementSetter?

Let's create a ProductBatchPreparedStatementSetter which can set parameters in the statement.

public class ProductBatchPreparedStatementSetter implements BatchPreparedStatementSetter {

  private final List products;

  public ProductBatchPreparedStatementSetter(List products) {
    Objects.requireNonNull(products);
    
    // Ideally you should do a defensive copy of this list.
    // this.products = new ArrayList<>(products);
    this.products = products;
  }

  @Override
  public void setValues(PreparedStatement ps, int i) throws SQLException {
    Product product = products.get(i);
    ps.setString(1, product.getName());
    ps.setString(2, product.getCategory());
    ps.setString(3, product.getDescription());
  }

  @Override
  public int getBatchSize() {
    return products.size();
  }

}

Usage

int[] results = jdbcTemplate.batchUpdate("insert into product (name, category, description) values(?,?,?)", 
    new ProductBatchPreparedStatementSetter(Arrays.asList(new Product("Lenovo Laptop", "laptop", "Thinkpad series laptop"),
 new Product("Acer Laptop", "laptop", "Predator series laptop"))));
log.info(() -> String.format("Inserted rows: %s", Arrays.toString(results)));

3. What is ParameterizedPreparedStatementSetter?

It is an interface used by JdbcTemplate to execute batch updates. It has only one method which takes PreparedStatement and Typed object as parameters. Using this, JdbcTemplate can execute multiple batch based on the batch size passed in the #batchUpdate method.

4. How to use ParameterizedPreparedStatementSetter?

Let's create a pretty straightforward implementation of this interface for our Product example.

ParameterizedPreparedStatementSetter<Product> pss = (ps, product) -> {
    ps.setString(1, product.getName());
    ps.setString(2, product.getCategory());
    ps.setString(3, product.getDescription());
  };

Usage

int batchSize = 5;
int[][] result = jdbcTemplate.batchUpdate("insert into product (name, category, description) values(?,?,?)",
    products, batchSize, pss);
log.info(Arrays.deepToString(result));

batchUpdate method which uses BatchPreparedStatementSetter returns 1-D int array whereas batchUpdate method which uses ParameterizedPreparedStatementSetter returns 2-D array. This also means that BatchPreparedStatementSetter executed single batch whereas ParameterizedPreparedStatementSetter executed multiple batches.

You can see the full example code on Github.

---

Tags: Spring Framework, Spring JDBC, BatchUpdate example with JdbcTemplate

← Back home