Cancelling Queries Using Jdbc API

Cancelling long running queries using Jdbc API

In this blog, I will share my experimentation with query cancellation timer. The objective is to cancel those queries which take a lot of time to execute.

The Jdbc API provides Statement.cancel() method to cancel the statement being run. But, this method should be
called from a different thread than the one that is executing the SQL query. In order to call that method we need a reference to the statement object also.

So, I have made a sample code that uses the Java Concurrency API to achieve the task.

The java concurrent API ExecutorService can be used to execute a task in a new thread. When we submit a task like a Runnable or Callable to the ExecutorService, it returns a Future object. The Future object can be used to get the results from the other thread.

Here in this example, ExecutorService is a single thread executor. The object QueryExecutor is a Callable, which means it is nothing but a Runnable that returns an object. Where as a Runnable doen’t return anything as void is the return type of run() method. QueryExecutor returns a JsonObject.

The queryExecutor object is submitted to the ExecutorService, which returns a Futureobject.

The future object has a get method that takes the timeout and the timeout unit. Upon timeout, the execution of the other thread will be stopped and the TimeoutExceptionoccurrs. Otherwise the return value will be obtained.

class JdbcService {
    private static final Logger logger = LoggerFactory.getLogger(JdbcService.class);

    public JsonObject query(@Nullable Connection connection, String sql) {
        if (connection == null || sql == null) {
            throw new IllegalArgumentException("Nopes! The connection object or sql is null");
        }
        if (logger.isDebugEnabled()) {
            logger.debug("JDBC query executor has started.");
        }

        long now = System.currentTimeMillis();
        Statement statement = null;
        ExecutorService executor = Executors.newSingleThreadExecutor();
        Future<JsonObject> future = null;
        try {
            statement = connection.createStatement();
            int jdbcQueryCancellationTime = Configurations.getCancellationTime();

            QueryExecutor queryExecutor = new QueryExecutor(statement, sql);

            //Submit the callable to the executor. Start the thread
            future = executor.submit(jdbcQueryExecutor);

            //Wait for specified time limit. Otherwise cancel statement
            JsonObject queryResult = future.get(jdbcQueryCancellationTime, TimeUnit.SECONDS);
            executor.shutdown();

            long time = System.currentTimeMillis();

            if (logger.isInfoEnabled()) {
                logger.info(String.format("Execution time taken for the query %s is %s milliseconds", sql,
                        (time - now)));
            }

            return queryResult;
        } catch (SQLException | InterruptedException | ExecutionException ex) {
            cancelStatement(statement, ex);
            throw new QueryException("Failed to query the database.", ex);
        } catch (TimeoutException ex) {
            cancelStatement(statement, ex);
            future.cancel(true);
            throw new JdbcQueryTimeoutException("Timeout exception occurred. Increase the timeout limit.");
        } finally {
            DbUtils.closeQuietly(connection);
            DbUtils.closeQuietly(statement);
            if (!executor.isTerminated()) {
                logger.error("Forcefully shutting down the executor");
            }

            executor.shutdownNow();

            if (logger.isDebugEnabled()) {
                logger.debug("Successfully shutdown the jdbc query executor service");
            }
        }
    }

    private void cancelStatement(Statement statement, Exception ex) {
        logger.error("Cancelling the current query statement. Timeout occurred.", ex);
        try {
            statement.cancel();
        } catch (SQLException exception) {
            logger.error("Calling cancel() on the Statement issued exception. Details are: ", exception);
        }
    }
}

One important caution is that upon TimeoutException the future.cancel(true) method should be called. And the executor should be shutdown once the task is complete.

Hope you understood the self explanatory code sample. In case of queries comment on this post so that I can reply.

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download

Thank you for reading this post and wish you happy coding.

Rajasekhar
Java BI Developer
Helical IT Solutions