CODEWITHSUNDEEP
javasqljooq
Jako
Jako

Reputation: 2577

jooq, fill field value on insert, similar to record_timestamp

Each table in my database have these fixed columns:

Thanks to jooq feature record_version and record_timestamp are automatically handled.

Is it possible to centralize and automate the maintenance of create_time ? Internally jooq is already managing the first two special fields.

Can I put a handler somewhere to fill the create_time value? Doing so, I could remove some boilerplate code to initialize this field.

I have another field on each table: update_by_account; anyway If I'm able to manage the previous mentioned field (create_time) I think I'll handle this field too.

thanks in advance

Upvotes: 1

Views: 609

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

Future jOOQ versions:

What you're looking for is the Listener API feature for Record and UpdatableRecord. It has not yet been implemented as of jOOQ 3.0

In the future, this feature will allow to inject some behaviour to jOOQ's records, when they are stored, inserted, updated, deleted, etc. This would include what you are requesting as well as what jOOQ is currently doing with its

  • record_version
  • record_timestamp

columns used for optimistic locking.

A solution for jOOQ 2.x or 3.0:

What you can do right now is implement an ExecuteListener and let it "detect" if it should become active and override a bind value for create_time. This will probably have to resort to parsing or at least regex-matching your SQL statement.

A SQL-only solution:

However, the best option for create_time, in my opinion, is to write a trigger for every one of your tables (Oracle syntax):

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT
ON my_table
REFERENCING NEW AS new
FOR EACH ROW
BEGIN
    :new.create_time = SYSDATE;
END t_triggers_trigger;

This will guarantee that the value is available no matter how you access your database...

Upvotes: 2

Related Questions