The jOOQ User Manual SQL building The DSLContext API Custom Settings Storing unchanged records

previous : next

Storing unchanged records

Supported by ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

When storing records, jOOQ only includes values in an INSERT or UPDATE statement that have been changed by client code. This raises the question of what should happen if no value has been changed at all:

BookRecord book = create.fetchOne(BOOK, BOOK.ID.eq(1));

// No value was changed. Should this execute an UPDATE statement?
book.update();

BookRecord newBook = create.newRecord(BOOK);

// No value was set. Should this execute an INSERT statement?
newBook.insert();

The insertUnchangedRecords setting governs the behaviour of TableRecord.insert(), as well as the INSERT part of UpdatableRecord.store() and UpdatableRecord.merge():

• true (the default): An INSERT INTO .. DEFAULT VALUES statement is executed, producing a row with only default values.
• false: No statement is executed, and 0 is returned.

The updateUnchangedRecords setting governs the behaviour of UpdatableRecord.update(), as well as the UPDATE part of store() and merge(). Executing an UPDATE for an unchanged record can be useful, for example, to fire triggers, to increment record version or timestamp columns, or to check that the row still exists. The possible values of org.jooq.conf.UpdateUnchangedRecords are:

• NEVER (the default): No statement is executed, and 0 is returned.
• SET_PRIMARY_KEY_TO_ITSELF: The primary key columns are set to themselves, e.g. UPDATE book SET id = id WHERE id = ?
• SET_NON_PRIMARY_KEY_TO_THEMSELVES: All other columns are set to themselves, e.g. UPDATE book SET title = title, author_id = author_id WHERE id = ?
• SET_NON_PRIMARY_KEY_TO_RECORD_VALUES: All other columns are set to the record's values, e.g. UPDATE book SET title = ?, author_id = ? WHERE id = ?. This is the same as calling record.changed(true) prior to updating.

Example configuration

Settings settings = new Settings()
    .withInsertUnchangedRecords(false) // Defaults to true
    .withUpdateUnchangedRecords(UpdateUnchangedRecords.SET_PRIMARY_KEY_TO_ITSELF); // Defaults to NEVER

The jOOQ User Manual SQL building The DSLContext API Custom Settings Storing unchanged records

previous : next