Be careful with DriverManagerDataSource

I needed to populate a lot of development data in a database. I wrote a simple script and configured my DataSource like this:

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName"
value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="{URL}"/>
</bean>

Then I fired up my script and let it run.

Soon my phone rang. It was a DBA.

DBA: “Are you connecting to dev007?”

Me: “Yes.”

DBA: “Well, stop it! You have made over 100,000 connections in the last two hours!”

What? Really? Yes, really.

A little research shows that DriverManagerDataSource makes a new connection, does the work, and then closes. Every. Single. Time. No pooling!

Now research shows that SingleConnectionDataSource would have worked nicely for me in this instance, but instead I used Apache’s BasicDataSource.

So then my setup looked something like this:

<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="{URL}"/>
<property name="poolPreparedStatements"><value>true</value></property>
<property name="maxActive"><value>1</value></property>
<property name="maxIdle"><value>1</value></property>

</bean>

I made the change, started the script, and verified with the DBA that it was not creating a new connection for each insert. If you were using this in a production-type environment (which I wasn’t in this case) you would want more than 1 for maxActive.