Inserting multiple rows in the JOOQ + returning the ids

Question

I am working on a Spring Boot application where I am using JOOQ version 3.15.5 to communicate with the DB. Now I am trying to insert multiple rows in DB and to get the IDs as the result set. How can I do this? I tried to use valuesOfRecords to insert a collection of records but I couldn't make it work, because it forces me to put all the fields of MY_TABLE, including the unknown ID. I tried:

context .insertInto(MY_TABLE, MY_TABLE.ID, MY_TABLE.STATUS, MY_TABLE.NAME) .valuesOfRecords(records) .returningResult(MY_TABLE.ID) .fetchInto(Long.class);

Thanks!

If you're using updatable records, you should perhaps be able to just batch-store the records, then get the generated IDs? First this: jooq.org/doc/3.15/manual-single-page/#batch-execution-for-crud, then just call getId() on the stored records, as seen here: jooq.org/doc/3.15/manual-single-page/#simple-crud — Petr Janeček
– Petr Janeček, Commented Jan 5, 2022 at 12:30
Also, this issue specifically talks about your approach: github.com/jOOQ/jOOQ/issues/12270 and how to make it work. — Petr Janeček
– Petr Janeček, Commented Jan 5, 2022 at 12:32

Lukas Eder · Accepted Answer · 2022-01-05 12:32:38Z

Solving the problem at hand

You don't have to include the ID column. Why not just write this instead?

context // No ID column here .insertInto(MY_TABLE, MY_TABLE.STATUS, MY_TABLE.NAME) // Use a Record2<?, ?> type here .valuesOfRecords(records) .returningResult(MY_TABLE.ID) .fetchInto(Long.class);

If your records are the generated MyTableRecord which you configured to extend Record3<?, ?, ?>, you'll just have to map the desired content to a Record2<?, ?>, or even to a Row2<?, ?>:

context .insertInto(MY_TABLE, MY_TABLE.STATUS, MY_TABLE.NAME) .valuesOfRows(records .stream() // An example mapping. .map(r -> row(r.getStatus(), r.getName())) .toList() ) .returningResult(MY_TABLE.ID) .fetchInto(Long.class);

The jOOQ 3.15 org.jooq.Rows utility has a few mapping functions that help with such cases. You could even write:

context .insertInto(MY_TABLE, MY_TABLE.STATUS, MY_TABLE.NAME) .valuesOfRows(records .stream() .collect(Rows.toRowList(r -> r.getStatus(), r -> r.getName())) ) .returningResult(MY_TABLE.ID) .fetchInto(Long.class);

Using 3.16 readonly columns

Starting from jOOQ 3.16, there is support for readonly columns:

If jOOQ knows your ID column is readonly (and it is, if it's an identity column), then it will ignore it from such statement if you configure it accordingly.

Collectives™ on Stack Overflow

Inserting multiple rows in the JOOQ + returning the ids

1 Answer 1

Solving the problem at hand

Using 3.16 readonly columns

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Solving the problem at hand

Using 3.16 readonly columns

Comments

Related