Issue
Environment: Spring application, using JOOQ 3.7, generating the JOOQ mapping code automatically from the schema, using Postgres as my database.
I've been porting some code from Hibernate to JOOQ. The Hibernate code pulls some authentication details stashed away on a per context basis to populate fields like "createdBy", "updatedBy", dates, etc.
The only way I can see to do this with JOOQ at the moment is that developers would have to remember to write code to update the fields by hand every time they update an object and I can see it's going to be tedious and easy to forget to populate the fields.
Using JOOQ, is there some way I can deal with "history fields" on each table in a better way than writing a bunch of code by hand?
We don't use DAOs at the moment, I'd prefer to avoid writing/generating an entire layer of code just to deal with these history fields.
Another option might be to do this in the database, and if JOOQ can't help with the problem, that may well be what we'll do.
Solution
There are several ways to do this with jOOQ:
1. Use jOOQ 3.17's client side computed columns
Starting with jOOQ 3.17, audit columns and other types of client side computed columns are available:
jOOQ 3.17 implements precisely this feature, along with a couple of other nice additions, such as two-dimensional versioning, which may be available in a future version:
- #4704 SQL:2011 temporal validity
2. Use a RecordListener to generate these values on UpdatableRecord
The RecordListener SPI is invoked every time you call any of:
TableRecord.insert()UpdatableRecord.store()UpdatableRecord.update()UpdatableRecord.delete()UpdatableRecord.refresh()
However, this SPI is not invoked when you write explict DML statements. For more information, see the manual, here:
3. Use a VisitListener to transform all the SQL statements generated by jOOQ
The VisitListener SPI is there for arbitrary SQL transformation operations. You can intercept all sorts of SQL statements as generated by jOOQ, and add additional clauses to them, e.g.
UPDATE table SET a = 1, b = 2 WHERE id = 3
Would become
UPDATE table SET a = 1, b = 2, updatedBy = 'me' WHERE id = 3
This SPI is documented here in the manual:
4. Write triggers
The best solution in my opinion is to move this kind of auto-generated data into the database by using triggers. This will allow you to update these values also in the event of a migration, manual update, or access via another language than Java (e.g. a Perl script, etc.)
Answered By - Lukas Eder
Answer Checked By - Robin (JavaFixing Admin)
