Managing database connections

Using DataSource

Spring obtains a database connection through DataSource. DataSource is part of the JDBC specification and is a generic connection factory. It allows the container or framework to hide the problems of connection pooling and transaction management from the application code. As a developer, you don't need to know in detail about how to connect to a database. This is the responsibility of the administrator who sets up the data source. You likely play both roles as you develop and test code, but you don't necessarily need to know how the production data source is configured.

If you're using the JDBC layer in Spring, you can get the data source from JNDI or configure your own using a connection pool implementation provided by a third party. Traditional options are Apache Commons DBCP and C3P0 with bean-based DataSource classes; for a modern JDBC connection pool, consider HikariCP with its designer-based API.

The next section uses the DriverManagerDataSource implementation from Spring. Several other DataSource options will be discussed later.

To configure the DriverManagerDataSource:

1. Establish a connection to DriverManagerDataSource the same way you would normally establish a connection for JDBC.

2. Specify the fully qualified JDBC driver class name so that DriverManager can load driver class.

3. Specify the URL, which varies between JDBC drivers. (Refer to your driver documentation for the correct value.)

4. Specify the username and password to connect to the database.

The following example shows how to configure DriverManagerDataSource in Java:

DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");>

val dataSource = DriverManagerDataSource().apply {
    setDriverClassName("org.hsqldb.jdbcDriver")
    url = "jdbc:hsqldb:hsql://localhost:"
    username = "sa"
    password = ""
}

The following example shows the corresponding XML configuration:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>
<context:property-placeholder location="jdbc.properties"/>

The following two examples show basic connection and configuration for DBCP and C3P0. To learn about other options that help you manage pooling functionality, see the product documentation for your respective connection pooling implementation.

The following example shows a DBCP configuration:

<bean id="dataSource" 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}"/>
</bean>
<context:property-placeholder location="jdbc.properties"/>

The following example shows the C3P0 configuration:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${jdbc.driverClassName}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>
<context:property-placeholder location="jdbc.properties"/>

Using DataSourceUtils

The DataSourceUtils class is a convenient and full-featured helper class that provides static methods for establishing connections from JNDI and closing connections when necessary. It supports streaming connections, for example, with DataSourceTransactionManager.

The implementation of SmartDataSource

The SmartDataSource interface should be implemented by classes that can provide a connection to a relational database. It extends the DataSource interface to allow classes that use it to make a request as to whether the connection should be closed after a certain operation. This use is effective if you know that you need to reuse the connection.

The AbstractDataSource extension

AbstractDataSource is abstract base class for DataSource implementations from Spring. It implements code that is common to all DataSource implementations. You should extend the AbstractDataSource class if you write your own DataSource implementation.

Using SingleConnectionDataSource

The SingleConnectionDataSource class is an implementation of the SmartDataSource interface that wraps a single Connection, which does not close after each use. It does not support multithreading.

If any client code calls close under the assumption that there is a connection pool (as when using persistence support tools), you should set the property suppressClose to true. This parameter returns a proxy that suppresses closures that wraps the physical connection. Note that you can no longer convert it to Oracle's own Connection or similar object.

SingleConnectionDataSource is primarily a test class. It generally allows you to easily test code outside of the application server when combined with a simple JNDI environment. Unlike DriverManagerDataSource, it constantly uses the same connection, avoiding excessive creation of physical connections.

Using DriverManagerDataSource

The DriverManagerDataSource class is an implementation of the standard DataSource interface that configures a regular JDBC driver through bean properties and returns a new Connection each time.

This implementation is useful for test and standalone environments outside of a Java EE container, either as a DataSource bean in a Spring IoC container or in combination with a simple JNDI environment. Calls to Connection.close() close the connection, so any persistence code that is compatible with DataSource should work. However, using JavaBean-based connection pools (such as commons-dbcp) is so easy, even in a test environment, that it is almost always preferable to DriverManagerDataSource.

Using TransactionAwareDataSourceProxy

TransactionAwareDataSourceProxy is a proxy for the target DataSource. The proxy wraps the target DataSource to improve compatibility with Spring-managed transactions. In this respect, it is similar to the JNDI transactional DataSource provided by the Java EE server.

See more information javadoc by TransactionAwareDataSourceProxy.

Using DataSourceTransactionManager

The DataSourceTransactionManager class is an implementation of PlatformTransactionManager for individual JDBC data sources. It binds a JDBC connection from the specified data source to the currently running thread, potentially allowing one thread connection per data source.

Application code must obtain the JDBC connection via DataSourceUtils.getConnection(DataSource) instead of the standard DataSource.getConnection in Java EE. It throws unchecked org.springframework.dao exceptions instead of checked SQLExceptions. All framework classes (for example, JdbcTemplate) use this strategy implicitly. If the search strategy is not used with this transaction manager, then it behaves exactly the same as a regular one. Thus, it can be used in any case.

The DataSourceTransactionManager class supports custom isolation levels and timeout values, which are applied as corresponding timeout values for JDBC statement requests. To support the latter option, application code must either use JdbcTemplate or call the DataSourceUtils.applyTransactionTimeout(..) method for each statement generated.

You can use this implementation instead of JtaTransactionManager when using a single resource, since it does not require the container to support JTA. Switching between them is simply a matter of configuration, as long as you adhere to the required connection search pattern. JTA does not support custom-created isolation levels.