More on Spring Batch Writer

Spring Batch Writer is the implementation of org.springframework.batch.item.ItemWriter. You can write a custom writer, but Spring Batch already has some useful implementations, such as

• org.springframework.batch.item.amqp.AmqpItemWriter for AMQP brokers such as RabbitMQ
• org.springframework.batch.item.file.FlatFileItemWriter for a file
• org.springframework.batch.item.database.JpaItemWriter for database using JPA
• org.springframework.batch.item.database.JdbcBatchItemWriter for writing to database with plain SQL

This post shows detailed information on using JdbcBatchItemWriter. (Refer to the javadoc for more information)

Passing parameters

JdbcBatchItemWriter supports traditional ? placeholder or named parameter. I recommend using named parameter because it is more readable. Named parameter placeholder starts with :. For example, the following SQL is using named parameters.

INSERT INTO TB_TARGET(NEW_COL1, NEW_COL2, NEW_COL3)
VALUES(:newCol1, :newCol2, :newCol3)

The parameters are set by ItemSqlParameterSourceProvider and Spring Batch already has the implementation. (BeanPropertyItemSqlParameterSourceProvider)

Using ItemSqlParameterSourceProvider

BeanPropertyItemSqlParameterSourceProvider is the implementation of ItemSqlParameterSourceProvider. It reads parameters from target VO, which is the return value of processor component. One rule is that target VO must have getter methods whose names are equivalent to the named parameters. For example, for above SQL, target VO has the following getter methods.

	public class TargetVO {
	public String new_col1;
	public String new_col2;
	public String new_col3;
	public String getNewCol1() {
	return this.new_col1;
	}
	public String getNewCol2() {
	return this.new_col2;
	}
	public String getNewCol3() {
	return this.new_col3;
	}
	}

view raw spring_batch_test.target_vo01 hosted with ❤ by GitHub

Debugging SQL

JdbcBatchItemWriter runs SQL in batch mode. Although batch mode is faster than non batch mode, it doesn’t show enough information even in debug mode. Therefore, to print SQL, we need capture it at JDBC driver level using Log4Jdbc.

1) Add dependency to Log4Jdbc. Refer to the following link to learn how to do it

2) Change driver class name into net.sf.log4jdbc.DriverSpy

3) Prefix jdbc url with jdbc:log4jdbc:. For example, for Oracle jdbc, jdbc:log4jdbc:oracle:thin:@guest01:1521:myora

4) Set logger level for jdbc.sqlonly, jdbc.audit and jdbc.sqltiming as needed