JOOQ, ignore columns like created_date, last_modified_date in INSERT and UPDATE

user_386350 0
Viewed 134

using mysql, can create a column like:

last_modified_date timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP

So when inserting and updating, we don't need to update this column.

Thought to use readonly column in Jooq:

          .withSyntheticObjects(new SyntheticObjectsType().withReadonlyColumns(
            new SyntheticReadonlyColumnType().withTables("*.*").withFields("CREATED_DATE").withFields("LAST_MODIFIED_DATE")
          ))

The generated class has:

public final TableField<BookRecord, LocalDateTime> CREATED_DATE = createField(DSL.name("created_date"), SQLDataType.LOCALDATETIME(0).nullable(false), this, "");

When trying to use

    BooksRecord rec = new BooksRecord();
    rec.from(myPojo);               
    dslContext.batchUpdate([rec]).execute();

it will complain created_date is null if we don't set the created_date. same for the modified date.

What's the correct way of using this SyntheticReadonlyColumnType? how do I check if it is working or not? Is there a way to globally ignore those columns in update and insert sql generation?

Update: ok, seemed that the SyntheticReadonlyColumnType isn't supported in open source edition: https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/readonly-columns

So the question will be what's the best way to achieve this in the open source edition? call Record.reset(Field) everytime before doing update?

jooq mysql
1 Answers

So the question will be what's the best way to achieve this in the open source edition?

The best approach is to use the built-in readonly column support of the commercial edition, which handles a lot of caveats and edge cases, including statements that your write manually, not just the CRUD UpdatableRecord.store() calls. It also does this automatically for every new computed column that you add to your schema.

call Record.reset(Field) everytime before doing update?

That's obviously an option, but you'll have to remember it every time you intereact with this record type.

If upgrading isn't an option (yet), and your readonly column use-cases are limited to UpdatableRecord calls only, then you could implement something similar using a RecordListener, but that won't work for example if you write:

dsl.update(BOOKS).set(rec).where(BOOKS.ID.eq(rec.getId())).execute();

The out-of-the-box readonly column support can patch that SQL query as well for you, transparently.

user_5217990
Related