Spring JDBC connection pool best practices

Question

I have a basic Spring JDBC application with a pretty basic configuration:

<bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">    <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>    <property name="url" value="jdbc:oracle:thin:@1.1.1.1:1521:XXX"/>    <property name="username" value="username"/>    <property name="password" value="password"/> </bean>  <bean id="dbThing" class="com.DbThing">    <property name="dataSource" ref="myDataSource"/> </bean> 

I would like to introduce a connection pool, and after reading several threads on SO I am a bit confused about which pooling library to use.

The libraries that seem to have more credits on SO are C3P0 and DBCP. Since I'm using Oracle, I could also use the pooled data source offered by the driver.

I understand that there are more libraries available - for instance the new Apache Tomcat 7 pooling libraries.

Is there any library that I should really avoid?

Is there any recommended configuration I should use with a given library?

Any "war story" you care to share?

Answer 1

C3PO and DBCP development have stalled mostly because they are mature. I have seen both of these drivers be able to support hundreds of transactions per second.

The Tomcat pool is a reworked & updated DBCP driver. MyBatis 3.0 also contains it's own pooling implementation which, based on code inspection, seems solid. Finally, there's BoneCP which claims to have the best performance. I haven't used any of these on a project yet.

Probably the best advice is to pick any of them test it. Spring makes it easy to swap out later.

Answer 2

As an alternative to BoneCP, have you perhaps tried Oracle's own database connection pool?

I've had good experiences for the last couple of weeks, so it might be worth giving it a shot - also, I suppose Oracle would know a thing or two about making a connection pool especially when paired up with their own database.

<bean id="dataSource" class="oracle.jdbc.pool.OracleConnectionPoolDataSource">     <property name="URL" value="${jdbc.url}" />     <property name="user" value="${jdbc.username}" />     <property name="password" value="${jdbc.password}" /> </bean> 

UPDATE: Also, if you're using (one of) the latest Oracle JDBC drivers (11.2.0.1+), you may want to try out the new Universal Connection Pool. The OracleConnectionPoolDataSource seems to be officially deprecated in favour of this pool. However, some users have reported errors using it, so it may be too early. I am in a position to use Oracle's latest JDBC drivers, so I will give it a try and update here as soon as have any info on this.

More info on this SO thread: Oracle UCP