I wonder if there is a way to make asynchronous call 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 server 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?
Update:
I'm aware that using a FixedThreadPool may be a good work-around, but I'm surprised that nobody have developed a system really asynchronous (without the usage of extra thread).
Update 2 (December 2010):
AFAIK, there is no solution for doing real asynchronous jdbc call, the best work-around is to wrap the call with something like an Actor or a Promise.
It's impossible to make an asynchronous call to the database via JDBC, but you can make asynchronous calls to JDBC with Actors (e.g., actor makes calls to the DB via JDBC, and sends messages to the third parties, when the calls are over), or, if you like CPS, with pipelined futures (promises) (good implementation is Scalaz Promises)
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.
Scala actors by default are event-based (not thread-based) - continuation scheduling allows creating millions of actors on a standard JVM-setup.
If you're targeting Java, Akka Framework is an Actor model implementation that has good API both for Java and Scala.
Aside from that, synchronicity of JDBC makes a perfect sense for me. Database session cost is far higher than cost of the Java thread blocked (either in fore- or background) and waiting for response. You your queries run so long that capabilities of an executor service (or wrapping Actor/fork-join/promise concurrency frameworks) are not enough for you (and you're consuming too much threads) you should first of all think about the database load. Normally response, from a database comes very fast, and executor service backed with a fixed thread pool is a good enough solution. If you have to much long-running queries, you should consider upfront (pre-) processing - like nightly recalculation of the data or smth like that.