请问如何在Druid连接池中强行中断正在执行的sql

[zong-wei-dev]

我遇到了这样一种情况: 通过druid连接池执行了一个select查询sql, 这个sql执行时间非常长, 长时间不能返回结果集,
我没有权限在oracle服务方强行中断会话, 希望通过druid编程来控制实现对druid连接池中正在执行的sql进行强行中断,
我尝试通过如下代码来实现这种想法.但发现sql依然执行等待返回结果, 请问如何写代码才能实现?

if (dataSource != null && dataSource instanceof DynamicRoutingDataSource) {
            DynamicRoutingDataSource source = (DynamicRoutingDataSource) dataSource;
            Class<DruidDataSource> druidDataSourceClass = DruidDataSource.class;
            Field connectionsField = druidDataSourceClass.getDeclaredField("connections");
            connectionsField.setAccessible(true);
            for (Map.Entry<String, DataSource> sourceEntry : source.getCurrentDataSources().entrySet()) {
                if (sourceEntry.getValue() instanceof ItemDataSource) {
                    ItemDataSource itemDataSource = (ItemDataSource) sourceEntry.getValue();
                    DataSource realDataSource = itemDataSource.getRealDataSource();
                    if (realDataSource instanceof DruidDataSource) {
                        DruidDataSource druidDataSource = (DruidDataSource) realDataSource;
                        DruidConnectionHolder[] connections = (DruidConnectionHolder[]) connectionsField.get(druidDataSource);
                        if (connections.length > 0) {
                            for (int i = 0; i < druidDataSource.getPoolingCount(); ++i) {
                                DruidConnectionHolder connHolder = connections[i];
                                for (PreparedStatementHolder stmtHolder : connHolder.getStatementPool().getMap().values()) {
                                    connHolder.getStatementPool().closeRemovedStatement(stmtHolder);
                                    druidDataSource.closePreapredStatement(stmtHolder);
                                }
                                connHolder.getStatementPool().getMap().clear();
                                if (connHolder.getConnection() instanceof DruidPooledConnection) {
                                    DruidPooledConnection pooledConnection = (DruidPooledConnection) connHolder.getConnection();
                                    Thread ownerThread = pooledConnection.getOwnerThread();
                                    String name = ownerThread.getName();
                                    System.out.println(name);
                                }
                                druidDataSource.discardConnection(connHolder);
                            }
                        }
                    }
                }
            }
 } 

当我得到DruidDataSource 对象后, 不知道如何编写才能将一个正在执行的sql强行终止执行, 望给予答复, 谢谢!

[yswang0927]

JDBC没提供中断的接口，你可以设置 statement.setQueryTimeout() 。

[z3068]

可以试试 queryTimeout 和 transactionQueryTimeout 参数，单位是秒。