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
auditFields.clear()
necessaryThis 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.
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.
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.
Comments