java - 如何在 JOOQ 的 `ExecuteListener` 中安全地执行自定义语句？

Question

我有一个自定义的 ExecuteListener，它在 JOOQ 当前正在查看的语句之前执行其他语句:

@Override
public void executeStart(ExecuteContext ctx) {
    if (ctx.type() != READ) {
        Timestamp nowTimestamp = Timestamp.from(Instant.now());
        UUID user = auditFields.currentUserId(); // NOT the Postgres user!

        Connection conn = ctx.connection();
        try (Statement auditUserStmt = conn.createStatement();
             Statement auditTimestampStmt = conn.createStatement()) {

            // hand down context variables to Postgres via SET LOCAL:
            auditUserStmt.execute(format("SET LOCAL audit.AUDIT_USER = '%s'", user.toString()));
            auditTimestampStmt.execute(format("SET LOCAL audit.AUDIT_TIMESTAMP = '%s'", nowTimestamp.toString()));
        }
    }
}

目标是提供一些 DB-Triggers，用于使用上下文信息进行审计。下面[1]给出了触发代码，给大家一个思路。请注意 try-with-resources 会在执行后关闭另外两个 Statement。

此代码在应用程序服务器中运行良好，我们使用 JOOQ 的 DefaultConnectionProvider 和普通的 JOOQ 查询(使用 DSL)，没有原始文本查询。

但是，在使用 DataSourceConnectionProvider 的迁移代码中，当 JOOQ 尝试执行其 INSERT/UPDATE 查询时，连接已经关闭。

触发异常的 INSERT 如下所示:

String sql = String.format("INSERT INTO migration.migration_journal (id, type, state) values ('%s', 'IDD', 'SUCCESS')", UUID.randomUUID());
dslContext.execute(sql);

这是引发的异常:

Exception in thread "main" com.my.project.data.exception.RepositoryException: SQL [INSERT INTO migration.migration_journal (id, type, state) values ('09eea5ed-6a68-44bb-9888-195e22ade90d', 'IDD', 'SUCCESS')]; This statement has been closed.
        at com.my.project.shared.data.JOOQAbstractRepository.executeWithoutResult(JOOQAbstractRepository.java:51)
        at com.my.project.demo.data.migration.JooqMigrationJournalRepositoryUtil.addIDDJournalSuccessEntry(JooqMigrationJournalRepositoryUtil.java:10)
        at com.my.project.demo.data.demodata.DemoDbInitializer.execute(DemoDbInitializer.java:46)
        at com.my.project.shared.data.dbinit.AbstractDbInitializer.execute(AbstractDbInitializer.java:41)
        at com.my.project.demo.data.demodata.DemoDbInitializer.main(DemoDbInitializer.java:51)
Caused by: org.jooq.exception.DataAccessException: SQL [INSERT INTO migration.migration_journal (id, type, state) values ('09eea5ed-6a68-44bb-9888-195e22ade90d', 'IDD', 'SUCCESS')]; This statement has been closed.
        at org.jooq.impl.Tools.translate(Tools.java:1690)
        at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:660)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:354)
        at org.jooq.impl.DefaultDSLContext.execute(DefaultDSLContext.java:736)
        at com.my.project.demo.data.migration.JooqMigrationJournalRepositoryUtil.lambda$addIDDJournalSuccessEntry$0(JooqMigrationJournalRepositoryUtil.java:12)
        at com.my.project.shared.data.JOOQAbstractRepository.executeWithoutResult(JOOQAbstractRepository.java:49)
        ... 4 more
Caused by: org.postgresql.util.PSQLException: This statement has been closed.
        at org.postgresql.jdbc.PgStatement.checkClosed(PgStatement.java:647)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:163)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:408)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:340)
        ... 7 more

我追溯到 DataSourceConnectionProvider.release()，因此 connection.close() 是通过 auditUserStmt.close() 调用的。请注意，SET 命令在同一 Connection 上执行非常重要。我可以从 JOOQ 的连接中获取一个我必须关闭自己的声明，但我找不到 JOOQ 方法来获取这样一个“非托管”声明。

我们正在使用 Hikari 连接池，因此 JOOQ 获取的连接是一个 HikariProxyConnection。在迁移代码中，DataSource 仅进行了最低限度的配置:

HikariDataSource dataSource = new HikariDataSource();
dataSource.setPoolName(poolName);
dataSource.setJdbcUrl(serverUrl);
dataSource.setUsername(user);
dataSource.setPassword(password);
dataSource.setMaximumPoolSize(10);

如何修复我的 ExecuteListener？

我正在使用 JOOQ 3.7.3 和 Postgres 9.5，以及 Postgres JDBC 驱动程序 42.1.1。

[1]: Postgres 触发代码:

CREATE OR REPLACE FUNCTION set_audit_fields()
  RETURNS TRIGGER AS $$
DECLARE
  audit_user UUID;
BEGIN
  -- Postgres 9.6 will offer current_setting(..., [missing_ok]) which makes the exception handling obsolete.
  BEGIN
    audit_user := current_setting('audit.AUDIT_USER');
    EXCEPTION WHEN OTHERS THEN
    audit_user := NULL;
  END;

  IF TG_OP = 'INSERT'
  THEN
    NEW.inserted_by := audit_user;
  ELSE
    NEW.updated_by  := audit_user;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Answer 1

根据@LukasEder 的建议，我最终使用 JDBC Connection 的包装器而不是 ExecuteListener 解决了这个问题。

这种方法的主要复杂之处在于 JDBC 不提供任何东西来跟踪事务状态，因此连接包装器需要在每次提交或回滚事务时重新设置上下文信息。

我在 this gist 中记录了我的完整解决方案，因为它太长了，无法放入 SO 答案中。