How do I decrease the timeouts to create a c3p0 ComboPooledDataSource and get an Oracle db connection?

Question

I have a pretty simple Hibernate project which connects to an Oracle database.

If for some reason it is unable to connect to Oracle (like if the network is down), it will take over a minute to fail. This occurs while building the data source and seems to occur when trying to interact with the database as well.

I would like to change the settings for it to fail in a few seconds. After changing what I believe are the relevant settings, nothing seems to change. It's like my configuration is being ignored.

Relevant portions of the Spring file doing my dependency injection, appContext.xml:

<bean id="oracleDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" 
    destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.OracleDriver"/>
    <property name="jdbcUrl" value="jdbc:oracle:thin:@****"/>
    <property name="user" value="****"/>
    <property name="password" value="****"/>
</bean>

<bean id="oracleSessionFactory" 
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="oracleDataSource"/>
    <property name="configLocation">
        <value>classpath:oracle.hibernate.cfg.xml</value>
    </property>
</bean>

And my hibernate configuration file: oracle.hibernate.cfg.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>
        <property name="hibernate.c3p0.max_size">1</property>
        <property name="hibernate.c3p0.checkoutTimeout">5000</property>
        <property name="hibernate.c3p0.acquireRetryAttempts">0</property>
        <property name="show_sql">true</property>
        <mapping class="****"/>
    </session-factory>
</hibernate-configuration>

I have heard this can be caused by not having hibernate-c3p0-version.jar in the classpath, so here are the classpath settings for my test...

...and here is where hibernate-c3p0-4.2.11.Final.jar is specified in my Maven pom.xml:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>4.2.11.Final</version>
</dependency>

Answer 1

You are using an injected DataSource in your configuration. Trying to add configuration to hibernate isn't going to help as hibernate isn't in control of the datasource. Set the properties on the Spring configured ComboPooledDataSource.

<bean id="oracleDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.OracleDriver"/>
    <property name="jdbcUrl" value="jdbc:oracle:thin:@****"/>
    <property name="user" value="****"/>
    <property name="password" value="****"/>
    <property name="acquireRetryAttempts" value="0"/>
    <property name="checkoutTimeout" value="5000"/>       
</bean>

Remove the hibernate.c3p0 properties from your hibernate configuration as well as the added dependency as that isn't going to be used.