JOOQ - equivalent of hibernate interceptor for populating history fields?

Question

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.

Answer 1

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:

• #1592 Audit columns
• #9879 Client side computed columns

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:

• http://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/crud-record-listener

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:

• http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-sql-transformation

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.)

Answer 2

Interesting question. I would say that you have two approaches here.

1. Copy Hibernate approach. You can bind a custom action to executeEnd step. This is happening just after entering row. You can access query object. Based on that you can try to figure out how to update those two fields (created/modified) in a separate call. You might want to augment also query before executing to database using executeStart, but that is going to be tricky.

2. Design. If you don't want to create boilerplate code, that's fine but is there a reason why you can't create some object that is going to handle this pre/post processing for you? You can drive your queries by some smart proxy that is going to update records for you. Maybe it's a good idea to create some history table in the database which is going to remember not only the modification time but also what has been done/changed. You're not using hibernate anymore. You're not limited to what it gives to you :).