How thread-safe is a JOOQ Configuration with a DataSourceConnectionProvider?

Question

I tend to write my database classes using the following pattern:

public class AccountDao { 
    private final Configuration configuration;

    @Inject
    public AccountDao(final @Named(ACCOUNT_DATA_SOURCE) DataSource dataSource) {
        configuration = new DefaultConfiguration().derive(dataSource).derive(SQLDialect.POSTGRES);
    }

    private UserRecord getUserById(final UUID id) {
        return using(configuration)
            .selectFrom(USER)
            .where(USER.ID.eq(id))
            .fetchOne();
    }
  
    ...

If I create a second class that also needs to use JOOQ code to connect to the same database, at what level should I share the connection? Can I create a second Configuration using the same DataSource, or might that cause concurrency issues?

Answer 1

The Configuration contains caches, such as reflection caches, which you should not re-create every time. Ideally, you have a single Configuration for your DataSource, which you're sharing everywhere and injecting from your CDI setup. Spring Boot does that automatically for you. I'm not aware of any CDI/jOOQ auto configuration, but you can easily roll your own.

There's no thread safety guarantee, because a Configuration is just a collection of settings and SPI implementations, among which the ConnectionProvider. If your hand-rolled ConnectionProvider isn't thread safe, then your Configuration isn't, either. If you're using the DataSourceConnectionProvider as in your example, then the thread safety guarantees of your DataSource are inherited. In classic Spring / Java EE setups, a DataSource is usually thread-bound, allowing for AOP oriented transaction declarations, so you'll be safe injecting that instance everywhere (since you're already injecting the DataSource)

It's all documented here: https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/thread-safety/

org.jooq.Configuration, and by consequence org.jooq.DSLContext, make no thread safety guarantees, but by carefully observing a few rules, they can be shared in a thread safe way. We encourage sharing Configuration instances, because they contain caches for work not worth repeating, such as reflection field and method lookups for org.jooq.impl.DefaultRecordMapper. If you're using Spring or CDI for dependency injection, you will want to be able to inject a DSLContext instance everywhere you use it.