0

I am writing a spring batch application and have an input csv file that has multiple rows with a unique person name and their phone number. In the csv I am guaranteed that the person to phone number is one to one relationship however I want to insert these in a database where there is a one to many relationship on person to phone.

I know how to insert each individually however when inserting the phone I want to get the id that was autogenerated when inserting the person to create the one to many relationship.

Not sure the best way to do that unless I write a custom processor that sends http request to the backend or directly access the database to create the person and then use the writer to create the phone number entry.

yodigi7
  • 25
  • 1
  • 9
  • Does this answer your question? [MySQL & Java - Get id of the last inserted value (JDBC)](https://stackoverflow.com/questions/4246646/mysql-java-get-id-of-the-last-inserted-value-jdbc) – crizzis May 28 '21 at 18:03
  • Not exactly, that may be how I did it if I used it in the processor but don't think that would be the optimal way. Would think there is a better way to do it. – yodigi7 May 28 '21 at 18:22
  • Define 'better' – crizzis May 28 '21 at 19:16
  • Less code and writing a bunch of boiler plate code to connect to the database. Figured there would be a way to tie it in using spring batch like the basic inserts are but with some more logic. – yodigi7 May 28 '21 at 19:30
  • Here is my basic insert, pretty clean and uses the spring batch java code: https://gist.github.com/yodigi7/5982bc31662e5160389ccb46b39ae558 – yodigi7 May 28 '21 at 19:31
  • 1
    Well, you could simply use the method your RDBMS provides for getting the last insert id and incorporate it into your `INPUT` statement. The problem is, after inserting the first phone number, it will no longer return the person id – crizzis May 28 '21 at 20:05
  • Ah so do you mean something like this? https://stackoverflow.com/questions/9477502/get-the-last-inserted-row-id-with-sql-statement – yodigi7 May 28 '21 at 22:39

1 Answers1

0

Was able to do this by just using a WITH statement around the first insert in the SQL and then use that temporary result to return the id. Then outside do the second insert on the other table using insert into select from. Gave the second insert the id variable and then "consts" for the other column values.

https://gist.github.com/yodigi7/5982bc31662e5160389ccb46b39ae558

yodigi7
  • 25
  • 1
  • 9