如何在JOOQ的`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的包装而不是使用ExecuteListener解决了这个问题.

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的`ExecuteListener`中安全地执行自定义语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！