• Jobs
  • About Us
  • professionals
    • Home
    • Jobs
    • Courses and challenges
  • business
    • Home
    • Post vacancy
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

508
Views
Spring batch - write to two tables by extracting auto generated key

When using spring batch, I have a requirement to write the data to two different tables. It appears like I can use a CompositeItemWriter with as many ItemWriters as possible.

However, I need to insert a record in the first table and retrieve the auto generated primary key and use it when inserting in the second table. Any idea how we can retrieve the auto generated key in this scenario?

about 3 years ago · Santiago Trujillo
3 answers
Answer question

0

Wis' solution does work, but from a performance point of you (which is normally one of the main concerncs when talking about batch processing) it is not recommended.

Question: how would you select the inserted rows in order to read the generated keys? do you have another unique field, or at least a combination of it which lets you identify the written items at all? can you read at least all items of the 'items'-list as one single call to the db or do you have to read every single entry with its own select?

At least, you will have an additional read-call to the db. but it could also be an additional call per item.

The far better solution would be to be able to create the key inside your batch.

Performance may not be a problem if we just talk about a couple of thousand inserts. However, if we talk about millions, it will matter.

Anyway, if I really had to re-read from the db, I would use the following two approaches.

First, If I do not have to write both tables in the same transaction

Step 1:

  • read from the source
  • prepare data
  • write to table 1, which creates the data

Step 2:

  • read from table 1 with the generated id
  • do some processing if necessary
  • write to table 2

Second, if I have to write both tables in the same transaction

public class MyWriter implements ItemWriter<MyObject> {
    private JdbcItemWriter<MyObject> writer1;
    private JdbcItemWriter<MyObject> writer2;

    @PostConstruct
    public void afterPropertiesSet() {
         writer1 = new JdbcItemWriter<MyObject>();
         writer1.set...
         writer1.afterPropertiesSet();

         writer2 = ... same thing

    }

    public void write(List<MyObject> items) {
         writer1.write(items);

         List<MyObject> reReadItems = 
                new JdbcTemplate(datasource)
                   // you could use a row that which has a unique chunk id
                   // or you could construct a query with an appropriate
                   // in-Clause... however, the size of possible in-clauses
                   // is limited, for instance oracle has 1000
                   .query(a query that selects only the entries you inserted above,
                (resultset, row) -> // RowMapper
                { 
                    MyObject obj = new MyObject();
                    obj.setXy(resultset.get...);
                    ...
                    return obj;
                 });
          writer2.write(reReadItems);
    }
}

This would at least limit your calls to the db to one addition call per chunk.

about 3 years ago · Santiago Trujillo Report

0

The writers are always executed in-order. Consequently, you can write to the first table AND read the generated key in the first writer and write to the second table in the second writer. If you updated the object in the first writer, you'll get the update in the second writer.

First writer may looks like :

public class MyWriter implements ItemWriter<MyObject> {
    @Override
    public void write(List<? extends MyObject> items) throws Exception {
        // assuming there is a DB connection
        for(MyObject item: items) {
            // create and execute query to insert data in table 1
            // get generated key and assign it into object
            int key = rs.getInt(...);
            item.setGeneratedKey(key);
        }
    }
}

Second writer may use the generatedKey field to construct the query for table 2. As you injected the value in first writer, it will be available for the second writer.

about 3 years ago · Santiago Trujillo Report

0

I am newby for Spring batch boot. But got lots of mainframe batch experience. I would do it this way assuming you have input file: 1- make job that inserts only your entity A with generated keys 2- make job (or steps) that expands your input with generated key by reading db. Output = input + generated key 3- make job that inderts your entity B So do not try to do it in one step.....

about 3 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recommend me some offers
I have an error