Set the database current timestamp while inserting using JOOQ-CodePudding

I am using the following code segment to do the insertion using JOOQ's UpdatableRecord.

public void acknowledgeDisclaimer(AcknowledgeDisclaimerReq acknowledgeDisclaimerReq) {
        DisclaimerRecord disclaimerRecord = dslContext.newRecord(Disclaimer.DISCLAIMER);
        disclaimerRecord.setDisclaimerForId(acknowledgeDisclaimerReq.getDealListingId());
        disclaimerRecord.setDisclaimerForType("DEAL");
        disclaimerRecord.setAcceptedAt(LocalDateTime.now());
        disclaimerRecord.setAcceptedByOwnerId(acknowledgeDisclaimerReq.getLoggedInOwnerId());
        int count = disclaimerRecord.store();
        log.info("Inserted entry for disclaimer for deal: {}, owner: {}, id {}, insertCount: {}", disclaimerRecord.getDisclaimerForId(), disclaimerRecord.getAcceptedByOwnerId(), disclaimerRecord.getId(), count);
}

When setting the AcceptedAt data, I want to use the database's current timestamp instead of passing the JVM timestamp. Is there any way to do that in JOOQ?

CodePudding user response：

UpdatableRecord.store() can only set Field<T> => T key/values, not Field<T> => Field<T>, so you cannot set an expression in your record. You can obviously run an explicit INSERT / UPDATE / MERGE statement instead.

Using triggers

The best way to ensure such a timestamp is set to the database timestamp whenever you run some specific DML on the table is to use a database trigger (you could make the trigger watch for changes in the ACCEPTED_BY_OWNER_ID value)

If you can't do this on the server side (which is the most reliable, because it will behave correctly for all database clients, not just the JDBC/jOOQ based ones), you might have a few client side options in jOOQ:

Using jOOQ 3.17 client side computed columns

jOOQ 3.17 has added support for stored (or virtual) client side computed columns, a special case of which are audit columns (which is almost what you're doing).

Using this, you can specify, for example:

<forcedType>
  <generator><![CDATA[
    ctx -> org.jooq.impl.DSL.currentTimestamp()
  ]]></generator>
  <includeExpression>(?i:ACCEPTED_AT)</includeExpression>
</forcedType>

The above acts like a trigger that sets the ACCEPTED_AT date to the current timestamp every time you write to the table. In your case, it'll be more like:

<forcedType>
  <generator><![CDATA[
    ctx -> org.jooq.impl.DSL
      .when(ACCEPTED_BY_OWNER_ID.isNotNull(), org.jooq.impl.DSL.currentTimestamp())
      .else_(ctx.table().ACCEPTED_AT)
  ]]></generator>
  <includeExpression>(?i:ACCEPTED_AT)</includeExpression>
</forcedType>

See a current limitation of the above here:

https://github.com/jOOQ/jOOQ/issues/13809

See the relevant manual sections here:

CodePudding user response：

Should be something like disclaimerRecord.setAcceptedAt(DSL.now());