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

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.