Asynchronous SQL Execution with jOOQ and Java 8’s CompletableFuture

Reactive programming is the new buzzword, which essentially just means asynchronous programming or messaging.

Fact is that functional syntax greatly helps with structuring asynchronous execution chains, and today, we’ll see how we can do this in Java 8 using jOOQ and the new CompletableFuture API.

In fact, things are quite simple:

// Initiate an asynchronous call chain
CompletableFuture

    // This lambda will supply an int value
    // indicating the number of inserted rows
    .supplyAsync(() -> DSL
        .using(configuration)
        .insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
        .values(3, "Hitchcock")
        .execute()
    )
                          
    // This will supply an AuthorRecord value
    // for the newly inserted author
    .handleAsync((rows, throwable) -> DSL
        .using(configuration)
        .fetchOne(AUTHOR, AUTHOR.ID.eq(3))
    )

    // This should supply an int value indicating
    // the number of rows, but in fact it'll throw
    // a constraint violation exception
    .handleAsync((record, throwable) -> {
        record.changed(true);
        return record.insert();
    })
    
    // This will supply an int value indicating
    // the number of deleted rows
    .handleAsync((rows, throwable) -> DSL
        .using(configuration)
        .delete(AUTHOR)
        .where(AUTHOR.ID.eq(3))
        .execute()
    )

    // This tells the calling thread to wait for all
    // chained execution units to be executed
    .join();

What did really happen here? Nothing out of the ordinary. There are 4 execution blocks:

  1. One that inserts a new AUTHOR
  2. One that fetches that same AUTHOR again
  3. One that re-inserts the newly fetched AUTHOR (throwing an exception)
  4. One that ignores the thrown exception and delets the AUTHOR again

Finally, when the execution chain is established, the calling thread will join the whole chain using the CompletableFuture.join() method, which is essentially the same as the Future.get() method, except that it doesn’t throw any checked exception.

Comparing this to other APIs

Other APIs like Scala’s Slick have implemented similar things via “standard API”, such as calls to flatMap(). We’re currently not going to mimick such APIs as we believe that the new Java 8 APIs will become much more idiomatic to native Java speakers. Specifically, when executing SQL, getting connection pooling and transactions right is of the essence. The semantics of asynchronously chained execution blocks and how they relate to transactions is very subtle. If you want a transaction to span more than one such block, you will have to encode this yourself via jOOQ’s Configuration and its contained ConnectionProvider.

Blocking JDBC

Obviously, there will always be one blocking barrier to such solutions, and that is JDBC itself – which is very hard to turn into an asynchronous API. In fact, few databases really support asynchronous query executions and cursors, as most often, a single database session can only be used by a single thread for a single query at a time.

We’d be very interested to learn about your asynchronous SQL querying requirements, so feel free to comment on this post!

The Avajé Ebean Framework and asynchronous query execution

Yesterday, I posted about the Ollin Framework, which implements fetching data into callback types, similar to Spring’s JdbcTemplate. Another idea I had recently discovered, is that of asynchronous query execution. Even if there are only few use cases for that in every day development life, sometimes it’s interesting to be able to load data in the background. An example is an accounting application that displays a pre-calculated dashboard with interesting information for the user to keep them busy and distracted, while the heavy load of accounting data is loaded in the background for the more sophisticated screens. Or maybe you just want to take advantage of your high-performant Oracle database and run several OLAP queries in parallel while you generate a neat report for your user.

This idea is implemented in the Avajé EBean Framework, which makes use of the java.util.concurrent.Future API. The example they provide looks like this:

Query<Order> query = Ebean.find(Order.class);
FutureList<Order> futureList = query.findFutureList();

// do something else ...

if (!futureList.isDone()) {
    futureList.cancel(true);
}

// wait for the query to finish...
List<Order> list = futureList.get();

This really looks appealing, I think! In the jOOQ world, this would map to something like this

FutureResult<BookRecord> result =
create.selectFrom(BOOK)
      .where(Book.AUTHOR_ID.in( /* expensive subquery */ ))
      .fetchLater();

… and the rest is pretty much the same, as FutureResult just extends both org.jooq.Result and java.util.concurrent.Future.