Stop executing Spring JDBC query programmatically in Java

Question

Currently, I'm running long running MySQL queries using Java Spring JDBC library. For this program, I want a way of stopping a running query. I want to do this programmatically, so killing processes is not what I need here.

Also, setting timeouts and killing threads using Java code is not preferred.

Is there a way to do this using programming conventions, or is there any preferred way or design to achieve this use case in Java?

Similar [question](https://stackoverflow.com/q/28481809/4808122), but you should always test the `stmt.cancel()` with your database and your driver and your use case if it realy works... — Marmite Bomber, Mar 18 '21 at 19:13
I'm using Spring JDBC, we have an option to cancel statement there? I can't find it. — Pramodya Mendis, Mar 18 '21 at 20:19
If you use `jdbctemplate` you should tag the question accordingly. `cancel` is a *plain* JDBC function with which you tagged your question. But it [seems](https://stackoverflow.com/q/1054642/4808122) you can use it as well in your context. — Marmite Bomber, Mar 18 '21 at 21:34
Does this answer your question? [Cancel SQL Statement with JDBC](https://stackoverflow.com/questions/28481809/cancel-sql-statement-with-jdbc) — Amin, Mar 19 '21 at 01:57

score 1 · Answer 1 · answered Mar 18 '21 at 19:15

You can use the SQL KILL Command to stop a process

sample

MariaDB [(none)]> show processlist;;
+----+-------------+-----------+------+---------+------+--------------------------+-------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info              | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+-------------------+----------+
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL              |    0.000 |
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL              |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL              |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL              |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL              |    0.000 |
| 46 | root        | localhost | NULL | Query   |   56 | User sleep               | select sleep(199) |    0.000 |
| 47 | root        | localhost | NULL | Query   |    0 | init                     | show processlist  |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+-------------------+----------+
7 rows in set (0.00 sec)


MariaDB [(none)]> kill 46;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> show processlist;;
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 47 | root        | localhost | NULL | Query   |    0 | init                     | show processlist |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.00 sec)

Appreciate your answer, but i want to do this using Java – Pramodya Mendis Mar 18 '21 at 20:19 — Pramodya Mendis, Mar 18 '21 at 20:19

Stop executing Spring JDBC query programmatically in Java

1 Answers1