Spring Batch: How to Insert multiple key-value pairs into Database table for each item

Question

After processing some XML files with Spring Batch ItemProcessor. The ItemProcessor returns items like this:

MetsModsDef
{
   int id;
   String title;
   String path;
   Properties identifers;
   ....
}

now i need to save this items into a database, so that the (id, title, path) will go into the "Work" table

and all the Properties stored in the "identifiers" field go into a "Key/Value"-Table called "Identifier" (work, identitytype, identityValue)

how can i acheive this?

currently i am using a CompositeItemWriter to split the object and write it into two tables like this:

   public ItemWriter<MetsModsDef> MultiTableJdbcWriter(@Qualifier("dataSource") DataSource dataSource) {

    CompositeItemWriter<MetsModsDef> cWriter = new CompositeItemWriter<MetsModsDef>();

    JdbcBatchItemWriter hsqlWorkWriter = new JdbcBatchItemWriterBuilder()
        .itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
        .sql("INSERT INTO work (id, title, path,enabled) VALUES (:id, :title,:path,1)" )
        .dataSource(dataSource)
        .build();

    JdbcBatchItemWriter hsqlIdentifierWriter = new JdbcBatchItemWriterBuilder()
        .itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
        .sql("INSERT INTO identity (work, identitytype, identityValue) VALUES (:work, :identitytype, :identityValue)" )
        .dataSource(dataSource)
        .build();

    List<ItemWriter<? super MetsModsDef>> mWriter = new ArrayList<ItemWriter<? super MetsModsDef>>();
    mWriter.add(hsqlWorkWriter);
    mWriter.add(hsqlIdentifierWriter);
    cWriter.setDelegates(mWriter);

but this will not work for a propertylist since (work, identitytype, identityValue) are not part of my domain object MetModsDef which only contains one map of properties which are supposed to go into the Identifier table.

i have found advice on how to do it when writing to a file, and even on using a splitter pattern from Spring-Integration Read one record/item and write multiple records/items using spring batch but i am still not sure how to actually do it, when writing out via jdbc or hibernate (which i assume would be similarish ) thanks for your advice !

score 1 · Answer 1 · answered Apr 04 '18 at 15:47

in case somebody is interested: after a while i have come up with own solution: I have found one extending HibernateItemWriter (for hibernate writes) on the internet:

Spring-Batch Multi-line record Item Writer with variable number of lines per record but i did not want to extend classes, so i had to come up with my own (based on what i could research on the internet).

I am not sure how good is, and how it will handle transactions or rollback (probably bad). but for now it is the only one i have. So if you need one too, or have comments on how to improve it. or even have a better one. You are very welcome. i have created my own IdentifierListWriter which creates the Key/value-pairs-like-objects (here each pair is called "identitifier") for each MetsModsDef Item and writes out them all using JdbcBatchItemWriter identifierWriter wich is passed to it from the configuration

    public class IdentifierListWriter implements ItemWriter<MetsModsDef>
{
    private ItemWriter<Identifier>      _identifierWriter;

    public IdentifierListWriter ( JdbcBatchItemWriter<Identifier> identifierWriter )
    {
        _identifierWriter= identifierWriter;
    }

    @Transactional(readOnly = false, propagation = Propagation.REQUIRED)
    public void write(List<? extends MetsModsDef> items) throws Exception
    {
        // Main Table WRITER
        for ( MetsModsDef item : items )
        {
            ArrayList<Identifier> ids = new ArrayList<Identifier>();
            for(String key : item.getAllIds().stringPropertyNames())
            {
             ids.add(new Identifier(item.getAllIds().getProperty(key),
                     key, item.getId()));
            }
            _identifierWriter.write(ids);
      }
    }
}

In the java configuration i create two jdbcBatchItemWriter Beans. One for the "Work" table and one for the "identifier" table. IdentifierListWriter bean and a CompositeItemWriter MultiTableJdbcWriter Bean which uses them all to write out the object

 @Bean
@Primary
public ItemWriter<MetsModsDef> MultiTableJdbcWriter(@Qualifier("dataSource") DataSource dataSource) {

    IdentifierListWriter identifierListWriter = new IdentifierListWriter(identifierWriter(dataSource) );

    CompositeItemWriter cWriter = new CompositeItemWriter();
    cWriter.setDelegates(Arrays.asList(hsqlWorkWriter(dataSource),identifierListWriter));
    return cWriter;
}

@Bean
public JdbcBatchItemWriter<MetsModsDef> hsqlWorkWriter(@Qualifier("dataSource") DataSource dataSource) {
    return new JdbcBatchItemWriterBuilder<MetsModsDef>()
            .itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
            .sql("INSERT INTO work (id, title, path,enabled) VALUES (:id, :title,:path,1)")
            .dataSource(dataSource)
            .build();

}

@Bean
public JdbcBatchItemWriter<Identifier> identifierWriter(@Qualifier("dataSource") DataSource dataSource) {
    return new JdbcBatchItemWriterBuilder()
            .itemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>())
            .sql("INSERT INTO identifier (identifier, type, work_id) VALUES ( :identifier, :type, :work)")
            .dataSource(dataSource)
            //.afterPropertiesSet()
            .build();
}

then the multiTableJdbcWriter is called from a Step:

   @Bean
public Step step1(ItemWriter<MetsModsDef> multiTableJdbcWriter) {
    return stepBuilderFactory.get("step1")
            .<StreamSource, MetsModsDef>chunk(1)
            .reader(new MetsModsReader())
            .processor(metsModsFileProcessor())
            .writer(multiTableJdbcWriter)

Spring Batch: How to Insert multiple key-value pairs into Database table for each item

1 Answers1