Search This Blog

Monday, 20 August 2012

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=""/>
    <property name="url" value="jdbc:mysql://" />
    <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"/>
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);
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() :
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


  1. I was very pleased to find this web-site.I wanted to thanks for your time for this wonderful read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you blog post.

  2. Are you trying to make money from your websites or blogs using popunder advertisments?
    In case you do, did you take a look at Clickadu?