What's the proper way to handle JDBC connections with Spring and DBCP?

Question

I'm using the Spring MVC to build a thin layer on top of a SQL Server database. When I began testing, it seems that it doesn't handle stress very well :). I'm using Apache Commons DBCP to handle connection pooling and the data source.

When I first attempted ~10-15 simultaneous connections, it used to hang and I'd have to restart the server (for dev I'm using Tomcat, but I'm gonna have to deploy on Weblogic eventually).

These are my Spring bean definitions:

<bean id="dataSource" destroy-method="close"
      class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="[...]"/>
    <property name="username" value="[...]" />
    <property name="password" value="[...]" />
</bean>

<bean id="partnerDAO" class="com.hp.gpl.JdbcPartnerDAO">
    <constructor-arg ref="dataSource"/>
</bean>

<!-- + other beans -->

And this is how I use them:

// in the DAO
public JdbcPartnerDAO(DataSource dataSource) {
    jdbcTemplate = new JdbcTemplate(dataSource);
}

// in the controller
@Autowired
private PartnerDAO partnerDAO;

// in the controller method
Collection<Partner> partners = partnerDAO.getPartners(...);

After reading around a little bit, I found the maxWait, maxActive and maxIdle properties for the BasicDataSource (from GenericObjectPool). Here comes the problem. I'm not sure how I should set them, performance-wise. From what I know, Spring should be managing my connections so I shouldn't have to worry about releasing them.

<bean id="dataSource" destroy-method="close"
      class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="[...]"/>
    <property name="username" value="[...]" />
    <property name="password" value="[...]" />
    <property name="maxWait" value="30" />
    <property name="maxIdle" value="-1" />
    <property name="maxActive" value="-1" />
</bean>

First, I set maxWait, so that it wouldn't hang and instead throw an exception when no connection was available from the pool. The exception message was:

Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

There are some long-running queries, but the exception was thrown regardless of the query complexity.

Then, I set maxActive and maxIdle so that it wouldn't throw the exceptions in the first place. The default values are 8 for maxActive and maxIdle (I don't understand why); if I set them to -1 there are no more exceptions thrown and everything seems to work fine.

Considering that this app should support a large number of concurrent requests is it ok to leave these settings to infinite? Will Spring actually manage my connections, considering the errors I was receiving? Should I switch to C3P0 considering it's kinda dead?

Answer 1

DBCP maxWait parameter should be defined in milliseconds. 30 ms is very low value, consider increasing it to 30000 ms and try again.