Spring Batch - write to 2 tables at once

Question

I have a Person object that has a list of addresses. My Spring Batch app converts Person JSON recors into Person POJOs and writes them to a database.

Ordinarily, I'd use JdbcBatchItemWriter but I see it is limited in writing to 2 seperate tables. I need to write to Person and Address table.

Hibernate could write this in one step but I don't see a simialr capability in Spring Bathc.

This SO question Multiple itemwriters in Spring batch suggests using a CompositeItemWriter but the issue with that is, I want my Address table to hold a column called Person_id to link an address back to Person. I won't be able to do that with 2 writes.

Any suggestions? Surely Batch caters for more complex writes

you can use hibernate in your ItemWriter, just call your PersonService in it to save all the data, or it's something wrong with that? — Kacper Wolkowski, Apr 10 '18 at 14:39
This doesn't invalidate @KacperWolkowski; with SB compose your object and with PersonService persist it with your technology (hibernate, jdbc or any other one you are using for persistence) — Luca Basso Ricci, Apr 11 '18 at 06:48
@LucaBassoRicci He suggested using Hibernate inside an ItemWriter. As per my question, ItemWriter's only write to one database table. — TheCoder, Apr 11 '18 at 07:07
Yes. But he also suggested to make a call to `PersonService#save(Person)` in `ItemWriter`. And in `PersonService` you can use your preferred technology. — Luca Basso Ricci, Apr 11 '18 at 07:11

score 5 · Answer 1 · answered Apr 02 '20 at 17:37

I see from the comment that you can't use an ORM or a service that persists a Person with its addresses. In this case, you can use a custom writer for that:

import java.util.List;

import javax.sql.DataSource;

import org.springframework.batch.item.ItemWriter;
import org.springframework.jdbc.core.JdbcTemplate;

public class PersonWriter implements ItemWriter<Person> {

    private JdbcTemplate jdbcTemplate;

    public PersonWriter(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public void write(List<? extends Person> items) {
        for (Person person : items) {
            jdbcTemplate.update("INSERT INTO Person ...");
            for (Address address : person.addresses) {
                jdbcTemplate.update("INSERT INTO Address ... where person_id = ?", person.getId()); 
            }
        }
    }
}

Another way to do it to delegate to two jdbc batch item writers:

import java.util.Collections;
import java.util.List;

import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcBatchItemWriter;

public class PersonItemWriter implements ItemWriter<Person> {

    private JdbcBatchItemWriter<Person> personWriter;
    private JdbcBatchItemWriter<Address> addressWriter;

    public PersonItemWriter(JdbcBatchItemWriter<Person> personWriter, JdbcBatchItemWriter<Address> addressWriter) {
        this.personWriter = personWriter;
        this.addressWriter = addressWriter;
    }

    @Override
    public void write(List<? extends Person> persons) throws Exception {
        for (Person person : persons) {
            personWriter.write(Collections.singletonList(person));
            List<Address> addresses = person.getAddresses();
            for (Address address : addresses) {
                address.setPersonId(person.getId());
            }
            addressWriter.write(addresses);
        }
    }
}

Spring Batch - write to 2 tables at once

1 Answers1

Linked