How can I set a timeout on spring DriverManagerDataSource

Question

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?

Answer 1

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?

Answer 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)

Answer 3

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.