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?
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:
Step 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.
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.
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.....