How to stop execution of the query after timeout

Question

Currently i am trying below code.The problem is after timeout(5 seconds) catch (TimeoutException e) block is getting execute but not able to cancel execution of query. I want to cancel execution after timeout and continue with next part of code.

                    ExecutorService executor = Executors.newSingleThreadExecutor();
                    Future<String> future = executor.submit(new Callable() {
                        public String call() throws Exception {

                            System.out.println("task running!");
                            ResultSet rs = psLoadingStr.executeQuery();
                            return "OK";
                        }
                    });

                    try 
                    {
                        future.get(5, TimeUnit.SECONDS); //timeout is in 5 seconds
                    } 
                    catch (TimeoutException e) 
                    {
                        System.err.println("Timeout");
                        future.cancel(true);
                    }
                    executor.shutdownNow();

I don't think there is a good way to cancel a JDBC query. You could set a timeout: http://stackoverflow.com/questions/10947303/stop-or-terminate-long-running-query-in-jdbc — Thilo, Aug 03 '16 at 04:11
Try closing your connection and resultset in TimeoutException block `rs.close()` for example — jarvo69, Aug 03 '16 at 04:12
Do you actually need the ExecutorService? Calling JDBC on the current thread is much simpler if you are going to wait for completion in the next line anyway. — Thilo, Aug 03 '16 at 04:13
@Thilo As he has written it, the service is not of much use. But thing is: if the ExecutorService is coming from **somewhere** (dependency injenction), then you can also inject mocked services; or a service that actually executes on the same thread. That is really great for testing. Whereas: when you do stuff directly on your thread (or some other thread), testing might be harder. — GhostCat, Aug 03 '16 at 04:56

score 0 · Answer 1 · answered Aug 03 '16 at 04:15

You can set a timeout on executing a query. SQLException will be thrown if the query doesn't complete in time and times out:

preparedstatement.setQueryTimeout(seconds);
ResultSet resultSet = preparedStatement.executeQuery();

while( resultSet.next() ){
     // do some stuff
}

Have a look at setQueryTimeout documentation

How to stop execution of the query after timeout

1 Answers1