UPSERT in PostgreSQL using jOOQ

Question

I am trying to perform an UPSERT in PostgreSQL using the jOOQ library.

For doing this I am currently trying to implement the following SQL statement in jOOQ: https://stackoverflow.com/a/6527838

My code looks like this so far:

public class UpsertExecutor {

    private static final Logger logger = LoggerFactory.getLogger(UpsertExecutor.class);

    private final JOOQContextProvider jooqProvider;

    @Inject
    public UpsertExecutor(JOOQContextProvider jooqProvider) {
        Preconditions.checkNotNull(jooqProvider);

        this.jooqProvider = jooqProvider;
    }

    @Transactional
    public <T extends Record> void executeUpsert(Table<T> table, Condition condition, Map<? extends Field<?>, ?> recordValues) {
        /*
         * All of this is for trying to do an UPSERT on PostgreSQL. See:
         * https://stackoverflow.com/a/6527838
         */

        SelectConditionStep<Record1<Integer>> notExistsSelect = jooqProvider.getDSLContext().selectOne().from(table).where(condition);
        SelectConditionStep<Record> insertIntoSelect = jooqProvider.getDSLContext().select(recordValues).whereNotExists(notExistsSelect);

        try {
            int[] result = jooqProvider.getDSLContext().batch(
                jooqProvider.getDSLContext().update(table).set(recordValues).where(condition),
                jooqProvider.getDSLContext().insertInto(table).select(insertIntoSelect)
            ).execute();

            long rowsAffectedTotal = 0;
            for (int rowsAffected : result) {
                rowsAffectedTotal += rowsAffected;
            }

            if (rowsAffectedTotal != 1) {
                throw new RuntimeException("Upsert must only affect 1 row. Affected: " + rowsAffectedTotal + ". Table: " + table + ". Condition: " + condition);
            }
        } catch (DataAccessException e) {
            if (e.getCause() instanceof BatchUpdateException) {
                BatchUpdateException cause = (BatchUpdateException)e.getCause();

                logger.error("Batch update error in upsert.", cause.getNextException());
            }

            throw e;
        }
    }
}

This code does however not compile, since select() doesn't support a map of values:

SelectConditionStep<Record> insertIntoSelect = jooqProvider.getDSLContext().select(recordValues).whereNotExists(notExistsSelect);

The Question

How do I provide select() with a set of predefined values like this: SELECT 3, 'C', 'Z'?

Update 1

I managed to get the code working. Here is the complete class:

public class UpsertExecutor {

    private static final Logger logger = LoggerFactory.getLogger(UpsertExecutor.class);

    private final JOOQContextProvider jooqProvider;

    @Inject
    public UpsertExecutor(JOOQContextProvider jooqProvider) {
        Preconditions.checkNotNull(jooqProvider);

        this.jooqProvider = jooqProvider;
    }

    @Transactional
    public <T extends Record> void executeUpsert(Table<T> table, Condition condition, List<FieldValue<Field<?>, ?>> recordValues) {
        /*
         * All of this is for trying to do an UPSERT on PostgreSQL. See:
         * https://stackoverflow.com/a/6527838
         */

        Map<Field<?>, Object> recordValuesMap = new HashMap<Field<?>, Object>();
        for (FieldValue<Field<?>, ?> entry : recordValues) {
            recordValuesMap.put(entry.getFieldName(), entry.getFieldValue());
        }

        List<Param<?>> params = new LinkedList<Param<?>>();
        for (FieldValue<Field<?>, ?> entry : recordValues) {
            params.add(val(entry.getFieldValue()));
        }

        List<Field<?>> fields = new LinkedList<Field<?>>();
        for (FieldValue<Field<?>, ?> entry : recordValues) {
            fields.add(entry.getFieldName());
        }

        SelectConditionStep<Record1<Integer>> notExistsSelect = jooqProvider.getDSLContext().selectOne().from(table).where(condition);
        SelectConditionStep<Record> insertIntoSelect = jooqProvider.getDSLContext().select(params).whereNotExists(notExistsSelect);

        try {
            int[] result = jooqProvider.getDSLContext().batch(
                jooqProvider.getDSLContext().update(table).set(recordValuesMap).where(condition),
                jooqProvider.getDSLContext().insertInto(table, fields).select(insertIntoSelect)
            ).execute();

            long rowsAffectedTotal = 0;
            for (int rowsAffected : result) {
                rowsAffectedTotal += rowsAffected;
            }

            if (rowsAffectedTotal != 1) {
                throw new RuntimeException("Upsert must only affect 1 row. Affected: " + rowsAffectedTotal + ". Table: " + table + ". Condition: " + condition);
            }
        } catch (DataAccessException e) {
            if (e.getCause() instanceof BatchUpdateException) {
                BatchUpdateException cause = (BatchUpdateException)e.getCause();

                logger.error("Batch update error in upsert.", cause.getNextException());
            }

            throw e;
        }
    }
}

It does however not feel very clean with the List<FieldValue<Field<?>, ?>> recordValues parameter. Any better ideas on how to do this?

It seems the val() static method might be what I'm looking for: jooq.org/javadoc/3.3.x/org/jooq/impl/… — uldall
– uldall, Commented Apr 6, 2014 at 10:05
You know you'll have to use SERIALIZABLE transactions in a retry loop, or lock the table, for that to work reliably, right? — Craig Ringer
– Craig Ringer, Commented Apr 6, 2014 at 10:40
@CraigRinger Yes, I am aware of that. Can you recommend a better approach? I am very interested in better solutions to this problem. — uldall
– uldall, Commented Apr 6, 2014 at 10:51
sql select 3,'C','Z'; is much the same as sql values (3,'C','Z'); , so yes val() sounds like the right method. — Jasen
– Jasen, Commented Feb 12, 2015 at 3:34
FYI PosgreSQL 9.5 will have UPSERT. wiki.postgresql.org/wiki/UPSERT — Roger
– Roger, Commented May 15, 2015 at 10:46

Lukas Eder · Accepted Answer · 2016-01-29 11:54:54Z

27

jOOQ 3.7+ supports PostgreSQL 9.5's ON CONFLICT clause:

The full PostgreSQL vendor-specific syntax is not yet supported, but you can use the MySQL or H2 syntax, which can both be emulated using PostgreSQL's ON CONFLICT:

MySQL `INSERT .. ON DUPLICATE KEY UPDATE`:

DSL.using(configuration)
   .insertInto(TABLE)
   .columns(ID, A, B)
   .values(1, "a", "b")
   .onDuplicateKeyUpdate()
   .set(A, "a")
   .set(B, "b")
   .execute();

H2 `MERGE INTO ..`

DSL.using(configuration)
   .mergeInto(TABLE, A, B, C)
   .values(1, "a", "b")
   .execute();

answered Jan 29, 2016 at 11:54

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

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

2 Comments

ud3sh Over a year ago

Utility derived from Lucas' solution for TableRecord objects:

public static int upsert(final DSLContext dslContext, final UpdatableRecord record) {         return dslContext.insertInto(record.getTable()).set(record).onDuplicateKeyUpdate().set(record).execute();     }

Lukas Eder Over a year ago

@ud3sh: Hey, thanks for mentioning. Feel free to provide this as a full answer. I'm sure it might be useful for others!

Johannes Barop · Accepted Answer · 2019-10-20 18:44:51Z

27

Here is an upsert utility method derived from Lucas' solution above for UpdatableRecord objects:

public static int upsert(final DSLContext dslContext, final UpdatableRecord record) {
    return dslContext.insertInto(record.getTable())
                     .set(record)
                     .onDuplicateKeyUpdate()
                     .set(record)
                     .execute();
}

edited Oct 20, 2019 at 18:44

Johannes Barop

7,9352 gold badges27 silver badges34 bronze badges

answered Sep 23, 2016 at 17:14

ud3sh

1,30912 silver badges13 bronze badges

2 Comments

pstobiecki Over a year ago

Doesn't work with PostrgreSQL (or actually H2 in PostgreSQL mode)

Rüdiger Schulz Jan 28 at 21:42

Works like a charm with PostgreSQL 17. @pstobiecki maybe you could try using testcontainers instead of H2, if you have that problem only with your tests.

frno · Accepted Answer · 2019-07-01 16:02:36Z

6

Inspired by @ud3sh comment with JOOQ 3.11, Kotlin, and the PostgreSQL DSL

This is an extension function to call upsert directly on the UpdatableRecord object

import org.jooq.UpdatableRecord

internal fun UpdatableRecord<*>.upsert(): Int {
    if(this.configuration() == null) {
        throw NullPointerException("Attach configuration to record before calling upsert")
    }
    return this.configuration().dsl().insertInto(this.getTable()).set(this).onConflict().doUpdate().set(this).execute()
}

edited Jul 1, 2019 at 16:02

answered Jun 5, 2019 at 15:40

frno

2,9012 gold badges20 silver badges19 bronze badges

1 Comment

Rüdiger Schulz Jan 22 at 15:36

onConflict() is the way to go, see jooq.org/doc/latest/manual/sql-building/sql-statements/…

e4c5 · Accepted Answer · 2015-08-13 03:19:27Z

4

Seems a bit of a complicated way of achieving the objective. Why not use a simple stored fucntion? how to create an upsert function is described in the postgresql manual then just call it from your java code.

answered Aug 13, 2015 at 3:19

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

1 Comment

alexius Over a year ago

+1. The only correct way to implement upsert now (until 9.5 released) is to use stored function with cycle as written in given link. More information is here depesz.com/2012/06/10/why-is-upsert-so-complicated

Collectives™ on Stack Overflow

UPSERT in PostgreSQL using jOOQ

The Question

Update 1

4 Answers 4

MySQL `INSERT .. ON DUPLICATE KEY UPDATE`:

H2 `MERGE INTO ..`

2 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The Question

Update 1

4 Answers 4

MySQL INSERT .. ON DUPLICATE KEY UPDATE:

H2 MERGE INTO ..

2 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

MySQL `INSERT .. ON DUPLICATE KEY UPDATE`:

H2 `MERGE INTO ..`