3

Kotlin coroutines and 'suspending functions' make it easy for the programmer to await the result of I/O without stopping the thread (the thread is given other work to do until the I/O is complete).

jOOQ is a Java-first product for writing and executing SQL in a typesafe way, but does not itself make explicit use of Kotlin coroutines.

Can jOOQ be called from a Kotlin co-routine scope to get the nice-to-write and thread-is-productive-even-during-IO benefits?

suspend fun myQuery() {
  return dsl.select()
  // .etc()
  .fetch()  // <- could this be a 'suspend' call?
}
David Bullock
  • 6,112
  • 3
  • 33
  • 43

2 Answers2

5

A: Yes.

BECAUSE org.jooq.ResultQuery<R extends Record> has a @NotNull CompletionStage<Result<R>> fetchAsync()` method which hooks into Java's (JDK 8+) machinery for 'futures'.

AND kotlinx-coroutines-jdk8 provides a bunch of extension methods for adapting between Kotlin suspending functions and JDK 8+ futures.

THEREFORE we can do:

import kotlinx.coroutines.future.await
...
suspend fun myQuery() {
  return dsl.select()
  //.etc()
  .fetchAsync()
  .await()    // <- IS  a suspending call !!!
}

It should be noted that there are a bunch of overloaded fetchX() methods on ResultQuery which provide lots of utility to synchronous calls, but they are not similarly overloaded for fetchAsync(). That's where the Kotlin programmer may wish to be familiar with the Java futures machinery: any sort of manipulation can be accomplished asynchronously using the thenApply {} method on CompletionStage<T>. For example, mapping the results:

suspend fun myQuery() {
  return dsl.select()
  //.etc()
  .fetchAsync()
  .thenApply { it.map(mapping(::Film)) }  // <- done as part of the 'suspend'
  .await()
}

although it should be fine to do it after the suspend:

suspend fun myQuery() {
  val records = dsl.select()
  //.etc()
  .fetchAsync()
  .await()

  return records.map(mapping(::Film))
}
David Bullock
  • 6,112
  • 3
  • 33
  • 43
  • 1
    As mentioned on your previous question, the `CompletionStage` will hopefully be [non-blocking in the near future, if supplied with an R2DBC connection](https://stackoverflow.com/a/68911636/521799) – Lukas Eder Aug 25 '21 at 06:34
  • 2
    @LukasEder Yes, though there are still advantages to be had (ie. minimal external design-forces distorting the 'shape of code') from the Kotlin side even though r2dbc is not *yet* in use under the covers for the actual non-blocking. Anyone who uses JDBC has the blocking behaviour, so it's not like anything is *lost* by using jOOQ. It's only people who are willing to distort their code-shape into the reactive style, and to live with the immaturity of r2dbc, who are any 'better off'. But with jOOQ, I can patiently wait for those gains while writing my app the way it *should be*. – David Bullock Aug 25 '21 at 08:34
  • @DavidBullock do you have any guidance on the optimal way to configure the `executorProvider` thread pool for jooq when using this API in JDBC blocking mode? For reference I'm using ktor as my server (coroutine-based), so I don't have an existing thread pool configured for blocking DB operations. – idolize Jan 13 '22 at 07:44
2

JOOQ added R2dbc support, the ResultQuery is a ReactiveStreams Publisher.

Currently the simplest approach is using Reactor Kotlin extension to convert the Reactor APIs to Kotlin Coroutines APIs.

  Flux.from(
     ctx.select()
     ...//etc
     ...// do not call fetch
  ) 
  .asFlow()

Update: Jooq guys cancelled the PR of Kotlin Coroutines on DslContext, so now you have to use kotlinx-coroutines-ractor as above.

When switching to 3.17 or later, use fetchAwait directly. There is a series of xxxAwait added.

ctx.select()
     ...//etc
     .fetchAwait()
but I did not find direct support for Kotlin Coroutines,**Jooq 3.17 ships official Koltin Coroutines support in `jooq-kotlin` module**.
Hantsy
  • 8,006
  • 7
  • 64
  • 109
  • 1
    The remark about the cancellation of this feature has been outdated, in the meantime. Given how simple it was, eventually, a `jooq-kotlin-coroutines` extension module has been added: https://www.jooq.org/doc/latest/manual/sql-building/kotlin-sql-building/kotlin-coroutines/ – Lukas Eder Feb 03 '23 at 15:31