Java r2dbc client execute sql and use returned id for next execute

1.8k views Asked by At

I use r2dbc client with postgresql and I would like to call in one transaction 2 inserts(use id of first query) like:

      client.execute("INSERT INTO place(id,city) VALUES(:id, :city)")
    .bind("id", "nextval('place_id_seq')")
    .bind("city", place.getCity())
    .fetch().rowsUpdated())
    )
    .then(client.execute("INSERT INTO place_category VALUES (:place_id, :category_id);")
      .bind("place_id", <PREVIOUS_ID OF INSERT>)
      .bind("category_id", place.getCategoryId())
      .fetch().rowsUpdated())
    .then();

I don't know how to get @Id of first insert <PREVIOUS_ID OF INSERT>

1

There are 1 answers

0
Hantsy On BEST ANSWER

In the first statement, add an extra filter to return the id fields.

 .filter((statement, executeFunction) -> statement.returnGeneratedValues("id").execute())

Check my example of Save and Query.

And there is a JUnit test case for demo connectting them together.

BTW: I was using the DatabaseClient in Spring 5.3 for these samples.