我有一个自定义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执行后会关闭另外两个。

该代码在应用程序服务器上运行良好，在该服务器中，我们使用JOOQDefaultConnectionProvider和普通的JOOQ查询（使用DSL），而不使用原始文本查询。

但是，在使用a的迁移代码中，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()被称为via
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;