Managing Data-sources via Spring

I am yet to work in a project where database connectivity is not required. Spring has covered this area of development too, providing a series of templates and DAOs. In most cases, the techniques provided by Spring need to use a data-source to connect to the database.
I lifted this definition from the IBM site

A data source is associated with a JDBC provider, which supplies the driver
implementation classes that are required for JDBC connectivity with your specific
vendor database. Application components transact directly with the data source
to obtain connection instances to your database. 

The article found in Java blog also covers the concept very well.
A diagrammatic flow of the interaction would be :

The application logic gets its database connections through the data-source. The application may directly use the connection or may use an underlying ORM framework like Hibernate(which will then directly use the connection) to complete its database operations. The data-source  is the one interacting with the JDBC driver to obtain the connection. 
The advantage is all database connection details now reside with the data-source and not with the application. Changing database connection details is as simple as reconfiguring/replacing the data-source. Another advantage is connection pooling. We can use pooled data-sources that more efficiently manage the connections that we get from the database, but that is an altogether different discussion. For now we simply focus on using data-sources.
The data- sources may be defined by a JDBC driver or they may be looked up by JNDI.
The data- sources associated with the JDBC driver are simple to use. They are mainly concerned with connecting to the database through the driver and returning you a simple java.sql.Connection instance. They do not include any smart logic for pooling.
Spring provides two such inbuilt data-sources. The first one, org.springframework.jdbc.datasource.DriverManagerDataSource returns a new connection every time the application requests a new connection. I configured a bean using the below XML:

<bean id="newConnectionDataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <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" />
</bean>

I decided to test the above code

private static void testDataSource(final DataSource dataSource) throws SQLException {
    for (int i =1; i<=30; i++) {
        Connection connection = dataSource.getConnection();
        System.out.println("Connection obtained at request no " + i + " is " + connection);            
        connection.close();
    }
}

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

The code simply retrieves the data-source and opens several connections. The output is as below:

The dataSource is created as org.springframework.jdbc.datasource.DriverManagerDataSource@872380
Connection obtained at request no 1 is com.mysql.jdbc.Connection@80fa6f
Connection obtained at request no 2 is com.mysql.jdbc.Connection@1f26605
...
Connection obtained at request no 28 is com.mysql.jdbc.Connection@d1fa5
Connection obtained at request no 29 is com.mysql.jdbc.Connection@aa37a6
Connection obtained at request no 30 is com.mysql.jdbc.Connection@76e369

As can be seen every request returned a new connection.

The other data-source provided by Spring is the org.springframework.jdbc.datasource.SingleConnectionDataSource. This data-source creates only one connection to the database in its lifetime and always returns the same connection for any request. The bean configuration is as below:

<bean id="singleConnectionDataSource"
    class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
    <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" />
</bean>

I executed the same main method to test the above bean and the output is as below:

The dataSource is created as org.springframework.jdbc.datasource.SingleConnectionDataSource@95c083
Exception in thread "main" Connection obtained at request no 1 is com.mysql.jdbc.Connection@166aa18
java.sql.SQLException: Connection was closed in SingleConnectionDataSource. Check that user code checks 

shouldClose() before closing Connections, or set 'suppressClose' to 'true'
    at org.springframework.jdbc.datasource.SingleConnectionDataSource.

getConnection(SingleConnectionDataSource.java:189)
    at com.test.TestDataSource.testDataSource(TestDataSource.java:19)
    at com.test.TestDataSource.main(TestDataSource.java:46)

As its is a single connection based class, we need to add the above fix to the same

public static void main(String[] args) throws SQLException {
    final BeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource("spring-jdbc-ds.xml"));        
    DataSource dataSource2 = (DataSource) beanFactory.getBean("singleConnectionDataSource");
    System.out.println("The dataSource is created as " + dataSource2);
    SingleConnectionDataSource singleConnectionDataSource = (SingleConnectionDataSource) dataSource2;
    singleConnectionDataSource.setSuppressClose(true);
    TestDataSource.testDataSource(dataSource2);
}

The code now works fine.