Search code examples
jooq

Jooq connection lifecycle

I am evaluating Jooq and I would like to confirm my understanding about the JDBC connection lifecycle.

I am using a connection pool (Hikari) and I configure a DSLContext using the datasource.

As I understand it:

  1. I am safe to create one DSLContext that I can reuse across my application in many threads? Is there any negative to doing this provided I don't modify the config? Contention etc?

  2. Each time I do a database access, does Jooq obtain a connection from the pool, do the DB access and then commit and release it? Eg:

Result<Record> result = context.select().from(AUTHOR).fetch();
Result<Record> otherResult = context.select().from(BOOKS).fetch();

Each of these queries would obtain and release a new connection from the pool?

  1. When using the transaction API like this:
context.transaction(ctx -> {
        DSLContext trans = DSL.using(ctx);
   ...
});

A connection would be obtained from the pool and reused for all accesses using the trans context?

Thanks!

Solution

  • I am safe to create one DSLContext that I can reuse across my application in many threads?

    Yes, you can, provided that after initialisation, you don't modify your Configuration and its components anymore, e.g. Settings.

    Is there any negative to doing this provided I don't modify the config? Contention etc?

    Au contraire, that's the recommended way of using a DSLContext. By reusing it, you will benefit from caching, e.g. for reflection if you're using Result.into(MyDto.class) methods, etc.

    Each time I do a database access, does Jooq obtain a connection from the pool, do the DB access and then commit and release it?

    Yes, that's how it works, if your DataSource is backed by a pool. Of course, the pool is free to provide jOOQ with the same connection every time, depending on transactional contexts that are governed outside of jOOQ. jOOQ doesn't care.

    When using the transaction API like this: [...] A connection would be obtained from the pool and reused for all accesses using the trans context?

    The transaction() method obtains a connection and keeps it for the duration of the TransactionalRunnable that you pass to it (i.e. the lambda). Your nested DSLContext trans instance will now work with the "cached" Connection, and not obtain new connections from your DataSource.