How to get the underlying connection inside a transaction using jOOQ?

Question

I'm using jOOQ inside an existing project which also uses some custom JDBC code. Inside a jOOQ transaction I need to call some other JDBC code and I need to pass through the active connection so everything gets inside the same transaction.

I don't know how to retrieve the underlying connection inside a jOOQ transaction.

create.transaction(configuration -> {
    DSLContext ctx = DSL.using(configuration);

    // standard jOOQ code
    ctx.insertInto(...);

    // now I need a Connection
    Connection c = ctx.activeConnection(); // not real, this is what I need
    someOtherCode(c, ...);
});

Reading the docs and peeking a bit on the source code my best bet is this:

configuration.connectionProvider().acquire()

But the name is a bit misleading in this particular use case. I don't want a new connection, just the current one. I think this is the way to go because the configuration is derived and I will always get the same connection, but I'm not sure and I can't find the answer in the documentation.

Answer 1

jOOQ's API makes no assumptions about the existence of a "current" connection. Depending on your concrete implementations of ConnectionProvider, TransactionProvider, etc., this may or may not be possible.

Your workaround is generally fine, though. Just make sure you follow the ConnectionProvider's SPI contract:

Connection c = null;
try {
    c = configuration.connectionProvider().acquire();
    someOtherCode(c, ...);
}
finally {
    configuration.connectionProvider().release(c);
}

The above is fine when you're using jOOQ's DefaultTransactionProvider, for instance.

Note there is a pending feature request #4552 that will allow you to run code in the context of a ConnectionProvider and its calls to acquire() and release(). This is what it will look like:

DSL.using(configuration)
   .connection(c -> someOtherCode(c, ...));