Hibernate and jOOQ sharing a transaction

Question

I'm trying to put jOOQ alongside hibernate in an existing big service. The code works as attended, save one problem: it seems that jOOQ queries are oblivious to Spring transactions (annotation based approach).

The problem is that within the same call stack there are some hibernate operations (repositories), and jOOQ does not see those entities, while hibernate does. I suspect that the problem is in the bean definition, separate transaction managers, or what not. Please note that I'm not using a Spring bot application, rather a "plain" Spring (version 5.0.8-RELEASE).

Configuration is copied from Spring autoconfig:

@Configuration
public class JooqAutoConfiguration {

    @Bean
    public DataSourceConnectionProvider dataSourceConnectionProvider(DataSource dataSource) {
        return new DataSourceConnectionProvider(new TransactionAwareDataSourceProxy(dataSource));
    }

    @Bean
    public SpringTransactionProvider transactionProvider(PlatformTransactionManager txManager) {
        return new SpringTransactionProvider(txManager);
    }

    @Bean
    @Order(0)
    public DefaultExecuteListenerProvider jooqExceptionTranslatorExecuteListenerProvider() {
        return new DefaultExecuteListenerProvider(new JooqExceptionTranslator());
    }

    @Configuration
    public static class DslContextConfiguration {
        private final ConnectionProvider connection;

        private final DataSource dataSource;

        private final TransactionProvider transactionProvider;

        private final RecordMapperProvider recordMapperProvider;

        private final RecordUnmapperProvider recordUnmapperProvider;

        private final Settings settings;

        private final RecordListenerProvider[] recordListenerProviders;

        private final ExecuteListenerProvider[] executeListenerProviders;

        private final VisitListenerProvider[] visitListenerProviders;

        private final TransactionListenerProvider[] transactionListenerProviders;

        private final ExecutorProvider executorProvider;

        public DslContextConfiguration(ConnectionProvider connectionProvider,
                                       DataSource dataSource, ObjectProvider<TransactionProvider> transactionProvider,
                                       ObjectProvider<RecordMapperProvider> recordMapperProvider,
                                       ObjectProvider<RecordUnmapperProvider> recordUnmapperProvider, ObjectProvider<Settings> settings,
                                       ObjectProvider<RecordListenerProvider[]> recordListenerProviders,
                                       ExecuteListenerProvider[] executeListenerProviders,
                                       ObjectProvider<VisitListenerProvider[]> visitListenerProviders,
                                       ObjectProvider<TransactionListenerProvider[]> transactionListenerProviders,
                                       ObjectProvider<ExecutorProvider> executorProvider) {
            this.connection = connectionProvider;
            this.dataSource = dataSource;
            this.transactionProvider = transactionProvider.getIfAvailable();
            this.recordMapperProvider = recordMapperProvider.getIfAvailable();
            this.recordUnmapperProvider = recordUnmapperProvider.getIfAvailable();
            this.settings = settings.getIfAvailable();
            this.recordListenerProviders = recordListenerProviders.getIfAvailable();
            this.executeListenerProviders = executeListenerProviders;
            this.visitListenerProviders = visitListenerProviders.getIfAvailable();
            this.transactionListenerProviders = transactionListenerProviders.getIfAvailable();
            this.executorProvider = executorProvider.getIfAvailable();
        }

        @Bean
        public DefaultDSLContext dslContext(org.jooq.Configuration configuration) {
            return new DefaultDSLContext(configuration);
        }

        @Bean
        public DefaultConfiguration jooqConfiguration() {
            DefaultConfiguration configuration = new DefaultConfiguration();
            configuration.set(SQLDialect.MYSQL);
            configuration.set(this.connection);
            if (this.transactionProvider != null) {
                configuration.set(this.transactionProvider);
            }
            if (this.recordMapperProvider != null) {
                configuration.set(this.recordMapperProvider);
            }
            if (this.recordUnmapperProvider != null) {
                configuration.set(this.recordUnmapperProvider);
            }
            if (this.settings != null) {
                configuration.set(this.settings);
            }
            if (this.executorProvider != null) {
                configuration.set(this.executorProvider);
            }
            configuration.set(this.recordListenerProviders);
            configuration.set(this.executeListenerProviders);
            configuration.set(this.visitListenerProviders);
            configuration.setTransactionListenerProvider(this.transactionListenerProviders);
            return configuration;
        }

    }
}

Answer 1

If you let Spring Boot configure jOOQ's DSLContext, and behind the scenes, the Configuration and ConnectionProvider, then you shouldn't have a transactional problem. However, do note that Hibernate might not have flushed the changes applied to its caches to the database, and jOOQ only queries the database directly, not any Hibernate caches.

Hence, you must make sure to always flush Hibernate's session prior to running any jOOQ (or JDBC, or other native SQL queries).