PB using JdbcBatchItemWriter with CompositeItemWriter in JAVA code- Spring-Batch

Question

I have extended the batch service code provided by Official Spring-Batch site - Batch Processing Service and modified the ItemWriter to generate the CSV and to write to the database.

I have used CompositeItemWriter to write in both CSV and Database. However the JdbcBatchItemWriter is not working correctly with CompositeItemWriter. The code is shown below.

    @Bean
public ItemWriter<Person> writer(DataSource dataSource) {
CompositeItemWriter<Person> cWriter = new CompositeItemWriter<Person>();   

// For DataBase 
JdbcBatchItemWriter<Person> writer = new JdbcBatchItemWriter<Person>();
    writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<Person>());
    writer.setSql("INSERT INTO people (first_name, last_name) VALUES (:firstName, :lastName)");
    writer.setDataSource(dataSource);

// For CSV
FlatFileItemWriter<Person> csvWriter = new FlatFileItemWriter<Person>();
csvWriter.setResource(new FileSystemResource(new File("./csv/new-data.csv")));
csvWriter.setShouldDeleteIfExists(true);
DelimitedLineAggregator<Person> lineAggregator = new DelimitedLineAggregator<Person>();
lineAggregator.setDelimiter(","); 

BeanWrapperFieldExtractor<Person> fieldExtractor = new BeanWrapperFieldExtractor<Person>();
String[] names = {"firstName", "lastName"};
fieldExtractor.setNames(names);
lineAggregator.setFieldExtractor(fieldExtractor);
csvWriter.setLineAggregator(lineAggregator);

List<ItemWriter<? super Person>> mWriter = new ArrayList<ItemWriter<? super Person>>();
mWriter.add(writer); // **Comment this line and the code works fine**
mWriter.add(csvWriter);
cWriter.setDelegates(mWriter);
    return cWriter;
}

Comment this line - mWriter.add(writer); to run the code. This shows that CompositeItemWriter is working good with FlatFileitemWriter, but not with JdbcBatchItemWriter. The error I am getting is -

or.springframework.jdbc.BadSqlGrammerException: PreparedStatementCallback; bad SQL grammar 
[insert into people(first_name, last_name) VALUES (:firstName, :lastName)]

Caused by: Syntax error in SQl statement "insert into people((first_name, last_name) VALUES (:[*]firstName, :lastName)"; expected"), DEFAULT, NOT, EXISTS, INTERSECTS, SELECT, FROM"; SQL Statement:

How can I resolve JdbcBatchItemWriter to work correctly with CompositeItemWriter ?

Can you check your SQL, I see here double brackets... insert into person((first_name — Avis
I think you are checking the error (It's a typo). Please see the code instead which is correct. — Aditya Khajuria
other than a double ) why 'insert into person' and not 'insert into people'? — Luca Basso Ricci
It's a typo corrected it. But the problem is not with query, If I return writer from above code database insertion is working correctly, which means the above query is correct. — Aditya Khajuria

Avis Avis · Accepted Answer · 2015-09-19T03:35:17

Its the problem with jdbc auto config which was not running in case of multiple itemWriters. Pls add the last line in same sequence :-

JdbcBatchItemWriter<Person> writer = new JdbcBatchItemWriter<Person>();
writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<Person>());
writer.setSql("INSERT INTO people (first_name, last_name) VALUES (:firstName, :lastName)");
writer.setDataSource(dataSource);

writer.afterPropertiesSet();

PB using JdbcBatchItemWriter with CompositeItemWriter in JAVA code- Spring-Batch

1 Answers