2

I have a long running select query on an embedded H2 Database and want to allow the user to cancel the query.

How can this be done? I cannot find anything about this.

[UPDATE] To be more specific I'm running my query using JPA. How can the query be stopped?

D-rk
  • 5,513
  • 1
  • 37
  • 55
  • possible duplicate of [How can I abort a running JDBC transaction?](http://stackoverflow.com/questions/295920/how-can-i-abort-a-running-jdbc-transaction) – Aaron Digulla Feb 24 '14 at 10:09
  • thanks for the hint. I'm running my query using JPA. So I don't have a statement which I can cancel. I updated the question accordingly. – D-rk Feb 24 '14 at 10:19
  • JPA provides no query cancel mechanism. Obviously if you were using JDO you could do that – DataNucleus Feb 24 '14 at 10:22
  • ok, so if I want to stick to JPA, there is no chance of stopping a query prematurely? Perhaps there is a possiblity which is specific to the H2 database? – D-rk Feb 24 '14 at 10:29
  • _Can_ you use the JDBC API? – Thomas Mueller Feb 24 '14 at 10:50
  • Use it for what? I think I'm only restricted to JPA because I don't want to rewrite all my queries. – D-rk Feb 24 '14 at 11:33
  • @Dirk You can cancel a query using JDBC, even if the query is not executed using JDBC. That's why I ask. But maybe it's the wrong approach - would a [query timeout](http://h2database.com/html/grammar.html#set_query_timeout) be better? – Thomas Mueller Feb 25 '14 at 05:10
  • How is this query cancelling using JDBC done? – D-rk Feb 25 '14 at 11:27

2 Answers2

6

H2 supports a query timeout setting. You can set this in the database URL as follows: jdbc:h2:~/db/test;query_timeout=10000. (Maybe this is not the right approach for you, but it might be for others that read this question.)

You can also cancel a query running in another connection (session) using the cancel_session function. But for this you need to enable the multi-threaded mode, which is currently not recommended for production use (it is still experimental in version 1.3.175).

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
0

If you have a few queries which might take very long, then don't use JPA for them. Create your own statement so you can cancel it.

Alternatively, get the source code for a JDBC proxy like log4jdbc and add code that allows you to get connections and statements per thread. That would allow you to get the statement after JPA has sent it to the proxy.

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820