有一些运行时间较长的查询会消耗应用程序的内存。我尝试设置超时，在应用程序还没响应前中断执行，但我尝试的Hibernate和 Oracle都不尊重我设定的任何超时。如何强制查询超时？

Oracle数据库 - Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Hibernate - 5.6.10

java - java 17

Spring 5.3.21

<bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521/ltoi" />
        <property name="username" value="" />
        <property name="password" value="" />               
</bean>
<bean id="hibernateProperties"
        class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <description>
            Globale Einstellungen/Properties zu Hibernate
            Connection Pool, Caching-Strategie, Fetch-Strategie, ...
      </description>
        <property name="properties">
            <props>
                <prop key="hibernate.dialect">de.cfs.hbn.CustomOracleDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.default_schema">CASHDATA</prop>
                <prop key="hibernate.current_session_context_class">thread</prop>
                <!--  <prop key="hibernate.transaction.factory_class">org.hibernate.engine.transaction.internal.jdbc.JdbcTransactionFactory</prop> -->
                <prop key="hibernate.transaction.coordinator_class">jdbc</prop>
                <prop key="hibernate.connection.release_mode">after_transaction</prop>
                <prop key="hibernate.max_fetch_depth">4</prop>
                <prop key="hibernate.jdbc.fetch_size">50</prop>
                <prop key="hibernate.jdbc.batch_size">50</prop>
                <prop key="hibernate.order_inserts">true</prop>
                <prop key="hibernate.order_updates">true</prop>
                <prop key="hibernate.jdbc.batch_versioned_data">true</prop>               
                <prop key="hibernate.cache.use_second_level_cache">false</prop>
                <prop key="hibernate.cache.use_query_cache">false</prop>
                <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
                <prop key="hibernate.id.new_generator_mappings">true</prop>
                <prop key="hibernate.hql.bulk_id_strategy">org.hibernate.hql.spi.id.inline.InlineIdsInClauseBulkIdStrategy</prop>
                <prop key="hibernate.allow_update_outside_transaction">true</prop>

                <prop key="hibernate.use_sql_comments">true</prop>

            </props>
        </property>
    </bean>


@Test
    public void timeoutTest2()
    {
        Session se = sessionFactory.openSession();

        String query = """
                select c.id
                from Transaction c
                join Customer k on k.id = c.kd_id
                where k.kd_nr = 35
                """;

        List<BigDecimal> resultList = se.createNativeQuery(query)        
        //.setHint("org.hibernate.timeout", 1)
        .setTimeout(1)
        .getResultList();

        se.close();

        System.out.println(resultList.size());

        assertTrue(resultList.size() > 0);

    }

try
    {
        //Class.forName(OracleDriver.class.getClass().getName().toString()).newInstance();
        Connection conn = DriverManager
                .getConnection("jdbc:oracle:thin:@127.0.0.1:1521/LTOI", "user", "password");            

        conn.setAutoCommit(false);

        PreparedStatement statement = conn.prepareStatement("select * from cashdata.neu_cashpoint_xml");
        statement.setQueryTimeout(2);
        statement.setFetchSize(1000000);

        ResultSet result = statement.executeQuery();

        while (result.next())
        {                
            System.out.println(result.getBigDecimal(1));                
        }

        conn.commit();
        result.close();
        statement.close();
        conn.close();
    }
    catch (SQLException e)
    {
        System.out.println(e.getMessage());
    }

解决方案

Oracle数据库自8i版本及更早就支持查询超时。（实际上，Oracle通过从客户端发送信号来取消正在运行的查询。）

JDBC标准也支持这个功能（setQueryTimeout），但该特性是可选的，因为某些DBMS不支持它。因此你可能需要检查你当前的JDBC驱动版本，可能它不支持，或者实现有缺陷。

另外，在处理DML语句时，查询不会立即被取消，你必须等待事务/语句回滚。

更新：当Oracle JDBC驱动程序支持超时时，你应该在JVM中看到一个名为OracleTimeoutPollingThread（或类似名称）的线程。这个线程负责发送“取消消息”。

此外，取消正在运行的查询是通过TCP头中的OOB/URG标志实现的。你可以通过TCP dump捕获这条消息：

tcpdump -i <iface> host dbhost and port 1521 -vvv -n 'tcp[13] & 0x20 != 0'