Spring Batch Writer for Parent Child Table Data Load in Batch

Question

Being new to Spring Batch, I am making a good progress by exploring some POCs. One of the POCs I am currently working on is about reading data from csv file and loading into database tables which have parent child relationships.

CSV file now contains customers and orders info as below.

1. custname1, custaddress1, custzip1, orderdate11, ordervalue11, ordertax11
2. custname1, custaddress1, custzip1, orderdate12, ordervalue12, ordertax12
3. custname2, custaddress2, custzip2, orderdate21, ordervalue21, ordertax21
4. custname2, custaddress2, custzip2, orderdate22, ordervalue22, ordertax22

These data will be loaded into DB tables Customer, Order where CustId and OrderId will be auto generated by Oracle Sequences and table Order has CustId as a foreign key for maintaining 1 to many relationship ( 1 Customer can have multiple Orders )

FlatFileItemReader, FieldSetMapper, ItemWriter are the interfaces I am employing in this POC and I am using JdbcTemplate for DB operations.

I have following questions. Please clarify.

1) How to design my Reader bean?

The model class Customer has its members, getters and setters methods. Same with model class Order.

Should Customer class ( reading data from CSV file ) contain a member for Order class and corresponding getter, setter methods in order to represent Parent - Child Relationship?

2) How to retrieve primary key which I am currently creating it within the INSERT sql for Customer using sequence.nextval?

3) How to store all the Primary Keys from Parent data bulk load and then use them for foreign key in Child table data load ?

Thanks.

Answer 1

Consider persisting the data as Hibernate entities for this case. If you still want to use JdbcTemplate, here are some suggestions. This is a good example that i found for this - http://www.javaworld.com/article/2458888/spring-framework/open-source-java-projects-spring-batch.html

1) How to design my Reader bean?

The reader should just read the given given lines into a corresponding single bean (lets call it CustomerOrder). In the reader do not try to map to a parent-child bean structure.

2) How to retrieve primary key which I am currently creating it within the INSERT sql for Customer using sequence.nextval?

Use SimpleJdbcInsert. In the ItemWriter, for each CustomerOrder bean, check if the Customer name is already there (I am assuming each Customer has unique name and address). Check this sample (note I have not executed/compiled it. It is just an illustration)

public class CustomerOrderWriter implements ItemWriter<CustomerOrder>
{
    private static final String GET_CUSTOMER = "select * from CUSTOMER where name = ? and address =?";
    private static final String INSERT_CUSTOMER = "insert into CUSTOMER (name,address) values (?,?)";
    private static final String INSERT_ORDER = "insert into ORDER (customer_id, date, value, tax) values (?,?,?,?)";

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void write(List<? extends CustomerOrder> customerOrders) throws Exception
    {
        //Process Write for each CSV row
        for( CustomerOrder custOrd : customerOrders )
        {
            //Check if a customer exists   
            List<Customer> custList = jdbcTemplate.query(GET_CUSTOMER, new Object[] {custOrd.getName(), custOrd.getAddress() }, new RowMapper<Customer>() {
                @Override
                public Customer mapRow( ResultSet resultSet, int rowNum ) throws SQLException {
                    Customer c = new Customer ();
                    c.setId( resultSet.getInt( 1 ) );
                    return c;
                }
            });

            //If customer already exists, just get the ID and insert the Order
            if( custList.size() > 0 )
            {
               //Since the customer is found, insert the Order table here with the customer ID.
            }
            else
            {
               //Insert the customer, using "SimpleJdbcInsert", get the newly inserted customer Id and then insert the Order table
            }
        }
    }
}

3) How to store all the Primary Keys from Parent data bulk load and then use them for foreign key in Child table data load ?

If you follow design in answer 2, you don't need to do this.