How to handle duplicate key with batch insert in Jooq?

Question

I need to insert a large number of rows into MySQL database and if they are duplicate, just update columns.

This is what I have:

dslContext .insertInto( USER, USER.ID, USER.NAME ) .valuesOfRows( profiles .stream() .collect( toRowArray( x -> DSL.val(x.getId()) x -> DSL.val(x.getName()) ) ) ) .onDuplicateKeyUpdate() .set(USER.NAME, ???) .execute();

How should I handle duplicates in this case? The JooQ documentation's example is set(AUTHOR.LAST_NAME, "Koontz") which uses a fixed value to update. But here many rows are being inserted. How can I tell jooq "if row is a duplicate, just update user name?"

Lukas Eder · Accepted Answer · 2022-11-10 16:05:00Z

1

Starting from jOOQ 3.17, you can use DSL.excluded(USER.NAME), which is PostgreSQL's version of the MySQL VALUES (NAME) function or NEW.NAME syntax. So:

.set(USER.NAME, DSL.excluded(USER.NAME))

answered Nov 10, 2022 at 16:05

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mahdi Over a year ago

So, iiuc, if I want to update only the NAME field in case of duplicate IDs in my example above, writing .set(USER.ID, DSL.excluded(USER.ID)) will achieve that right?

Lukas Eder Over a year ago

No, then you would have to write exactly what I wrote in the answer. Here's the PostgreSQL docs for the EXCLUDED pseudo table (look for "excluded"): postgresql.org/docs/current/sql-insert.html. It translates to VALUES() or NEW in MySQL: dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Lukas Eder Over a year ago

For the record, you should read the word EXCLUDED as being the pseudo table of rows that have been excluded from insertion. That's how PostgreSQL defined it. jOOQ used the PostgreSQL naming, because 1) MySQL's naming is in flux, incompatibly, 2) VALUES already has a very different meaning within jOOQ and the SQL standard.

Mahdi Over a year ago

Thanks. Any suggestions about how to do this for a table which has many columns (~20) without writing a large chunk of x -> DSL.val(x.getName() style block of code?

Lukas Eder Over a year ago

@Mahdi: Use the set(Map) overload, and Stream.of(table.fields()).filter(...).collect(toMap(...)))

Collectives™ on Stack Overflow

How to handle duplicate key with batch insert in Jooq?

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related