How to batch insert using jooq without fetching sequence number

user_13369839 0
Viewed 868

I want to do batch insert to postgres using jooq:

List<MyTableRecord> records = new ArrayList<>();
for (Dto dto : dtos) {
    Field<Long> sequenceId = SEQUENCE.nextval();
    Long id = using(ctx).select(sequenceId).fetchOne(sequenceId);
    records.add(mapToRecord(dto, id));
}
using(ctx).batchInsert(records).execute();

The problem is that I am fetching next sequence number for each row.

For simple insert I can use Field in statement like this:

create.insertInto(ID, VALUE)
  .values(SEQUENCE.nextval(), val("William"))
  .execute();

How can I do so with batch insert?

jooq code-generation database java
1 Answers

Pre-fetch all the sequence values

You could pre-fetch all the sequence values you need using this:

List<Long> ids = using(ctx)
    .select(sequenceId)
    .from(generateSeries(1, dtos.size()))
    .fetch(sequenceId);

for (int i = 0; i < dtos.size(); i++)
    records.add(mapToRecord(dtos.get(i), ids.get(i)));

using(ctx).batchInsert(records).execute();

This seems like a useful feature to have out of the box, in an RDBMS agnostic way via using(ctx).nextvals(SEQUENCE, dtos.size()). We'll consider this for a future jOOQ version: https://github.com/jOOQ/jOOQ/issues/10658

Don't use records

An alternative is to batch actual INSERT statements instead of Record.insert() calls via batchInsert(). That way, you can put the SEQUENCE.nextval() expression in the statement. See: https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution/

user_5217990
Related