插入、更新操作时，通过JOOQ设置字段的默认值

背景：jooq3.12、JDK13、IDEA2019.2.4、postgres12

参考资料：https://github.com/jOOQ/jOOQ/issues/1592

方法1：通过DefaultRecordListener实现

package com.zsx.listener;

import org.jooq.*;
import org.jooq.impl.DefaultExecuteListener;
import org.jooq.tools.StopWatch;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.util.Arrays;
import java.util.Optional;

@Component("customizeExecuteListener")
public class CustomizeExecuteListener extends DefaultExecuteListener implements ExecuteListenerProvider {

    private static final Logger LOGGER = LoggerFactory.getLogger(CustomizeExecuteListener.class);

    private StopWatch watch;

    @Override
    public ExecuteListener provide() {
        return this;
    }

    @Override
    public void executeStart(ExecuteContext ctx) {
        super.executeStart(ctx);
        watch = new StopWatch();
    }

    @Override
    public void executeEnd(ExecuteContext ctx) {
        try {
            super.executeEnd(ctx);
            //记录执行时间大于1ms(1s:1_000_000_000L)的操作
            if (watch.split() <= 1_000_000L) {
                return;
            }
            ExecuteType type = ctx.type();
            StringBuffer sqlBuffer = new StringBuffer();
            if (type == ExecuteType.BATCH) {
                Arrays.stream(ctx.batchQueries()).forEach(query->sqlBuffer.append(query.toString()));
            } else {
                sqlBuffer.append(Optional.ofNullable(type).map(ExecuteType :: toString).orElse("ExecuteType is null"));
            }
            watch.splitInfo(String.format("The type of database interaction that is being executed with this context : { %s }", sqlBuffer.toString()));
        } catch (Exception e) {
            LOGGER.error(" SlowQueryListener.executeEnd.Exception ", e);
        }
    }

}

方法2：通过DefaultVisitListener实现

package com.zsx.listener;

import org.jooq.*;
import org.jooq.impl.TableImpl;
import org.springframework.stereotype.Component;

import java.sql.Timestamp;
import java.util.*;

import static java.util.Arrays.asList;
import static org.jooq.Clause.*;


//@Component("customizeVisitListener")
public class CustomizeVisitListener implements VisitListener, VisitListenerProvider {

    public static final String CREATED_AT = "created_at";
    private static final String CREATED_BY = "created_by";
    public static final String UPDATED_AT = "updated_at";
    private static final String UPDATED_BY = "updated_by";
    private static final String ADMIN = "admin2";
    private static final String AUDIT_FIELDS = "auditFields";

    @Override
    public VisitListener provide() {
        return this;
    }

    @Override
    public void clauseStart(VisitContext context) {
        if (context.clause() == INSERT || context.clause() == UPDATE) {
            push(context);
        }
    }

    @Override
    public void clauseEnd(VisitContext context) {
        var auditMap = auditFields(context);
        if (auditMap != null && !auditMap.isEmpty()) {
            if (context.queryPart() instanceof InsertQuery) {
                InsertQuery<Record> insertQuery = (InsertQuery) context.queryPart();
                insertQuery.addValue(auditMap.get(CREATED_AT), new Timestamp(System.currentTimeMillis()));
                insertQuery.addValue(auditMap.get(CREATED_BY), ADMIN);
                insertQuery.addValue(auditMap.get(UPDATED_AT), new Timestamp(System.currentTimeMillis()));
                insertQuery.addValue(auditMap.get(UPDATED_BY), ADMIN);
            } else if (context.queryPart() instanceof UpdateQuery) {
                UpdateQuery<Record> updateQuery = (UpdateQuery) context.queryPart();
                updateQuery.addValue(auditMap.get(UPDATED_AT), new Timestamp(System.currentTimeMillis()));
                updateQuery.addValue(auditMap.get(UPDATED_BY), ADMIN);
            }
        }

        if (context.clause() == INSERT || context.clause() == UPDATE) {
            pop(context);
        }

    }

    @Override
    public void visitStart(VisitContext context) {
        List<Clause> clauses = asList(context.clauses());
        if ((clauses.contains(INSERT) || clauses.contains(UPDATE))
                && context.queryPart() instanceof TableImpl) {
            Table<?> table = (Table<?>) context.queryPart();
            Field<Timestamp> createdAtField = table.field(CREATED_AT, Timestamp.class);
            // 存在审计字段CREATED_AT，则默认其他几个审计也存在，反之亦然
            if (createdAtField == null) {
                return;
            }
            Field<String> createdByField = table.field(CREATED_BY, String.class);
            Field<Timestamp> updatedAtField = table.field(UPDATED_AT, Timestamp.class);
            Field<String> updatedByField = table.field(UPDATED_BY, String.class);
            auditFields(context).putAll(Map.of(CREATED_AT, createdAtField, CREATED_BY,
                    createdByField, UPDATED_AT, updatedAtField, UPDATED_BY, updatedByField));
        }
    }

    @Override
    public void visitEnd(VisitContext context) {

    }

    private Map<String, Field> auditFields(VisitContext context) {
        return auditFieldsStack(context).peek();
    }

    /**
     * stack to keep `deleted` field.
     */
    private Deque<Map<String, Field>> auditFieldsStack(VisitContext context) {
        //noinspection unchecked
        Deque<Map<String, Field>> data = (Deque<Map<String, Field>>)
                context.data(AUDIT_FIELDS);
        if (data == null) {
            data = new ArrayDeque<>();
            context.data(AUDIT_FIELDS, data);
        }
        return data;
    }

    private void push(VisitContext context) {
        auditFieldsStack(context).push(new HashMap<>());
    }

    private void pop(VisitContext context) {
        auditFieldsStack(context).pop();
    }

}

测试类

package com.zsx.test.listener;

import com.zsx.generator.jooq.tables.records.AuthorRecord;
import com.zsx.listener.CustomizeRecordListener;
import com.zsx.listener.CustomizeVisitListener;
import org.jooq.DSLContext;
import org.jooq.InsertValuesStep2;
import org.jooq.UpdateConditionStep;
import org.jooq.conf.ParamType;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultRecordListenerProvider;
import org.jooq.impl.DefaultVisitListenerProvider;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import java.util.concurrent.TimeUnit;

import static com.zsx.generator.jooq.tables.Author.AUTHOR;
import static com.zsx.generator.jooq.tables.Book.BOOK;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
@ExtendWith(SpringExtension.class)
public class CustomizeRecordAndVisitListenerTest {

    @Autowired
    private DSLContext dslContext;

    @Test
    void testRecordListenerInsertInto() {
        dslContext.configuration().set(DefaultRecordListenerProvider.providers(new CustomizeRecordListener()));
        InsertValuesStep2<AuthorRecord, String, Integer> insertValuesStep2 = dslContext.insertInto(AUTHOR).columns(AUTHOR.FIRST_NAME, AUTHOR.AGE).values("zhangfei", 20);
        String sql = insertValuesStep2.getSQL(ParamType.INLINED);
        assertEquals(1, insertValuesStep2.execute());
        // 通过CustomizeRecordListener的实现方式，此处设置默认值不成功
        assertFalse(sql.contains(CustomizeRecordListener.CREATED_AT));
    }

    @Test
    void testVisitListenerInsertInto() {
        dslContext.configuration().set(DefaultVisitListenerProvider.providers(new CustomizeVisitListener()));
        InsertValuesStep2<AuthorRecord, String, Integer> insertValuesStep2 = dslContext.insertInto(AUTHOR).columns(AUTHOR.FIRST_NAME, AUTHOR.AGE).values("zhangfei", 20);
        String sql = insertValuesStep2.getSQL(ParamType.INLINED);
        assertEquals(1, insertValuesStep2.execute());
        // 通过CustomizeVisitListener的实现方式，此处设置默认值成功
        assertTrue(sql.contains(CustomizeVisitListener.CREATED_AT));
    }

    @Test
    void testInsert() {
        AuthorRecord author = dslContext.newRecord(AUTHOR);
        author.setFirstName("张飞");
        author.setAge(21);
        assertEquals(1, author.insert());
    }

    @Test
    void testStore() {
        AuthorRecord author = dslContext.newRecord(AUTHOR);
        author.setFirstName("赵云");
        author.setAge(18);
        assertEquals(1, author.store());
    }

    @Test
    void testInsertAndStore() {
        AuthorRecord author = dslContext.newRecord(AUTHOR);
        author.setFirstName("关羽");
        author.setAge(23);
        try {
            TimeUnit.SECONDS.sleep(2);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        assertEquals(1, author.insert());
        author.setFirstName("项羽");
        assertEquals(1, author.store());
    }

    @Test
    void testUpdate() {
        InsertValuesStep2<AuthorRecord, String, Integer> values = dslContext.insertInto(AUTHOR).columns(AUTHOR.FIRST_NAME, AUTHOR.AGE).values("刘备", 30);
        AuthorRecord author = values.returning().fetchOne();
        assertNotNull(author);
        author.setLastName("小刘");
        assertEquals(1, author.update());
    }

    @Test
    void testRecordListenerUpdateExistsSubQuery() {
        dslContext.configuration().set(DefaultRecordListenerProvider.providers(new CustomizeRecordListener()));
        UpdateConditionSt