How to write to multiple tables per read row using Spring Batch with no conditions

Question

I have found many examples to use multiple writers in this forum. Most, if not all, of the answers focus on CompositeItemWriter and ClassifierItemWriter.

Business Need: Read a single line from an input file. This line will contain multiple fields (over 50) which need to be written to their own database tables (in theory represent different classes).

                               ----- claimwriter(write to claim table)
                              /
                             /
claimlineitemprocessor  -----
                             \
                              \
                               ----- pharmacywriter(write to pharmacy table)

I have used a fieldset mapper to create the object representing the claim line (ClaimLine). Most of the fields are a simple mapping to the data in the file, but a few need to have their format changed or related field mapping logic.

Basic item writer code looks like this:

@SuppressWarnings({ "unchecked", "rawtypes" })
@Bean
public ItemWriter<ClaimLine> writer() {
    CompositeItemWriter<ClaimLine> cWriter = new CompositeItemWriter<ClaimLine>();

    JdbcBatchItemWriter claimWriter = new JdbcBatchItemWriter();
    claimWriter.setItemSqlParameterSourceProvider(new ClaimItemSqlParameterSourceProvider());
    claimWriter.setSql( // would like to insert into pharmacy table);
    claimWriter.setDataSource(dataSource);
    claimWriter.afterPropertiesSet();

    JdbcBatchItemWriter pharmacyWriter = new JdbcBatchItemWriter();
    pharmacyWriter.setItemSqlParameterSourceProvider(new PharmacyItemSqlParameterSourceProvider());
    pharmacyWriter.setSql( // would like to insert into pharmacy table);
    pharmacyWriter.setDataSource(dataSource);
    pharmacyWriter.afterPropertiesSet();

    List<ItemWriter<? super ClaimLine>> mWriter = new ArrayList<ItemWriter<? super ClaimLine>>();
    mWriter.add(claimWriter); 
    mWriter.add(pharmacyWriter);
    cWriter.setDelegates(mWriter);

    // other code

    return cWriter;
};

When creating the custom source providers, each of them seem to expect because that is the class that has already been mapped to the input line and contain the values I would like to send to the respective tables.

This is basically where I am now stuck thinking I cannot use a CompositeItemWriter because I am trying to transform a single object into two different ones. And ClassifierCompositeItemWriter works like a router and sends it down a path specific to a condition, which is not what I want to do.

For reference, I tried doing something similar with Spring Integration and also hit a similar roadblock.

Any help is appreciated.

Answer 1

I believe the comments from @Hansjoerg and @Luca provide valuable responses to this question and were researched prior to and during the research for an answer.

I was able to resolve this issue by continuing the use of a ItemSqlParameterSourceProvider and the code is below. When I initially explored how to use this class and its methods, my thought was that I was still only operating on the ClaimLine class.

What is truly happening is the method is receiving the class from the writer and you are setting the values of the SQL statement you set with setSQL(String sql). With the use of ItemSqlParameterSourceProvider, you are using the named parameters in the SQL for the put statement. The code below only shows the code for a claim. The pharmacy would be similar.

public class ClaimItemSqlParameterSourceProvider implements ItemSqlParameterSourceProvider<ClaimLine> {

    @SuppressWarnings({ "serial"})
    @Override
    public SqlParameterSource createSqlParameterSource(final ClaimLine item) {
        return new MapSqlParameterSource(new HashMap<String, Object>() {
            {
                put("rxclaimid", item.getRxClaimID());
                ...
                // many more
            }
        });
    }
}

A custom item writer may have also resolved this issue, but it seems to require more coding to support it. In the end, either using a ItemPreparedStatementSetter or a ItemSqlParameterSourceProvider should be fine for this case. The main reason we choose the latter is because the parameters are clearly named instead of accessing the parameter values by index values (1,2,3, etc) and using "?" in the setSQL call.