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?
Advertisement
Answer
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:
See the relevant manual sections here: