jOOQ - dslContext is null when injected

Question

I'm using Spring Boot, jOOQ and R2DBC.

By following this post I've created a DSLContext bean:

@Configuration
open class JooqConfig(private val cfi: ConnectionFactory) {

    @Bean
    open fun jooqContext(): DSLContext {
        return DSL.using(cfi).dsl();
    }
}

application.yml

spring:
  r2dbc:
    url: r2dbc:h2:mem:///~/db/testdb
    username: sa
    password: password

I've then created a Repository.

@Repository
@Transactional
open class JooqUserRepository(private val dslContext: DSLContext) {
   suspend fun getUser(): Record {
      return dslContext.select() // dslContext is null
           .from(USER_MODEL)
          .awaitFirst()
   }
}

This throws an exception:

Cannot invoke "org.jooq.DSLContext.select(org.jooq.SelectFieldOrAsterisk[])" because "this.dslContext" is null
java.lang.NullPointerException: Cannot invoke "org.jooq.DSLContext.select(org.jooq.SelectFieldOrAsterisk[])" because "this.dslContext" is null

I've checked my config and DSL.using(cfi).dsl() is definitely returning an object.

What am I missing here? I'm struggling to find documentation on how to get this setup with Spring Boot and R2DBC.

Update

Main class

@SpringBootApplication
open class BrewServerApplication

fun main(args: Array<String>) {
    SpringApplication.run(BrewServerApplication::class.java, *args)
}

Answer 1

I guess that the problem is next one:

If you will remove @Transactional from JooqUserRepository the dslContext Bean in going to be injected and will not be null:

And also the problem here is that the methods are final by default in Kotlin, so Spring is unable to create proxy for the class.

As a fix you have to rewrite:

@Repository
@Transactional
open class JooqUserRepository(private val dslContext: DSLContext) {
   suspend fun getUser(): Record {
      return dslContext.select()
           .from(USER_MODEL)
          .awaitFirst()
   }
}

TO

@Repository
@Transactional
open class JooqUserRepository(private val dslContext: DSLContext) {
   open suspend fun getUser(): Record {
      return dslContext.select()
           .from(USER_MODEL)
          .awaitFirst()
   }
}

Answer 2

Declare a DSLContext bean firstly,

@Configuration
class JooqConfig {

    @Bean
    DSLContext dslContext(ConnectionFactory connectionFactory) {
        return DSL.using(
                new TransactionAwareConnectionFactoryProxy(connectionFactory),
                SQLDialect.POSTGRES
        );
    }
}

Kotlin version:

@Configuration
class JooqConfig {

    @Bean
    fun dslContext(connectionFactory: ConnectionFactory) =
        DSL.using(TransactionAwareConnectionFactoryProxy(connectionFactory), SQLDialect.POSTGRES)

}

Then you can inject DSLContext in your repositories as other beans.

Check my example project: https://github.com/hantsy/spring-r2dbc-sample/tree/master/jooq

Check the tests using DSLContext, https://github.com/hantsy/spring-r2dbc-sample/blob/master/jooq/src/test/java/com/example/demo/PostRepositoryTest.java

For your codes, I would like use Flux/Mono to wrap the dslContext.select and convert the result to POJO/Kotlin Data Class.

val sql = dslContext.select(...).from(...).where(...) // which is a reactive `Publisher` since 3.15.

// create `Mono`/`Flux` from `Publisher`
Flux.from(sql).map{...}.asFlow() // multiple result
Mono.from(sql).map{...}.awaitSingle() // single result, such as `count`, `sum` 

Check my example using jOOQ and Kotlin Coroutines: https://github.com/hantsy/spring-r2dbc-sample/blob/master/jooq-kotlin-co-gradle/src/main/kotlin/com/example/demo/domain/repository/PostRepositoryImpl.kt