How can I set a timeout on spring DriverManagerDat

问题:

We are using DriverManagerDataSource from the Spring framework (version 2.5) to pool connections to Oracle. However, it seems that these connections don't have any timeout defined - yesterday, after emergency database restart, we had a thread hanging on a socket read inside the database connection. How can I set the timeout, to say 10 mins, so that it raises an exception next time?

回答1:

I ended up changing the bean in the Spring context in the following way:

<bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" autowire="no">
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="connectionProperties">
        <props>
            <prop key="oracle.net.READ_TIMEOUT">60000</prop>
        </props>
    </property>
</bean>

I don't know if it works yet.

回答2:

Oracle has a builtin connection pool: oracle.jdbc.pool.OracleDataSource. I recommend you use that directly. The reference for Oracle JDBC (10gR2, other versions will be similar) is in http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/toc.htm. Points of interest:

• Configuration of data sources
• Statement caching
• Implicit connection caching (and specially timeout properties)

回答3:

If your Oracle driver implementation supports a timeout property you can pass it through to the underlying implementation via getConnectionProperties().put(key, timeout)

I'm assuming you realise that DriverManagerDataSource isn't a connection pool? From the docs:

NOTE: This class is not an actual connection pool; it does not actually pool Connections. It just serves as simple replacement for a full-blown connection pool, implementing the same standard interface, but creating new Connections on every call. [..] If you need a "real" connection pool outside of a J2EE container, consider Apache's Jakarta Commons DBCP or C3P0.

You may also be interested in OCI Connection Pooling?