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

score 3 · Answer 1 · answered Jan 05 '17 at 16:35

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.

Azodious · Answer 2 · 2017-01-05T11:25:21.387

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

edited Jan 05 '17 at 11:25

answered Jan 05 '17 at 11:19

Azodious

13,752
1
36
71

How can I get PreparedStatement object in controller if it placed controller->new thread->first service->second service-> PreparedStatement object – user5620472 Jan 05 '17 at 11:47
use setQueryTimeout - is bad solution. I can have big query. I need stop execute only bu user press button – user5620472 Jan 05 '17 at 11:47
@user5620472 You need to design the cancellation service too. – Azodious Jan 05 '17 at 11:48
@user5620472 `setQueryTimeout` i suggested as a way to cancel the query in general way not specific to your question, so that you can think about to use it if it can fit in your design with little alter. – Azodious Jan 05 '17 at 11:50
I'm talking about it and asking. I implemented the interupt of execution thread. if the thread is interupted - I break my cycle and return. But I can not understend how can I get PreparedStatement and call cancel() method – user5620472 Jan 05 '17 at 11:52
Why not create a list of `PreparedStatements`? pick the required object from this list to call cancel on it. You'll have to mark the object with some identifier. – Azodious Jan 05 '17 at 11:55
I update question. PreparedStatements placed in controller-> serviceA->serviceB – user5620472 Jan 05 '17 at 11:57

Database query interruption

2 Answers2