Implement batchMerge() in JOOQ

I need to “upsert” a List of items.

class Item {
 private UUID id;
 private UUID anotherId;
 private List<String> things;
 ...
}

If an item’s id and anotherId is already existing, other values should be replaced by the new item. If it’s not, item should be inserted as new record.

I came across JOOQ’s batchMerge and I thought it is similar to “upsert”. Unfortunately there are few documentation on the internet since this is a fairly new method by JOOQ as of writing.

I tried

List<ItemRecord> items = ... //built from context.newRecord(ITEM) then added to a list
context.batchMerge(items).execute();

thinking it will automatically get the updated fields. There is no update in the values if row is already existing. It does insert a new record if it’s not yet existing.

I came across this documentation of merge but I do not know how to translate it to batchMerge. https://www.jooq.org/doc/3.1/manual/sql-building/sql-statements/merge-statement/

I am fairly new to SQL and JOOQ. I am using PostgreSQL as database.

Answer

JOOQ’s batchMerge() works as expected with Postgres.

The problem was not actually with JOOQ. A service that calls the repo method for batchMerge is marked @Transactional. It’s a Spring annotation which has a rollback feature. That is why it appears it doesn’t persist to the database. I marked it as @Transactional(noRollbackFor = CustomException.class) to solve my issue.