how to set cuurent DB time in jooq record object

Question

I have generated jooq records via the code generator. In my record I have a timestamp field which I want to set as the DB current dat time.

The below works:

MyRecord myrecord = new MyRecord();
myrecord.setCreateTime(Timestamp.from(Instant.now())); 

But I want to use the DB time , so something like this:(obviously there is a compile error in the below syntax)

myrecord.set(MY_RECORD.CREATE_TIME, DSL.currentTimestamp());

What is the best way to achieve this?

Answer 1

Using jOOQ's INSERT statement directly, rather than UpdatableRecord

jOOQ's UpdatableRecord API doesn't allow for using SQL expressions as values, only actual values can be passed to the Record.set(Field<T>, T) method, by design. If you wish to introduce SQL expressions, you should revert to using classic SQL, e.g. an INSERT:

DSL.using(configuration)
   .insertInto(MY_TABLE)
   .set(...)
   .set(MY_TABLE.CREATE_TIME, DSL.currentTimestamp())
   .execute();

Preferred approach: Use SQL

Another option would be to resort to using triggers or SQL DEFAULT expressions that you can put on the table definition directly. That way, the current timestamp will be generated regardless if you insert the record through jOOQ or through some other means.

Answer 2

"Another option would be to resort to using triggers or SQL DEFAULT expressions that you can put on the table definition directly"

Agreed, but there should not be a need to resort to triggers depending on your application and/or database platform. Columns can be set as DEFAULT, eg

MY_COLUMN DATE DEFAULT SYSDATE

which still allows someone to override the value with a NULL, or you can do

MY_COLUMN DATE DEFAULT ON NULL SYSDATE

to put the default in place even if a null is explicitly requested.