What is the recommended way to execute long-running queries in jOOQ?
Are there any downsides in executing query asynchronously(except blocking operation on ForkJoinPool which could be mitigated by supplying execution to dedicated thread pool), converting result into CompletableFuture and calling get() method with specified timeout?
dslFactory
.create()
.selectFrom(TABLE_NAME)
.where(conditions)
.fetchAsync()
.toCompletableFuture().get(10, TimeUnit.SECONDS);
As you've noted, when running jOOQ queries on top of JDBC (sync or async, doesn't matter), you're going to block somewhere, and you have to manage those resources somehow. In a lot of cases, this isn't a big problem.
If blocking turns out to be a problem, jOOQ 3.15 starts supporting R2DBC which allows for running your query reactively, without blocking in the client: https://blog.jooq.org/2021/07/15/reactive-sql-with-jooq-3-15-and-r2dbc
Your query would then look like this:
Flux<TableNameRecord> result = Flux.from(
dslFactory
.create()
.selectFrom(TABLE_NAME)
.where(conditions));