JOOQ and resource handling

Question

I use JOOQ for querying my relational database, I've recently been looking at the connection handling and its confusing me a little. I've tried reading the JavaDoc and also this: When is a Connection closed when calling JooQ DSLContext's .close(), if at all? but its creating even more FUD for me.

Currently my code does this:

try (final Connection cn = pool.getConnection()) {
    DSLContext dsl = DSL.using(cn, MARIADB);
    // query stuff
}

Essentially I'm treating JOOQ as just a querier that doesn't do connection handling at all. I've never had problems with this code.

However, I do get warnings from IntelliJ saying that DSLContext is AutoClosable and should be handled by a try-with-resources. I know it doesn't have to in this case but my first question is 'Can it?'. Is it safe to replace the above code with this instead:

try (final DSLContext dsl = DSL.using(pool.getConnection(), MARIADB)) {
    // query stuff
}

The other StackOverflow post said that you need to use close() on the DSLContext when you have created it using one of the helper methods. But what if you just passed the Connection object in? Will the close() still close my connection?

I also found that DSL has another using() that allows you to assign an entire DataSource. So instead I could also do this:

final DSLContext dsl = DSL.using(pool, MARIADB);

and then just leave out all the try-with-resources entirely. What are trade-offs here? Are there any even?

IntelliJ further complained about an UpdateQuery that has the AutoClosable interface (inherited from Query). Is it necessary to close my queries? I've always just called execute() and closed the underlying connection without problems.

Answer 1

What I am looking for is code that will satisfy these four requirements

1. It uses correct resource management
2. It uses JOOQ
3. The try-with-resources warning is turned on in the IDE
4. There are no warnings

The various pieces of code above all fail at least one of those requirements. But ultimately the connection handling doesn't matter that much because the query classes in JOOQ also generate tons of warnings.

The best way is indeed to turn the warning off, but then for JOOQ specifically using Intellij's exclusion rules. How to do this is mentioned in a comment on the page that Lukas linked too (https://blog.jooq.org/2015/12/02/a-subtle-autocloseable-contract-change-between-java-7-and-java-8/).

I'll just have to remember to do things the right way for JOOQ classes :)

Answer 2

Currently my code does this:

try (final Connection cn = pool.getConnection()) {
    DSLContext dsl = DSL.using(cn, MARIADB);
    // query stuff
}

That's correct usage.

Essentially I'm treating JOOQ as just a querier that doesn't do connection handling at all.

That's a correct assumption.

However, I do get warnings from IntelliJ saying that DSLContext is AutoClosable and should be handled by a try-with-resources

Many IDEs do this check, but it's usually best to turn them off. In Java 8+, you cannot reasonably expect an AutoCloseable to really need closing. One such example is Stream, which is AutoCloseable for those cases where it really does contain a resource, but mostly it doesn't.

This was a subtle API change in Java 8, leading to this warning in IDEs best being turned off (or you can maybe specify exceptions).

Your questions:

Is it safe to replace the above code with this instead:

try (final DSLContext dsl = DSL.using(pool.getConnection(), MARIADB)) {
  // query stuff
}

Yes, you can do that. The DSLContext.close() call will only close resources that were created by DSLContext. In your case, it does not have any effect.

For the record, resourceful DSLContexts are created, e.g. by DSL.using(url, username, password)

and then just leave out all the try-with-resources entirely. What are trade-offs here? Are there any even?

All of this has really nothing to do with resources.

IntelliJ further complained about an UpdateQuery that has the AutoClosable interface (inherited from Query). Is it necessary to close my queries? I've always just called execute() and closed the underlying connection without problems.

1. turn off that warning! :-)
2. queries can be resourceful when calling Query.keepStatement(true)