CODEWITHSUNDEEP
mysqlspringcsvspring-batch
Joey Cote
Joey Cote

Reputation: 362

How to do multiple queries in Spring Batch (specifically use LAST_INSERT_ID())

I am trying to write a Spring Batch Starter job that reads a CSV file and inserts the records into a MySQL DB. When it begins I want to save the start time in a tracking table, and when it ends, the end time in that same table. The table structure is like:

TRACKING : id, start_time, end_time

DATA: id, product, version, server, fk_trk_id

I am unable to find an example project that does such a thing. I believe this needs to be a Spring Batch Starter project that can handle multiple queries. i.e.

// insert start time

1. INSERT INTO tracking (start_time) VALUES (NOW(6));

// get last inserted id for foreign key

2. SET @last_id_in_tracking = LAST_INSERT_ID();

// read from CSV and insert data into 'data' DB table

3. INSERT INTO data (product, version, server, fk_trk_id) VALUES (mysql, 5.1.42, Server1, @last_id_in_tracking);
4. INSERT INTO data (product, version, server, fk_trk_id) VALUES (linux, 7.0, Server2, @last_id_in_tracking);
5. INSERT INTO data (product, version, server, fk_trk_id) VALUES (java, 8.0, Server3, @last_id_in_tracking);

// insert end time

6. UPDATE tracking SET end_time = NOW(6) WHERE fk_trk_id = @last_id_in_table1;

I'd like sample code and explanation on how to use those queries to multiple tables in the same Spring Batch Starter job.

start of edit section - additional question

I do have an additional question. In my entities I have them set-up to represent the relationships with annotations (i.e @ManyToOne, @JoinColumn)...

In your code, how would I get the trackingId from a referenced object? Let me explain:

My Code (Data.java):

@JsonManagedReference
@ManyToOne
@JoinColumn(name = "id")
private Tracking tracking;

Your code (Data.java):

@Column(name = "fk_trk_id")
private Long fkTrkId;

Your code (JobConfig.java):

final Data data = new Data();
data.setFkTrkId(trackingId);

How do I set the id with "setFkTrkId" when the relationship in my Entity is an object?

end of edit section - additional question

Upvotes: 2

Views: 2938

Answers (2)

Nghia Do
Nghia Do

Reputation: 2668

I have created a project for you as an example. Please refer to https://bigzidane.wordpress.com/2018/02/25/spring-batch-mysql-reader-writer-processor-listener/

This example simply has a Reader/Processor/Writer. The reader will read a CSV file and then process something and then write to database.

And we have a listener to capture StartJob and EndJob. For Start Job, we will insert an entry to DB and then return a generatedId. We will pass the same ID to writer when we stored entries.

Note: I'm sorry I'm reused an example I have already. So it may not match 100% as your question but technically it should be the same.

Thanks, Nghia

Upvotes: 2

Joe Chiavaroli
Joe Chiavaroli

Reputation: 316

Here is an example app that does what you're asking. Please see the README for details.

https://github.com/joechev/examples/tree/master/csv-reader-db-writer

Upvotes: 3

Related Questions