How to properly add values to StoreQuery for every row in VisitListener in jOOQ?

Elyorbek Ibrokhimov

I am trying to set audit fields for each insert or update queries only if a table has audit fields. After lots of trials and errors I came up with this logic using VisitListener:


public class AuditVisitListener implements VisitListener {

    private static final Field<Timestamp> createdAt = field("created_at", Timestamp.class);
    private static final Field<String> createdBy = field("created_by", String.class);
    private static final Field<Timestamp> updatedAt = field("updated_at", Timestamp.class);
    private static final Field<String> updatedBy = field("updated_by", String.class);
    private Set<Field<?>> auditFields = new HashSet<>();

    private UserContextHolder userContextHolder;

    public AuditVisitListener(UserContextHolder userContextHolder) {
        this.userContextHolder = userContextHolder;
    }

    @Override
    public void visitStart(VisitContext context) {
        QueryPart topLevelQuery = context.context().topLevel();
        if(topLevelQuery instanceof SelectQuery<?>) {
            // No need to process further since we don't set audit fields for select queries
            return;
        }
        collectAuditFields(context.queryParts());
        if(topLevelQuery instanceof InsertQuery<?> insertQuery) {
            addAuditFieldsForInsert(insertQuery);
        }
        if(topLevelQuery instanceof UpdateQuery<?> updateQuery) {
            addAuditFieldsForUpdate(updateQuery);
        }
    }

    @Override
    public void visitEnd(VisitContext context) {
        // Without this line, audit fields are added
        // even if there is no audit fields in a table. 
        auditFields.clear();
    }

    private void collectAuditFields(QueryPart[] queryParts) {
        for(QueryPart queryPart : queryParts) {
           if(queryPart instanceof Table<?> table) {
               if(table.field(createdAt) != null){
                   auditFields.add(createdAt);
               }
               if(table.field(createdBy) != null) {
                  auditFields.add(createdBy);
               }
               if(table.field(updatedAt) != null) {
                  auditFields.add(updatedAt);
               }
               if(table.field(updatedBy) != null) {
                   auditFields.add(updatedBy);
               }
           }
        }
    }

    private void addAuditFieldsForInsert(StoreQuery<?> storeQuery) {
        if(auditFields.contains(createdAt)) {
            storeQuery.addValue(createdAt, now());
        }
        if(auditFields.contains(createdBy)) {
            storeQuery.addValue(createdBy, userId());
        }
        addAuditFieldsForUpdate(storeQuery);
    }

    private void addAuditFieldsForUpdate(StoreQuery<?> storeQuery) {
        if(auditFields.contains(updatedAt)) {
            storeQuery.addValue(updatedAt, now());
        }
        if(auditFields.contains(updatedBy)) {
            storeQuery.addValue(updatedBy, userId());
        }
    }

    private String userId() {
        return userContextHolder.getUserId()
                .orElseThrow(() -> new BusinessException(ErrorCode.EMPTY_USER_CONTEXT));
    }

}

First problem is without auditFields.clear(), audit fields are added even if there is no audit fields in a table. Why does it happen? Second problem is it works for single row inserts but when insert statement has multiple rows it sets audit fields only to the last row. For example this insert statement:

    ctx.insertInto(BRAND, BRAND.NAME_EN, BRAND.NAME_KR, BRAND.STATUS)
            .values("Heinz", "하인즈", (short)1)
            .values("Hunts", "헌츠", (short)1)
            .execute();

produces this query:

insert into `pms`.`brand` (`name_en`, `name_kr`, `status`, created_at, created_by, updated_at, updated_by) 
values ('Heinz', '하인즈', 1, null, null, null, null),
       ('Hunts', '헌츠', 1, current_timestamp(), '[email protected]', current_timestamp(), '[email protected]')

However, if I insert multiple rows a batch insert it again works:

    BrandRecord heinz = ctx.newRecord(BRAND);
    heinz.setNameEn("Heinz");
    heinz.setNameKr("하인즈");
    heinz.setStatus((short)1);

    BrandRecord hunts = ctx.newRecord(BRAND);
    hunts.setNameEn("Hunts");
    hunts.setNameKr("헌츠");
    hunts.setStatus((short)1);

    ctx.batchInsert(heinz, hunts).execute();

How to set audit fields for multirow insert or update statements?

For reference, AuditVisitListener is configured using VisitListenerProvider:

@Configuration
public class JooqConfig {

    @Bean
    public DefaultConfigurationCustomizer customizer(UserContextHolder userContextHolder) {
        return configuration -> {
            configuration.setVisitListenerProvider(() -> new AuditVisitListener(userContextHolder));
        };
    }

}

jOOQ: 3.18
Java: 17
Spring Boot: 3.0.5

Lukas Eder

Why is auditFields.clear() necessary

This points to a bigger problem in your design. The lifecycle of your VisitListener probably isn't per query rendering, but per Configuration, meaning your various queries share the same instance. This means that clear() seems to "fix" the issue, but in fact hides a much bigger concurrency problem.

Either, you should use a VisitListenerProvider that instantiates new VisitListener instances every time, or put your context in the VisitContext, instead, whose lifecycle is that of a single rendering traversal.

Why does it only work on the last row

The (very historic) InsertQuery and UpdateQuery APIs only allow for appending data to the last row. To add a new row, InsertQuery.newRecord() is called. From then on, there's no public API in InsertQuery to append values to previous rows.

This isn't the case for batch queries, which are multiple queries, not multiple rows per query.

The newer (as of jOOQ 3.18 still experimental) model API will allow you to access all the rows in the VALUES clause. It will also allow for writing much simpler SQL transformation logic that doesn't need the VisitListener SPI.

A note on alternatives

Please note that jOOQ has:

It will be much easier to just work with that, or, alternatively, use a trigger to fill those column values.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

R: How do I add a column with values to a table, for every row in said table?

How to replace values after every nth row

How to add a row to every group with pandas groupby?

How to add every couple row of matrix?

how to add salary in every row of a table

How to Add a Button/Hyperlink in every row in HTML

how to add extra information in every row of autocomplete

How to add sum value to every row?

Add column to table with set values for every row expanding the table

How convert MySQL syntax WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) to jOOQ DSL?

How to handle a connection properly with jooq?

How to add row with default values if not in consecutive order

How to add time values to get the total for a row?

How to add a row to each group and assign values

How to enable a row with values on a click of a(add) button

How to add a list of values ​in dataframe as a new row

mysql how to update a column of every row with a given set of values

How To Transpose Every 3 values From One row To Multiple columns?

Python - How to Loop over every row in a dataframe to change the values in a column?

How to fill in every nth row with values from another column with dplyr?

How to get the row values for every 30th minute?

How does deduplicate values in every row of a matrix in pytorch?

How to create a percentage column based on the values present in every third row?

How to change all values to the left of a particular cell in every row

How to display 4 values in every row in html using ngfor and ngif?

How to add strings and special characters to every row in dataframe in new column?

How to add plus(+) sign in every row of a column using SQL query?

Python | How to add a string to every row in a csv file?

How to add a string to every even row in a pandas dataframe column series?