Code Foundry - Tutorials on Full-Stack Software Development Full-Stack software development

There are many different variants of #batchUpdate methods available in JdbcTemplate. We will specifically look into those who uses BatchPreparedStatementSetter and ParameterizedPreparedStatementSetter.

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.

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)));

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.

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 means that BatchPreparedStatementSetter executed single batch whereas ParameterizedPreparedStatementSetter executed multiple batches.

That's it. You can find the complete code of this example on Github.