Multiple sql insert statements in JdbcBatchItemWriterBuilder

[borjaDB]

Hello,

I'm working on a batch writing class using JdbcBatchItemWriter to write to database where I have two tables: customer and customer_viability.

I tried to add two inserts, one for each table in the .sql function verifying that it works with H2 but fails with Postgre or Oracle.

I'd like to know if it's possible to add multiple SQL statements in the same execution or, in any case, if the .sql function is designed for that.

Here my example:

public JdbcBatchItemWriter<Customer> writer(DataSource dataSource) {
	return new JdbcBatchItemWriterBuilder<Customer>()
			.itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>() {
				// code example
			})
			.sql("INSERT INTO customers (first_name, last_name, balance) VALUES (:firstName, :lastName, :balance); "
					+ "INSERT INTO customer_viability (first_name, last_name, viable) VALUES (:firstName, :lastName, :viable);")
			.dataSource(dataSource)
			.build();
}

Thanks in advance.

[fmbenhassine]

The JdbcBatchItemWriter uses JdbcOperations#execute() and NamedParameterJdbcOperations#batchUpdate() from spring-jdbc which both expect a single SQL statement.

If you want to write to multiple tables, then you need a custom writer or a composite one. The following links should help:

• Spring Batch - write to 2 tables at once
• Writing in multiple unrelated tables in spring batch writer

Let me know if this helps or if you need more support on this.

[borjaDB]

The information you sent through to me was perfect. Thanks for the reply.