How to cancel a long query in the database from another thread?

The reporting system takes data from the database. The cursor opens and the data is sent to the ecxel file. everything works in a loop that is spinning in a separate thread. One iteration of the cycle - one report - one request in the database. But the essence is different. Requests in a database are different and there are values ​​for which a request can be performed tending to infinity, for hours and hours. And here is such a situation - a cycle of 10 steps was launched and at step 3, for example, the value of the query with which it will complete 24 hours comes across. The user does not want to wait and wants to cancel the execution. Options with timeout are not suitable as there are requests that actually work for a long time and this is normal. Rough The interface displays the generation progress and the user sees by what value the report is currently being executed. And if it hangs for a long time, the user checks this value himself and decides to cancel the generation or leave. So here is how to implement a cancellation. The application hangs on this line:

ps.executeQuery();//PreparedStatement 

I drew information that there is a cool cancel() method for the PreparedStatement class that should cancel execution. But it turned out that he is not so cool and does not work as he wants. In the English stack, I rummaged through all and some argue that this method does not guarantee the cancellation of the query in the database. And he does not work for me either. I run the request for execution, then cancel it, the method works without errors, but the session hangs in the database. And only after killing the session with hands in the database is thrown in the program.

here is the code. here I create a PreparedStatement and add it to the cache in order to get it in another thread and cancel it:

 StatementCache.getInstance().addToMap(resolutionId, ps); ResultSet rs = ps.executeQuery(); StatementCache.getInstance().removeFromMap(resolutionId); 

And here I am trying to cancel

 PreparedStatement fromMap = StatementCache.getInstance().getFromMap(resolutionId); if (fromMap != null) { try { fromMap.cancel(); } catch (SQLException e) { return; } StatementCache.getInstance().removeFromMap(resolutionId); } 

and everything works out without errors. but the session is not upholstered.

Question: How can I cancel the execution of a long query in the database?

As an option, find out in the sessionId program and execute the kill session with a separate request. But I can not find an option to find out this sessionId. and the option seems too crutch.

EDIT If it helps to better understand how this code works

 public class StatementCache { private static volatile StatementCache instance; private ConcurrentMap<Long, PreparedStatement> map = new ConcurrentHashMap<>(); private StatementCache(){ } public static StatementCache getInstance() { StatementCache local = instance; if (local == null) { synchronized (StatementCache.class) { local = instance; if (local == null) { instance = local = new StatementCache(); } } } return local; } public void addToMap(Long id, PreparedStatement statement) { map.put(id, statement); } public void removeFromMap(Long id) { map.remove(id); } public PreparedStatement getFromMap(Long id) { return map.get(id); } } 

in one thread, PreparedStatement is placed here in the other one and is canceled (it changes already launched).