jOOQ - General way to insert multiple data and get generated IDs

Question

what is the general way to insert multiple data via jooq, when I need the generated key of each element?

Normally I would use a batch insert, which is not possible at the moment because of this.

I could use create.newRecord(...) and insert each element separately. Afterwards, the ID is set correctly, but this approach has a bad performance.

I hope someone has a better approach, I cannot be the only one who need this feature...

Thanks a lot in advance,
tohoe

Answer 1

As you've found yourself, jOOQ 3.4.2 currently has the limitation documented in issue #3327 with respect to fetching IDs after DSLContext.batchStore().

A workaround that might work well enough would be to create a big INSERT .. RETURNING statement with all your records, such as:

DSL.using(configuration)
   .insertInto(TABLE)
   .set(record1)
   .newRecord()
   .set(record2)
   .newRecord()
   ...
   .returning()
   .fetch();

This is just a workaround, of course, and might not even perform as well as batching, as the statement could possibly turn out to be quite large.