Is asynchronous jdbc call possible?

Question

I wonder if there is a way to make asynchronous calls to a database?

For instance, imagine that I've a big request that take a very long time to process, I want to send the request and receive a notification when the request will return a value (by passing a Listener/callback or something). I don't want to block waiting for the database to answer.

I don't consider that using a pool of threads is a solution because it doesn't scale, in the case of heavy concurrent requests this will spawn a very large number of threads.

We are facing this kind of problem with network servers and we have found solutions by using select/poll/epoll system call to avoid having one thread per connection. I'm just wondering how to have a similar feature with database request?

Note: I'm aware that using a FixedThreadPool may be a good work-around, but I'm surprised that nobody has developed a system really asynchronous (without the usage of extra thread).

** Update **
Because of the lack of real practical solutions, I decided to create a library (part of finagle) myself: finagle-mysql. It basically decodes/decodes mysql request/response, and use Finagle/Netty under the hood. It scales extremely well even with huge number of connections.

Answer 1

As mentioned in other answers JDBC API is not Async by its nature.
However, if you can live with a subset of the operations and a different API there are solutions. One example is https://github.com/jasync-sql/jasync-sql that works for MySQL and PostgreSQL.

Answer 2

Just a crazy idea : you could use an Iteratee pattern over JBDC resultSet wrapped in some Future/Promise

Hammersmith does that for MongoDB.

Answer 3

Here is an outline about what an non-blocking jdbc api could look like from Oracle presented at JavaOne: https://static.rainfocus.com/oracle/oow16/sess/1461693351182001EmRq/ppt/CONF1578%2020160916.pdf

So it seems that in the end, truly asynchronous JDBC calls will indeed be possible.

Answer 4

Ajdbc project seems to answer this problem http://code.google.com/p/adbcj/

There is currently 2 experimental natively async drivers for mysql and postgresql.