I work for a client on a product that makes direct queries against databases via JDBC. A while ago, I added some code so that a user could stop the execution of a series of queries by clicking on a cancel button. Behind the scenes, it interrupts the thread executing the queries. Since that thread checks whether it's been interrupted by calling
Thread.currentThread().isInterrupted() before starting to execute each query, no new queries will start once the cancel button was pushed. However, any query that's already started will run to completion before the code discovers that a request to cancel had been made. Recently, my client decided that we should make cancellation more granular and add the ability to stop a query in mid-stream. Looking at the
JDBC docs, there's nothing to indicate that any of the relevant methods respond to interrupts (none throw
InterruptedExceptions or claim to wrap them in a
SQLException), so I had to come up with another way. I settled on an approach where I submit the query as a
Callable to an
ExecutorService and then block, waiting for the result via
Future.get(). If the thread is interrupted while we're waiting, get() throws an InterruptedException, which gives us the chance to call
Statement.cancel(). It's pretty simple and works nicely. :)
Here's the code in a somewhat abridged/condensed form (I create the ExecutorService elsewhere using
Executors.newCachedThreadPool()):
final String sql = "...<some SQL>...";
final Statement statement = conn.createStatement();
Future<ResultSet> queryFuture =
execService.submit(new Callable<ResultSet>() {
@Override
public ResultSet call() throws Exception {
statement.execute(sql);
return statement.getResultSet();
}
}
);
ResultSet rs;
try {
rs = queryFuture.get();
} catch (InterruptedException e) {
logger.info("Query interrupted - calling Statement.cancel()");
statement.cancel();
throw e;
} catch (ExecutionException e) {
//code to handle or rethrow the exception
}
By the way, if any of the
java.util.concurrent classes above are unfamiliar to you, I
highly recommend
Java Concurrency in Practice by Brian Goetz (et al.). It's an excellent book.
Posts
