Using Spring Batch to write csv records to different SQL tables based on a field value

Question

I'm working with an application where I'm required to translate various CSV files to an SQL database. Different CSV files may contain a variable number of columns but the first three will always be consistent between files.

The files are being read in just fine, and new database tables are created before the CSV is picked up as I know the combination of possible values. However, when it comes to the writer (I'm using a JdbcBatchItemWriter), I need to reference the newly created table names based on the values in these columns to determine which tables the data corresponds to - I won't know which row corresponds to which table until I look at each row.

Some code to illustrate this:

public JdbcBatchItemWriter<Form> writer(@Value("#{jobParameters}") Map<String,Object> jobParameters) {
    JdbcBatchItemWriter<Form> writer = new JdbcBatchItemWriter<Form>();
    ...
    String parameters = getParameters();
    writer.setSql(String.format("INSERT INTO tableName VALUES (%s)", parameters.toString()));

Basically, 'tableName' needs updating for each row that is processed.

It seems that ItemSqlParameterSourceProvider and ItemPreparedStatementSetter are designed for populating values in SQL query strings, but there isn't anything I can find to get the table name in as a parameter. Because I don't have access to each item at the level of the writer definition, I can't replace the value before the prepared statement is put together.

I've considered filtering items before they get there, but it's too messy for an unknown number of tables that might need to be entered into from the CSV. Any ideas?

A ClassifierCompositeItemWriter coupled with a custom Classifier should fits your need — Luca Basso Ricci, May 09 '17 at 06:48

score 0 · Accepted Answer · answered May 09 '17 at 05:35

Write your own writer, that keeps a map of writers. Everytime a new tablename appears, you can instantiate a new writer and store it in this map.

Instantiating a JdbcBatchItemWriter on the fly is no big deal (it does not have to be a spring bean).

public static <T> ItemWriter<T> createDbItemWriter(DataSource ds, String sql, ItemPreparedStatementSetter<T> psSetter) {
    JdbcBatchItemWriter<T> writer = new JdbcBatchItemWriter<>();

    writer.setDataSource(ds);
    writer.setSql(sql);
    writer.setItemPreparedStatementSetter(psSetter);

    writer.afterPropertiesSet();
    return writer;
}

Your writer will have to look somehow loke this (note: this code is not tested, it is just here to give you an idea)

public class MyWriter extends ItemWriter<MyDto> {

    private Map<String, JdbcBatchItemWriter<MyDto>> writersMaps = new HashMap<>();

    private JdbcBatchItemWriter<MyDto> getDbWriter(String tableName) throws Exception {
        return writersMaps.putIfAbsent(tableName, createJdbcWriter(tableName));

    }

    private JdbcBatchItemWriter<MyDto> createJdbcWriter(String tableName) {
          JdbcBatchItemWriter<T> writer = new JdbcBatchItemWriter<>();

          // do your configuration

          writer.afterPropertiesSet();
          return writer;
    }

    public void write(List<MyDto> items) throws Exception {
        Map<String, List<MyDto>> groupedItems = 
                --> build a list for every targetTableName, put in a Map


        for (Map.Entry<String, List<MyDto>> entry : groupedItems) {
            getDbWriter(entry.getKey()).write(entry.getValue);
        }
    }
}

Thanks for the advice. I ended up extending ClassifierCompositeItemWriter, which seems to be doing something along the lines of what you've described. — MattSteel, May 12 '17 at 16:00

Using Spring Batch to write csv records to different SQL tables based on a field value

1 Answers1