CODEWITHSUNDEEP
javaspringhibernateapache-commons-dbcp
DKG
DKG

Reputation: 387

Spring DBCP connection getting terminated

I have an application where the DB connection are created by the Spring configuration (then passed on to hibernate). Following is the default parameters in our spring file.

<bean id="localDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName">
    <value>${database.driver}</value>
    </property>
    <property name="url">
    <value>${dbUrl}</value>
    </property>
    <property name="username">
    <value>${dbUser}</value>
    </property>
    <property name="password">
    <value>${dbPassword}</value>
    </property>
</bean>

However, in a particular environment the Oracle DB connections are getting closed by a network setting after certain period of time. We initially thought that the closing of the connections is because of the connections being idle. We added validationQuery to periodically post a query on the server so that the connections are not idle. 

<bean id="localDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName">
    <value>${database.driver}</value>
    </property>
    <property name="url">
    <value>$dbUrl</value>
    </property>
    <property name="username">
    <value>$dbUser</value>
    </property>
    <property name="password">
    <value>$dbPassword</value>
    </property>
    <property name="validationQuery">
    <value>SELECT 1 FROM dual</value>
    </property>
    <property name="testWhileIdle">
    <value>true</value>
    </property>
    <property name="testOnReturn">
    <value>true</value>
    </property>
    <property name="timeBetweenEvictionRunsMillis">
    <value>1000</value>
    </property>
</bean>
  1. Even after the above setting the connections are still getting closed.
  2. Also observed that when the application is idle for 2-3 (varying) hours, the validation query stops getting executed on the server and then the connections are getting closed by the network settings.

My question is does adding validationQuery guarantees, the connections not being idle? The application fails on first login attempt throwing no DB connection; but works after 2-3 attempts. Not sure what is happening here.

Upvotes: 0

Views: 917

Answers (2)

gpa
gpa

Reputation: 2451

On the side note, C3P0 Connection Pool libraries is much better than DBCP. I had always issues in past with DBCP, ever since switched to C3P0 no issues so far...

http://www.mchange.com/projects/c3p0/

Upvotes: 0

Michael Peacock
Michael Peacock

Reputation: 2104

The validationQuery is only used to test a connection when it's retrieved from the connection pool. See: https://commons.apache.org/proper/commons-dbcp/configuration.html

Also, I've seen some odd behavior with org.apache.commons.dbcp.BasicDataSource and Oracle (11 and 12). We've had much better stability with the org.apache.tomcat.dbcp.dbcp.BasicDataSource instead. The config options are pretty close (maybe identical?) to the commons dbcp implementation, so conversion is a snap.

Upvotes: 1

Related Questions