Database query interruption

Question

I have start some process in new thread:

FutureTask<?> futureTask = new FutureTask<Void>(() -> {
               startSomeProcess();
            }, null);

In startSomeProcess method I generate report for each item:

for (Item item : items) {
            if (Thread.interrupted()) {
                logger.info("currentThread isInterrupted");
                return;
            }
             generateReport(item);
     }

In generateReport() method I execute some select to DB and generate excel report from ResultSet:

try (PreparedStatement ps = conn.prepareStatement(query.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE)) {
                ResultSet rs = ps.executeQuery();
                //and fill excel from rs 
                 ...

But My ps.executeQuery(); can execute a long time(1 sec- 999999h. I have cancel button. If it press I make this

futureTask.cancel(true); 

and check exception in startSomeProcess(); method. But I can not check and interupt ps.executeQuery();. When I make futureTask.cancel(true); this executeQuery throw exception. But I am not configure this. It is automaticly.

My quations: when I interupt the thread - Can I or do I have myself interrupt the query to the database, or it just happens and I have to handle the error only?

EDIT:

User send command - "cancel report generate". I faind futureTask and call cancel() method. and break build report. But if ps.executeQuery(); already start - I do not know how break it

Answer 1

when I interupt the thread - Can I or do I have myself interrupt the query to the database, or it just happens and I have to handle the error only?

Once executeQuery starts, it can be stopped using cancel method.

Other way is to handle exception or use setQueryTimeout

Answer 2

My questions: when I interrupt the thread - Can I or do I have myself interrupt the query to the database, or it just happens and I have to handle the error only?

Interrupting the thread only sets a flag on the thread which causes only those methods that throw InterruptedException to do so. In this case the SQL methods don't throw InterruptedException so just calling future.cancel(true) will not work.

If you are trying to cancel a running SQL query then you are going to need to this at a JDBC level by canceling the underlying Statement. To quote from the javadocs of Statement.cancel():

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.

So in your case, this means that another thread is going to have to do the canceling of your ps. I'd create an object that wraps your generateReport() method that puts the PreparedStatement ps into a volatile field. Then when the method executes the ps it does so from the field and another thread could call cancel() on the object which calls ps.cancel().

It is important to note that query cancelation is SQL database and JDBC driver dependent. Not all databases support it.