如何在 JOOQ's 的 `ExecuteListener` 中安全地执行自定义语句? [英] How to safely execute custom statements within JOOQ's `ExecuteListener`?

问题描述

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

I have a custom ExecuteListener that executes additional statements before the statement JOOQ is currently looking at:

@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.

The goal is to provide some DB-Triggers for auditing with context information. The trigger code is given below [1] to give you an idea. Note the try-with-resources that closes the two additional Statements after execution.

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

This code works fine in the application server, where we use JOOQ's DefaultConnectionProvider and ordinary JOOQ queries (using the DSL), no raw text queries.

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

However, in the migration code, which uses a DataSourceConnectionProvider, the connection is already closed when JOOQ attempts to execute its INSERT/UPDATE query.

触发异常的 INSERT 如下所示:

The INSERT that triggers the exception looks like this:

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()，因此通过 auditUserStmt.close() 调用了 connection.close().请注意，在同一 Connection 上执行 SET 命令至关重要.我可以从 JOOQ 的连接中获取一个我必须关闭自己的语句，但我找不到 JOOQ 方法来获取这样的非托管"语句.

I traced this back to DataSourceConnectionProvider.release() and therefore connection.close() being called via auditUserStmt.close(). Note that it is critical that the SET commands are executed on the same Connection. I would be fine with obtaining a Statement from JOOQ's connection that I have to close myself, but I can't find a JOOQ method to obtain such an "unmanaged" statement.

我们使用的是Hikari连接池，所以JOOQ获取的连接是一个HikariProxyConnection.在迁移代码中，DataSource 只进行了最低限度的配置:

We're using the Hikari connection pool, so the connection acquired by JOOQ is a HikariProxyConnection. From within the migration code, the DataSource is configured only minimally:

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 触发代码:

[1]: Postgres Trigger Code:

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;

推荐答案

根据@LukasEder 的建议，我最终使用 JDBC Connection 的包装器而不是 来解决这个问题执行监听器.

As per @LukasEder's suggestion, I ended up solving this problem with a wrapper around the JDBC Connection instead of with an ExecuteListener.

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

The main complication in this approach is that JDBC does not provide anything to track the transaction status, and therefore the connection wrapper needs to re-set the context information every time the transaction was committed or rollbacked.

我在这个要点中记录了我的完整解决方案，因为它太长而无法放入所以答案.

I documented my full solution in this gist, since it's too long to fit in an SO answer.

这篇关于如何在 JOOQ's 的 `ExecuteListener` 中安全地执行自定义语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！