How to share the set statements between insert() and onConflict()?

As you can see in the example below, a lot of my set statements are duplicated between the insert and update clauses. Is there some way I can factor out the duplicated set statements to a shared block of code?

I'm thinking something like how it's pretty simple to factor out parts of a where clause by using DSL.condition()?

db.insertInto(RAID_V2).
  set(RAID_V2.HANDLE, handle).
  set(RAID_V2.SERVICE_POINT_ID, servicePointId).
  set(RAID_V2.URL, raidUrl).
  set(RAID_V2.URL_INDEX, urlContentIndex).
  set(RAID_V2.PRIMARY_TITLE, primaryTitle).
  set(RAID_V2.METADATA, jsonbMetadata).
  set(RAID_V2.METADATA_SCHEMA, mapJs2Jq(metadata.getMetadataSchema())).
  set(RAID_V2.START_DATE, startDate).
  set(RAID_V2.DATE_CREATED, LocalDateTime.now()).
  set(RAID_V2.CONFIDENTIAL, confidential).
  onConflict(RAID_V2.HANDLE).doUpdate().
    set(RAID_V2.SERVICE_POINT_ID, servicePointId).
    set(RAID_V2.URL, raidUrl).
    set(RAID_V2.URL_INDEX, urlContentIndex).
    set(RAID_V2.PRIMARY_TITLE, primaryTitle).
    set(RAID_V2.METADATA, jsonbMetadata).
    set(RAID_V2.METADATA_SCHEMA, mapJs2Jq(metadata.getMetadataSchema())).
    set(RAID_V2.START_DATE, startDate).
    set(RAID_V2.DATE_CREATED, LocalDateTime.now()).
    set(RAID_V2.CONFIDENTIAL, confidential).
    where(RAID_V2.HANDLE.eq(handle)).
  execute();

Solution

You're looking for the PostgreSQL specific EXCLUDED clause, which has been introduced in jOOQ 3.17:

#5214: Native support for the clause
#13571: Emulation of the clause in MERGE
#13575: Emulation of the clause inside of expressions (3.18)

For example:

.onConflict(RAID_V2.HANDLE).doUpdate()
.set(RAID_V2.SERVICE_POINT_ID, excluded(RAID_V2.SERVICE_POINT_ID))
.set(RAID_V2.URL, excluded(RAID_V2.URL))
...

Or, if you're not too keen on repeating all the columns, just do it automatically, like this:

.onConflict(RAID_V2.HANDLE).doUpdate()
.set(Arrays.stream(RAID_V2.fields())
           .filter(f -> !isKey(f)) // Exclude key columns, and others...
           .collect(Collectors.toMap(f -> f, f -> excluded(f))))

Note, while it probably doesn't apply to your specific case, in simpler cases, you could use UpdatableRecord::merge to simplify the upsert operation.