ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Interruptible Database Queries Interruptible Database Queries

by Slav Boleslawski
06/16/2004

One typical software design goal is responsiveness, understood as how easy and quick it is for the user to interrupt the current operation. Certain operations -- such as complex database queries; network I/O handling; extensive calculations; sorting of, or searching in, large data sets -- can take seconds or even minutes before they complete. Well-designed software allows the user to cancel such a long operation in progress. In this article I will demonstrate how to cancel a time-consuming database query by simply interrupting the thread in which the query runs. Such an interruptible database query will enable you to develop truly interactive programs that respond promptly even to the most impatient users.

A typical database query consists of four steps: creating a connection, creating a statement on this connection, obtaining a ResultSet object by executing the query on the statement, and, finally, retrieving data from the ResultSet object:

Connection conn = createConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
    // retrieve data from rs
}

The first two steps do not require much time to execute. Usually, it takes at most a couple of seconds to establish a database connection. In practice, a connection is obtained from a pool of reusable connections and this step happens very quickly.

The bulk of the execution time belongs to the last two steps, and I will focus our attention on them.

Interruptible Query Execution

The executeQuery() method of Statement runs synchronously and it can be interrupted by invoking the cancel() method on the same statement object from another thread (see the Statement.cancel() API). We will create a new class called QueryExecutor, which will have a method similar to the executeQuery() of Statement, but can be cancelled by invoking the interrupt() method on its executing thread. So instead of:

ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
    // retrieve data from rs
}

we will have:

QueryExecutor executor = 
    new QueryExecutor(stmt, query);
ResultSet rs = executor.executeQuery();
while(rs.next()) {
    // retrieve data from rs
}

The QueryExecutor class uses a "worker" thread to execute a query. Its executeQuery() method only passes query parameters to the worker, and then waits for the query to complete.

The steps of code execution in executeQuery() and the worker need to be carried out in the following order:

  • Wait for query parameters in the worker.
  • Set query parameters in executeQuery().
  • Wait for the query to complete, in executeQuery().
  • Execute the query in the worker.
  • Set query results in the worker.
  • Return query results in executeQuery().

The above order is achieved by using thread synchronization:

public synchronized ResultSet executeQuery(
        Statement statement, String query) 
        throws SQLException, 
        InterruptedException {
    //Set query parameters
    synchronized(params) {
        params.statement = statement;
        params.query = query;
        params.pending = true;
        params.notify();
    }
    
    synchronized(results) {
        try {
            //Wait for the query to complete
            while(!results.serviced) {
                results.wait();
            }
            if (results.exception != null) {
                throw results.exception;
            }
        } catch (InterruptedException e) {
            cancel();
            throw e;
        } finally {
            results.serviced = false;
        }
        return results.rs;
    }
}
//The implementation of the Runnable interface (for the worker thread)
public void run() {	
    ResultSet rs = null;
    SQLException ex = null;
    while(!closeRequest) {
        synchronized(params) {
            try {
                //Wait for query parameters
                while(!params.pending) {
                    params.wait();
                }
                params.pending = false;
            } catch (InterruptedException e) {
                if (closeRequest) {
                    return;
                }
            }
            //Execute query
            try {
                rs = params.statement.executeQuery(
                    params.query);
            } catch (SQLException e) {
                if (!cancelRequest) {
                    ex = e;
                }
            }
        }

        //Set query results
        synchronized(results) {
            results.rs = rs;
            results.exception = ex;
            results.serviced = true;
            results.notify();
        }
    }
}

QueryExecutor defines two private classes that help to keep the code cleaner. The Params class encapsulates query parameters:

private class Params {
    public Statement statement;
    public String query;
    public boolean pending;
}

and the Results class holds query results:

private class Results {
    public ResultSet rs;
    public SQLException exception;
    public boolean serviced;
}

In the beginning, the executeQuery() method sets query parameters in a block synchronized on the params object (created in the constructor of QueryExecutor). The worker waits for these parameters in a block also synchronized on the same params object. executeQuery() sets a Params.pending flag, notifies the worker, and waits for the query to complete, which is signalled by a serviced flag of Results.

The worker executes the query, and upon completion sets query results. If the query completes successfully, the worker sets a ResultSet object (rs). In case an exception is thrown during query execution, the worker sets an exception object of Results.

While the worker is busy executing a query, the executeQuery method is waiting on its wait() call. This is unblocked by the notify() call made by the worker when it completes its job. When executeQuery() wakes up, it first checks if an exception has been thrown in the worker during query execution. If so, it rethrows this exception. If not, the executeQuery() method returns a ResultSet object to the caller.

Let's have a closer look at what happens when the thread in which executeQuery() runs is interrupted. The executeQuery() method spends almost all of its time waiting for a query to complete. When an interruption occurs, the results.wait() method throws an InterruptedException exception. This exception should percolate to the caller of executeQuery(), but before this happens, a cancel() method is called to stop the running query:

private void cancel() {
    cancelRequest = true;
    try {
        params.statement.cancel();
        synchronized(results) {
            while(!results.serviced) {
                results.wait();
            }
        }
    } catch (SQLException e) {
        return;
    } catch (InterruptedException e) {
        return;
    } finally {
        cancelRequest = false;
    }
}

This method calls the cancel() method of Statement and waits for the query to terminate.

When the executing query is cancelled in this way, its statement throws an SQLException. In order to differentiate between this exception and other exceptions thrown because of some exceptional conditions during query execution, we use a cancelQuery flag, which is set when the statement is cancelled. Please note that when an SQLException is thrown in the worker, the exception object of Results is set only if the exception was not caused by a statement cancellation:

try {
    rs = params.statement.executeQuery(
        params.query);
} catch (SQLException e) {
    if (!cancelRequest) {
        ex = e;
    }
}

Pages: 1, 2

Next Pagearrow