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?

Answer 1

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

• https://github.com/jOOQ/jOOQ/issues/4299
• http://www.postgresql.org/docs/9.5/static/sql-insert.html

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(); 

Answer 2

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(); }