Cancel SQL Statement with JDBC

Question

I am stuck with this problem here. I am running an application on my Tomcat Application Server. As a frontend I am using an HTML site with javascript in it, in the backend i am using Java.

When the user clicks on a button several sql queries are made, one after another. Now I want to provide the ability to cancel this query if the user wants to.

I already checked if my jdbc driver and the database are compatible for the cancel() method and this is just fine.

Here is my code :

PreparedStatement stmt = null;

public void runQuery(String query) {
    Connection con = getConnection();       

    try {
        stmt = con.prepareStatement(query);
        stmt.execute();
    } catch(SQLException e) {
        e.printStackTrace();
    } finally {
        if(stmt != null && !stmt.isClosed()) {
            stmt.close();
        }

        if(con != null) {
            con.close();
        }
    }
}

public void cancelQuery() {
    try {
        if(stmt != null && !stmt.isClosed()) {
            stmt.cancel();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

So the user clicks the run button => runQuery is executed and stmt is initialized/overriden with the query which needs to execute.

Then the user clicks the cancel button => cancelQuery is executed. Unfortunately I sometimes get a NullPointerException because stmt is null. But it should not even call cancelQuery if the stmt is null ?! Here is the stacktrace:

    Stacktrace:] with root cause
java.lang.NullPointerException
    at com.sybase.jdbc3.jdbc.SybStatement.doCancel(SybStatement.java:646)
    at com.sybase.jdbc3.jdbc.SybStatement.cancel(SybStatement.java:614)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.cancel(DelegatingStatement.java:269)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.cancel(DelegatingStatement.java:269)
    at de.package.util.DBHelper.cancelQuery(DBHelper.java:82)
.....

Any idea why this keeps producing an exception ? How can I cancel the statement the right way ?

EDIT: I had allook at the link in the comments and now running the cancel() method from a different thread. However the NullPointer still happens. This is how i call the cancel() method now:

public void cancelQuery() {
        Thread thread = new Thread(new SQLCancelRunnable(stmt));
        thread.start();
}

    public class SQLCancelRunnable implements Runnable {
    PreparedStatement stmt;

    public SQLCancelRunnable(PreparedStatement stmt) {
        this.stmt = stmt;
    }

    @Override
    public void run() {
        if(stmt != null) {
        try {
            System.out.println(stmt);
            System.out.println(stmt.toString());
                stmt.cancel();
                System.out.println("canceled");

        } catch (SQLException e) {
            e.printStackTrace();
        }

       }
   }
}

EDIT2 Found my answer the problem was the finally block of the runQuery() method. Because I closed statement & connection the NullPointer was thrown. I now removed this block but this, of course, leads to huge resource leaking. Anyone who can guide me in the right direction how to close my resources properly ?

Answer 1

You'll need to synchronize statement closing:

public void runQuery(String query) {
    ...
    try {
        stmt = con.prepareStatement(query);

        stmt.execute();
        ...
    finally {
        synchronized(this) {
            if(stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
        }
    }
}

public void cancelQuery() {
    synchronized(this) {
        if(stmt != null && !stmt.isClosed()) {
            stmt.cancel();
        }
    }
}

between every statement another thread may execute some kind of code, so a simple if is not sufficient to be sure the state of the world is as you expect it to be.

Synchronizing on this may not be the best option, but as the stmt may be null we can't use this object.

Edit: If the code starting the query is asynchronous you'll also have to be prepared for calls to cancelQuery even before your statement is prepared.

Answer 2

You can use Statement.cancel()

As Java Docs Says

void cancel()
            throws SQLException

Cancels this Statement object if both the DBMS and driver support aborting an SQL statement. This method can be used by one thread to cancel a statement that is being executed by another thread.

You can also set setQueryTimeout if query execution passes a threshhold time

java.sql.Statement.setQueryTimeout(seconds)

Update

Don't forget to Rollback the transation

Anyone who can guide me in the right direction how to close my resources properly ?

this is what for finally block was invented

finally{
//Release All Resources
}

The finally block always executes when the try block exits. This ensures that the finally block is executed even if an unexpected exception occurs. But finally is useful for more than just exception handling — it allows the programmer to avoid having cleanup code accidentally bypassed by a return, continue, or break. Putting cleanup code in a finally block is always a good practice, even when no exceptions are anticipated.

Answer 3

    PreparedStatement stmt = null;

    public void runQuery(String query) {
    Connection con = getConnection();       


    try {
    stmt = con.prepareStatement(query);
    stmt.execute();

    }
    catch(SQLException e) {
        e.printStackTrace();
    }
    finally {
        if(stmt != null && !stmt.isClosed()) {
            stmt.close();
        }

        if(con != null) {
        con.close();
        }
    }

}

public void cancelQuery() {
    try {
        if(stmt != null && !stmt.isClosed()) {
            stmt.cancel();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Try this. I have added a Generic Exception just after the SQLException.

Cannot say this is a very clean solution but it will ignore the null pointer exception which is possiblly raised by stmt.close() statement.

Answer 4

You should take a look at Apache DB-Utils it makes this kind of problems disappear and you can simply write something like:

} finally {
    DbUtils.closeQuietly(resutlSet);
    DbUtils.closeQuietly(preparedStatement);
    DbUtils.closeQuietly(connection);
}