Configuring apache dbcp PoolingDataSource with Spring

Question

Tried to follow the pattern on apache dbcp examples, I understand everything except how and where the database properties come from and in which bean they have to be placed in application context.

I used Spring Data Source instead, but as I recall I configured it in hurry and I remember having difficulties with configuring the original dataSource provided by apache dbcp itself. So I happen to have time to face the problem and fulfill the original intent of using PoolingDataSource.

The reason I used Spring implementation is because it provides means of setting up the parameters to connect to database.

http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/jdbc/datasource/DriverManagerDataSource.html

According to http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/PoolingDataSource.html

There are no methods to populate configuration like url or load driver.

I tried to track it through the object pools etc. , but got really lost.

Replying upfront: Yes, I don't want to use apache basicDataSource.

So now I am returning to the problem and can't really understand where to fetch the parameters? Driver? Url? It seems that url, pw and username are set on connection factory. But where to fetch postgresql driver to be loaded?

Please help to complete the configuration.

(using spring for configuration)

<!-- the one I want to use now -->
  <bean id="dataSource" class="org.apache.commons.dbcp.PoolingDataSource">
     <constructor-arg><ref bean="pool"/></constructor-arg>
  </bean>

<!-- the one I used before as a workaround

<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
   <property name="driverClassName" value="org.postgresql.Driver"/>
   <property name="url" value="jdbc:postgresql:postgres"/>
   <property name="username" value="magicUserName"/>
   <property name="password" value="magicPassword"/>
</bean>  -->

<bean id="pool" class="org.apache.commons.pool.impl.GenericObjectPool">
   <property name="minEvictableIdleTimeMillis"><value>300000</value </property>
   <property name="timeBetweenEvictionRunsMillis"><value>60000</value </property>
</bean>

<bean id="dsConnectionFactory" class="org.apache.commons.dbcp.DataSourceConnectionFactory">
   <constructor-arg><ref bean="dataSource"/></constructor-arg>
</bean>

<bean id="poolableConnectionFactory" class="org.apache.commons.dbcp.PoolableConnectionFactory">
    <constructor-arg index="0"><ref bean="dsConnectionFactory"/ </constructor-arg>
    <constructor-arg index="1"><ref bean="pool"/></constructor-arg>
    <constructor-arg index="2"><null/></constructor-arg>
    <constructor-arg index="3"><null/></constructor-arg>
    <constructor-arg index="4"><value>false</value></constructor-arg>
    <constructor-arg index="5"><value>true</value></constructor-arg>
</bean>



 <bean id="txManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

 <tx:annotation-driven transaction-manager="txManager" />

</beans>

I believe we are interested just in the first two, but I included everything just in case.

Seems to be there are many people using workarounds: http://forum.springsource.org/showthread.php?10772-How-do-I-create-a-org-apache-commons-dbcp-PoolableConnection

Answer 1

You can config as below:

  <bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="url" value="<put database connection url here>" />
    <property name="username" value="XXXXXX" />
    <property name="password" value="XXXXXXXX" />
    <property name="driverClassName" value="<database driver here>" />
</bean>

<bean id="pool" class="org.apache.commons.pool.impl.GenericObjectPool">
    <property name="minEvictableIdleTimeMillis"><value>300000</value></property>
    <property name="timeBetweenEvictionRunsMillis"><value>60000</value></property>
</bean>

<bean id="dsConnectionFactory" class="org.apache.commons.dbcp.DataSourceConnectionFactory">
    <constructor-arg><ref bean="dataSource"/></constructor-arg>
</bean>

<bean id="poolableConnectionFactory" class="org.apache.commons.dbcp.PoolableConnectionFactory">
    <constructor-arg index="0"><ref bean="dsConnectionFactory"/></constructor-arg>
    <constructor-arg index="1"><ref bean="pool"/></constructor-arg>
    <constructor-arg index="2"><null/></constructor-arg>
    <constructor-arg index="3"><null/></constructor-arg>
    <constructor-arg index="4"><value>false</value></constructor-arg>
    <constructor-arg index="5"><value>true</value></constructor-arg>
</bean>

<bean id="pooledDS" class="org.apache.commons.dbcp.PoolingDataSource" 
                                               depends-on="poolableConnectionFactory">
    <constructor-arg><ref bean="pool"/></constructor-arg>
</bean>

And you can use "pooledDS" (PoolingDataSource) the same any orther DataSource.

Ortherwise, i think you should simply use BacsicDataSource, you still can config number of connections in pool by "initialSize" and "maxActive":

<bean id="basicDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="removeAbandoned" value="true"/>
        <property name="initialSize" value="10" />
        <property name="maxActive" value="50" />
    </bean>