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?
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)
stmt.cancel()with your database and your driver and your use case if it realy works...jdbctemplateyou should tag the question accordingly.cancelis a plain JDBC function with which you tagged your question. But it seems you can use it as well in your context.