How do I call java.sql.Connection::abort?

Question

I would like to write a SwingWorker that, when the user cancels the task, it: 1) cancels the PreparedStatement and 2) aborts the Connection.

Here is some code to illustrate:

class Task extends SwingWorker<Void, Void> {

    Connection conn;
    PreparedStatement p_stmt;
    @Override
    protected Void doInBackground() throws Exception {
        ResultSet results = p_stmt.executeQuery();
        while(results.next()) {
            if(isCancelled())
                return;
        }
        return null;
    }

    void cancell() {
        cancel(true);
        try {
            p_stmt.cancel();
            conn.abort(/* How do I properly implement Executor? */);
        } catch (SQLException e) {
            handleError(e);
        }
    }

    @Override
    protected void done() {
        try {
            get();
        } catch (ExecutionException | InterruptedException e) {
            handleError(e);
        }
    }
}

static void handleError(Exception e) {
    // ...
}

This line:

conn.abort(/* How do I properly implement Executor? */);

Is the line I'm interested in. What should I pass to Connection::abort?

Answer 1

You need to provide any implementation of Executor here. This could for example be an ExecutorService created by one of the methods of Executors, for example Executors.newSingleThreadPool(). You could also use a ForkJoinPool, for example the common pool returned by ForkJoinPool.commonPool(), but it could also be as basic as using a lambda that creates a new thread for each runnable passed:

connection.abort(runnable -> new Thread(runnable).start())

You could even just keep everything blocked in the current thread until the cleanup is done:

connection.abort(Runnable::run)

Each have their pros and cons, and unfortunately it is hard to provide a good advice because the JDBC specification is unclear on how the Executor is to be used. For example, should the driver add all cleanup tasks to the executor before returning from abort (the wording in the Javadoc implies this, but it is open to interpretation), or is it free to add tasks asynchronously during the cleanup? How well-behaved should the cleanup task(s) be in terms of execution time, etc.

If you create an Executor specifically for calling abort using Executors.newSingleThreadPool(), you will need to shut it down eventually otherwise you are leaking threads, but if the abort clean up tasks asynchronously add new tasks, there is no clearly defined moment that you can shutdown the executor service (or you may have shut it down too soon). This may be an indication that you should use a fixed thread pool with maybe more than one thread for the lifetime of your application, instead of creating it specifically for calling the abort.

On the other hand if you use the common fork/join pool, you may impact other parts of your application that also use it if a lot of tasks are added, or if the tasks are long running.

The first lambda I provided can result in the creation of a lot of threads if the driver creates a lot of tasks, and the second lambda I provided could result in a long wait or maybe even incorrect behaviour if the driver expects it to be run asynchronously.

In other words, it is a bit of a headache.