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!

Answer 1

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:

• https://github.com/jOOQ/jOOQ/issues/9864
• https://www.jooq.org/doc/dev/manual/sql-building/column-expressions/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.