Pooled Data-sources and Spring

In an earlier post we saw non pooled data-sources. However both DriverManagerDataSource and SingleConnectionDataSource have their own limitations.
As SingleConnectionDataSource has only one database connection to work with, it doesn’t scale well in a multi-threaded application.
On the other hand the DriverManager-DataSource is capable of supporting multiple threads. However opening multiple connections incurs a performance cost and hence there arises the need to pool connections. For this there are pooled data-sources available which create a pool of connections and provides them to the different users.
Spring as of now does not include any implementations of pooled data-sources. However it includes support for third party implementations one of them being DBCP(Database Connection Pool).
To test the same I created the below bean:

<bean id="dbcpDatasource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="org.gjt.mm.mysql.Driver"/>
    <property name="url" value="jdbc:mysql://127.0.0.1/information_schema" />
    <property name="username" value="root" />
    <property name="password" value="root" />

    <!-- The number of connections created when the pool is started. 
    (actually created when first connection is opened)-->
    <property name="initialSize" value="5"/>

    <!-- The maximum number of connections that can be allocated from the pool 
        at the same time. If zero, there’s no limit. -->
    <property name="maxActive" value="10"/>

    <!-- The maximum number of connections that can be idle in the pool without 
        extras being released. If zero, there’s no limit. -->
    <property name="maxIdle" value="5"/>
        
     <!-- The minimum number of connections that can remain idle in the pool 
     without new connections being created.-->
    <property name="minIdle" value="5"/>
    
    <!-- The maximum number of prepared statements that can be allocated from 
        the statement pool at the same time. If zero, there’s no limit. -->
    <property name="maxOpenPreparedStatements" value="0"/>

    <!-- How long the pool will wait for a connection to be returned to the 
        pool (when there are no available connections) before an exception is thrown. 
        If -1, wait indefinitely. -->
    <property name="maxWait" value="-1"/>

    <!-- How long a connection can remain idle in the pool before it’s eligible 
        for eviction. -->
    <property name="minEvictableIdleTimeMillis" value="600000"/>

    <!-- Whether or not to pool prepared statements (Boolean). -->
    <property name="poolPreparedStatements" value="true"/>
    
</bean>

Most of the above properties have default values.I decided to test the data-source and view its properties.

private static void displayDsProperties(final DataSource dataSource)
            throws SQLException {
//        System.out.println("dataSource.getLoginTimeout() : "
//                + dataSource.getLoginTimeout()); //UNSUPPORTED
        BasicDataSource basicDataSource = (BasicDataSource) dataSource;
        System.out.println("basicDataSource.getDefaultAutoCommit() : "
                + basicDataSource.getDefaultAutoCommit());
        System.out.println("basicDataSource.getDefaultCatalog() : "
                + basicDataSource.getDefaultCatalog());
        System.out.println("basicDataSource.getDefaultTransactionIsolation() : "
                + basicDataSource.getDefaultTransactionIsolation());
        System.out.println("basicDataSource.getDefaultReadOnly() : "
                + basicDataSource.getDefaultReadOnly());
        System.out.println("basicDataSource.getDriverClassName() : "
                + basicDataSource.getDriverClassName());
        System.out.println("basicDataSource.getInitialSize() : "
                + basicDataSource.getInitialSize());
//        System.out.println("basicDataSource.getLoginTimeout() : "
//                + basicDataSource.getLoginTimeout()); //UNSUPPORTED
        System.out.println("basicDataSource.getMaxActive() : "
                + basicDataSource.getMaxActive());
        System.out.println("basicDataSource.getMaxIdle() : "
                + basicDataSource.getMaxIdle());
        System.out.println("basicDataSource.getMaxOpenPreparedStatements() : "
                + basicDataSource.getMaxOpenPreparedStatements());
        System.out.println("basicDataSource.getMaxWait() : "
                + basicDataSource.getMaxWait());
        System.out.println("basicDataSource.getMinEvictableIdleTimeMillis() : "
                + basicDataSource.getMinEvictableIdleTimeMillis());
        System.out.println("basicDataSource.getMinIdle() : "
                + basicDataSource.getMinIdle());
        System.out.println("basicDataSource.getNumActive() : "
                + basicDataSource.getNumActive());
        System.out.println("basicDataSource.getNumIdle() : "
                + basicDataSource.getNumIdle());
        System.out.println("basicDataSource.getNumTestsPerEvictionRun() : "
                + basicDataSource.getNumTestsPerEvictionRun());
        System.out.println("basicDataSource.getPassword() : "
                + basicDataSource.getPassword());
        System.out.println("basicDataSource.getRemoveAbandonedTimeout() : "
                + basicDataSource.getRemoveAbandonedTimeout());
        System.out.println("basicDataSource.getRemoveAbandoned() : "
                + basicDataSource.getRemoveAbandoned());
        System.out.println("basicDataSource.getLogAbandoned() : "
                + basicDataSource.getLogAbandoned());
    }

public static void main(String[] args) throws SQLException {
    final BeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource("spring-dbcp.xml"));
    DataSource dataSource = (DataSource) beanFactory.getBean("dbcpDatasource");
    System.out.println("The dataSource is created as " + dataSource);
    displayDsProperties(dataSource);
}

The output is as below:

The dataSource is created as org.apache.commons.dbcp.BasicDataSource@6025e7
basicDataSource.getDefaultAutoCommit() : true
basicDataSource.getDefaultCatalog() : null
basicDataSource.getDefaultTransactionIsolation() : -1
basicDataSource.getDefaultReadOnly() : false
basicDataSource.getDriverClassName() : org.gjt.mm.mysql.Driver
basicDataSource.getInitialSize() : 5
basicDataSource.getMaxActive() : 10
basicDataSource.getMaxIdle() : 5
basicDataSource.getMaxOpenPreparedStatements() : 0
basicDataSource.getMaxWait() : -1
basicDataSource.getMinEvictableIdleTimeMillis() : 600000
basicDataSource.getMinIdle() : 5
basicDataSource.getNumActive() : 0
basicDataSource.getNumIdle() : 0
basicDataSource.getNumTestsPerEvictionRun() : 3
basicDataSource.getPassword() : root
basicDataSource.getRemoveAbandonedTimeout() : 300
basicDataSource.getRemoveAbandoned() : false
basicDataSource.getLogAbandoned() : false