Set query timeout in milliseconds, not seconds, on invocation of PreparedStatement?

Question

Is there a way to set the query timeout in milliseconds, instead of seconds? The java.sql.Statement API only has a method for seconds, but even 1 second is too slow for my use case.

Java API: http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)

I am using the Oracle DB.

Answer 1

This is quite convoluted, but could work:

1. have an ExecutorService
2. create a Callable for the query
3. submit the callable to ExecutorService
4. the Future<ResultType> will have a "get(Long, TimeUnit)" function, blocking until at most the set timeout - which has configurable granularity (at least it promises to be like that...

Something like this in almost-Java-code:

public class MyCallable implements Callable<ResultType> {
  @Override
  public ResultType call() throws Exception {
    //do query, with 1s timeout - that way no thread will be busy for more than 1s
    return result;
  }
}

Handling the requests

ExecutorService threadPool; //initialised somewhere earlier

Future<ResultType> futureResult = threadPool.submit(new MyCallable());
try {
    ResultType result = futureResult.get(100, TimeUnit.MILLISECONDS);
    //results received in time, do the processing here
} catch(TimeoutException e) {
    //request too slow -- handle it
} catch( other exceptions...) { ... }

Concerns:

• I don't know how much overhead this means...
• timeout: I don't know how exactly will they be handled.
• the requests that time out, will be stuck in the threadpool until the inner, JDBC timeout (1s) kicks in...
• threadpool: if fixed, might be a bottleneck (see concern above), if dynamic: might be an overhead