Querying the DB within the ExecuteListener.end event in jOOQ - Stack Overflow

admin管理员组

文章数量:1344975

I need to check some conditions after an insert statement was executed in a jOOQ project. For this, I've written a custom ExecuteListener, overriding its end(ExecuteContext) method.

Within the end method, I need to query for some meta data and access the data in the DB. Both requires me tho have a working connection. I want to execute for instance:

context.meta()
       .filterTables(t -> tables.stream().anyMatch(table -> Objects.equals(table.asTable().getName(), t.getName())))
       .getPrimaryKeys();

This works perfectly fine as long as jOOQ handles the connections. As soon as I use this approach in an environment where a connection pool is in place (Agroal in Quarkus in my case), the connection is closed at the time ExecuteListener.end is called.

I tried to get a second connection like this:

final ConnectionProvider provider = ctx.configuration().connectionProvider();
try (Connection conn = provider.acquire()) {
                final DSLContext dsl = DSL.using(conn, ctx.configuration().dialect());
                final List<UniqueKey<?>> primaryKeys = MetadataInformation.getPrimaryKeys(dsl, insertTable);
...
...
}

But this returns also connections which are in state closed.

Is there as way to get around this problem?

For the 'jOOQ-only' test I use a H2 database (v 2.3.232), for the Quarkus test a Postgres DB (v 17.4.1). We have .jooq.pro:jooq:3.19.19 and .jooq.pro:jooq-meta:3.19.19 on the classpath.

Footnote: My code will be used in Quarkus and non-Quarkus environments. I.e. if possible, I do not want to reference internals (like DataSource) to get a new connection.

I need to check some conditions after an insert statement was executed in a jOOQ project. For this, I've written a custom ExecuteListener, overriding its end(ExecuteContext) method.

Within the end method, I need to query for some meta data and access the data in the DB. Both requires me tho have a working connection. I want to execute for instance:

context.meta()
       .filterTables(t -> tables.stream().anyMatch(table -> Objects.equals(table.asTable().getName(), t.getName())))
       .getPrimaryKeys();

This works perfectly fine as long as jOOQ handles the connections. As soon as I use this approach in an environment where a connection pool is in place (Agroal in Quarkus in my case), the connection is closed at the time ExecuteListener.end is called.

I tried to get a second connection like this:

final ConnectionProvider provider = ctx.configuration().connectionProvider();
try (Connection conn = provider.acquire()) {
                final DSLContext dsl = DSL.using(conn, ctx.configuration().dialect());
                final List<UniqueKey<?>> primaryKeys = MetadataInformation.getPrimaryKeys(dsl, insertTable);
...
...
}

But this returns also connections which are in state closed.

Is there as way to get around this problem?

For the 'jOOQ-only' test I use a H2 database (v 2.3.232), for the Quarkus test a Postgres DB (v 17.4.1). We have .jooq.pro:jooq:3.19.19 and .jooq.pro:jooq-meta:3.19.19 on the classpath.

Footnote: My code will be used in Quarkus and non-Quarkus environments. I.e. if possible, I do not want to reference internals (like DataSource) to get a new connection.

• jooq

Share Improve this question Follow asked yesterday SimonSimon 30111 gold badge22 silver badges1010 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

Instead of trying to use the context.configuration() directly (which no longer has a usable connection), you can use a new connection from your connection pool to create a new DSLContext .

Use the ExecuteContext's connection provider only if it guarantees active connections. If the provider doesn't work as expected in certain environments, use an externally managed DataSource.

@Override
public void end(ExecuteContext ctx) {
    final ConnectionProvider provider = ctx.configuration().connectionProvider();
    try (Connection connection = provider.acquire()) {
        if (connection == null || connection.isClosed()) {
            throw new IllegalStateException("Connection is null or closed.");
        }
        DSLContext dsl = DSL.using(connection, ctx.configuration().dialect());
        List<UniqueKey<?>> primaryKeys = MetadataInformation.getPrimaryKeys(dsl, insertTable);
        System.out.println(primaryKeys);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

If provider.acquire() gives issues (like returning closed connections) in environments like Quarkus, you might want to directly use the DataSource.

For scenarios where Quarkus and @Inject DataSource are applicable

import javax.inject.Inject;
import javax.sql.DataSource;

@Inject
DataSource dataSource;

@Override
public void end(ExecuteContext ctx) {
    try (Connection connection = dataSource.getConnection()) {
        DSLContext dsl = DSL.using(connection, ctx.configuration().dialect());
        List<UniqueKey<?>> primaryKeys = MetadataInformation.getPrimaryKeys(dsl, insertTable);
        System.out.println(primaryKeys);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

For environments without dependency injection, you can configure the data source explicitly using HikariCP or any configured connection pool

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

private static final DataSource dataSource;

static {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:your-database-url");
    config.setUsername("username");            
    config.setPassword("password");           
    config.setDriverClassName(".postgresql.Driver"); 
    dataSource = new HikariDataSource(config);
}

@Override
public void end(ExecuteContext ctx) {
    try (Connection connection = dataSource.getConnection()) {
        DSLContext dsl = DSL.using(connection, ctx.configuration().dialect());
        List<UniqueKey<?>> primaryKeys = MetadataInformation.getPrimaryKeys(dsl, insertTable);
        System.out.println(primaryKeys);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Don't run this in ExecuteListener::end, which happens at the very end of the execution lifecycle, with resources no longer being available? Instead, just run this at the ExecuteListener::executeEnd event, which happens at the end of the actual query execution but prior to fetching / closing / etc.

本文标签： Querying the DB within the ExecuteListenerend event in jOOQStack Overflow