rewrite an insert query at runtime to populate a field with dynamic value

[Gang Luo]

I have a table Foo defined as

create table FOO {

  name varchar(123),

  from varchar(123) default null

}

I am using JOOQ 3.16 and already have following code `db.insertInto(Foo).set(Foo.name, "xxx").execute()` everywhere. Now I want to find a way to re-write that query to effectively be `db.insertInto(Foo).set(Foo.name).set(Foo.from, "XYZ")` where "XYZ" is a dynamically computed value. But I don't want to make that actual change into 100s of places. Instead I want to do it once and centrally in JOOQ level to re-write the insert query dynamically. 

Basically below is what I want to achieve

- Intercept the query before it get executed

- if the query is an insert query, for FOO table, set column "FOO.From" to be something meaningful

- execute that re-written query to insert the entry with "from" column properly populated. 

I am assessing the option to have a new "ExecuteListener" where the "ExecuteContext" is given. I can get a "Query" (or batched queries) from it, inspect if it is insert statement, and call `addValue`  to add the right value for "from". The only blocker is, there doesn't seem to be a good way for me to tell what table it is, as I only want to add value for table "FOO". "Query" does has "table" property which is not exposed publicly unfortunately. 

Would appreciate some help about the best way to achieve my goal. Thanks. 

[Lukas Eder]

Hello,

Assuming you cannot use triggers for some reason, one simple way to do this is to:

- Intercept all INSERT queries with an ExecuteListener

- Use the experimental query object model (QOM) API to set QOM.Insert::$columns and QOM.Insert::$values, if QOM.Insert::$into is the right target table

- Set the modified query back on the ExecuteContext

For the second part, you'll have to upgrade jOOQ, as QOM.Insert has been added in jOOQ 3.18 only.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/66986af7-18cc-4cfc-a3db-f4b90d12e0dfn%40googlegroups.com.

[Dominik Hirt]

If you're using jooq records, you could use a RecordListener like I do for a similar case.

My from field is modified_by and I populate it automatically:

/**
This RecordListener serves as an interceptor for the record.store() calls and inserts a ‘modified_by’ field if one exists. 

As it is not possible to search for the field, we search for the corresponding getter method ‘getModifiedBy’. 

As the search for a corresponding method is expensive, we remember the results in a cache.
 */

public class JooqRecordListenerSetModifiedBy extends DefaultRecordListener {

@Override
public void insertStart(RecordContext ctx) {
   setModifiedBy(ctx);
}

@Override
public void storeStart(RecordContext ctx) {
   setModifiedBy(ctx);
}

private void setModifiedBy(RecordContext ctx) {
   Record dbRecord = ctx.record();
   Class<?> clazz = dbRecord.getClass();
   Boolean hasModifiedBy = cache.get(clazz);

   if (hasModifiedBy == null) {
     hasModifiedBy = hasModifiedBy(dbRecord);
     cache.put(clazz, hasModifiedBy);
   }

   if (hasModifiedBy.equals(Boolean.TRUE)) {
     dbRecord.setValue(DSL.field(DSL.name("modified_by")), UsernameResolver.resolveUsername());
   }
}

private Boolean hasModifiedBy(Record dbRecord) {
   Boolean result = Boolean.FALSE;

   Method[] methods = dbRecord.getClass().getDeclaredMethods();
   for (Method method : methods) {
     if (method.getName().equals("getModifiedBy")) {
       result = Boolean.TRUE;
       break;
     }
   }
   return result;
}

}

The other audit field is modified_at and it's automatically set by JOOQ due to the maven plugin configuration:

<generator>
  <database>
    <recordTimestampFields>modified_at</recordTimestampFields>

        ....

Hope it helps

Dominik

[Lukas Eder]

On Sat, Jun 1, 2024 at 8:55 PM Dominik Hirt <dominik...@gmail.com> wrote:

private Boolean hasModifiedBy(Record dbRecord) {

   Boolean result = Boolean.FALSE;

   Method[] methods = dbRecord.getClass().getDeclaredMethods();
   for (Method method : methods) {
     if (method.getName().equals("getModifiedBy")) {
       result = Boolean.TRUE;
       break;
     }
   }
   return result;
}

}

You could let your generated records extend an interface declaring the relevant methods via a generator strategy:

- Programmatic: https://www.jooq.org/doc/latest/manual/code-generation/codegen-generatorstrategy/

- Configurative: https://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/

That would allow for avoiding reflection.

Though note, that jOOQ supports auditing out of the box, and will ensure it applies also to non-UpdatableRecord interactions, including all INSERT, UPDATE, and MERGE statements that run by jOOQ's API:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-audit/

[Alf Lervåg]

Just curious, is it possible to check if the field is available from the RecordContext by using ctx.recordType().field("modified_by")?

Alf Lervåg

1. juni 2024 kl. 20:55 skrev Dominik Hirt <dominik...@gmail.com>:



--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/efd98b9e-c9b4-42c4-a013-9defe67256e9n%40googlegroups.com.

[Dominik Hirt]

Hi Alf,

thanks for your message.

I didn't realise that there was also this option. thanks for the hint, it makes implementation much easier and now works without reflection:

private void setModifiedBy(RecordContext ctx) {
   Field<String> modifiedByField = ctx.recordType().field("modified_by", String.class);
   if (modifiedByField == null) {
     log.warn("missing field 'modified_by'");
   } else if (StringUtils.isNotBlank(modifiedByField.get(ctx.record()))) {
     // field already set
   } else {
     ctx.record().set(modifiedByField, UsernameResolver.resolveUsername());
   }
}

And regarding the hint from Lukas and the audit capabilities of JOOQ:

i use the variant with the trigger that Vlad suggested in https://vladmihalcea.com/postgresql-audit-logging-triggers/

it has some advantages
1) only one stored prcedure
2) simple extension for new tables by defining another trigger that uses this SP
3) possibility to display detailed changelogs and modified_fields

But for the optimistick locking, I rely on JOOQ :-)

Kind regards

Dominik