Java 7 try-with-resources using spring transactions results in connection closed when committing

Question

I am trying to upgrade some existing Java 6 code into Java 7 and I am facing an issue with the new try-with-resources syntax when dealing with JDBC connections that are managed by a spring transaction. The database connections are managed by Oracle UCP and are retrieved with the DataSourceUtils class provided by Spring. However, in order to create a simple example for this question, I am using HSQLDB 2.3.2 and Apache commons-dbcp 2.0.1. The same issue occurs with both setups. The exception occurs when the transaction is being committed by the Spring TransactionInterceptor:

org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException: Connection is null.
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:272)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:496)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
    at com.creaseol.dao.SampleDao$$EnhancerBySpringCGLIB$$45ec8efa.putWithTryWithResources(<generated>)
    at com.creaseol.TestTryCatch.testWithResources(TestTryCatch.java:69)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:72)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:81)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:216)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:82)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:60)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:67)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:162)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLException: Connection is null.
    at org.apache.commons.dbcp2.DelegatingConnection.checkOpen(DelegatingConnection.java:608)
    at org.apache.commons.dbcp2.DelegatingConnection.commit(DelegatingConnection.java:362)
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:269)
    ... 40 more

I have created a simple Maven project to show the issue. The four files required are below. I have a DAO that has the following two methods annotated with @Transactional:

protected Connection getConnection() {
    return DataSourceUtils.getConnection(m_ds);
}

@Transactional
public void putWithNormalTryCatch(int id) {
    Connection conn = null;
    CallableStatement statement = null;
    try {
        conn = getConnection();
        statement = conn.prepareCall(String.format("INSERT INTO test VALUES (%s)", id));
        statement.execute();
    }
    catch (SQLException sql) {
        throw new RuntimeException("failed during insert",sql);
    }
    finally {
        if (statement != null){
            try {
                statement.close();
            }
            catch (SQLException sqle) {
                System.err.println("Failed to close statement after execution. "+ sqle.getMessage());
            }
        }
        if (conn != null) {
            DataSourceUtils.releaseConnection(conn, m_ds);
        }
    }
}

@Transactional
public void putWithTryWithResources(int id) {
    try (Connection conn = getConnection();
         CallableStatement statement = conn.prepareCall(String.format("INSERT INTO test VALUES (%s)", id)))
    {
        statement.execute();
    }
    catch (SQLException sql) {
        throw new RuntimeException("failed during insert",sql);
    }
}

The first method putWithNormalTryCatch uses the old style try catch block and the putWithTryWithResources uses the Java 7 try-with-resources. Note that the connection is retrieved using DataSourceUtils.getConnection and it is released using DataSourceUtils.releaseConnection.

I believe the problem is that the try-with-resources is actually calling close on the connection, rather than releasing it to the pool. What you really want is that call to close() on the connection calls DataSourceUtils.releaseConnection.

I could simply wrap the DataSource with in a TransactionAwareDataSourceProxy, but according to the docs this should not be necessary. Is there a better approach?

---

Full Code

DAO

package com.creaseol.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.transaction.annotation.Transactional;

public class SampleDao {

    private final DataSource m_ds;

    public SampleDao(DataSource ds) {
        m_ds = ds;
    }

    protected Connection getConnection() {
        return DataSourceUtils.getConnection(m_ds);
    }

    @Transactional
    public void putWithNormalTryCatch(int id) {
        Connection conn = null;
        CallableStatement statement = null;
        try {
            conn = getConnection();
            statement = conn.prepareCall(String.format("INSERT INTO test VALUES (%s)", id));
            statement.execute();
        }
        catch (SQLException sql) {
            throw new RuntimeException("failed during insert",sql);
        }
        finally {
            if (statement != null){
                try {
                    statement.close();
                }
                catch (SQLException sqle) {
                    System.err.println("Failed to close statement after execution. "+ sqle.getMessage());
                }
            }
            if (conn != null) {
                DataSourceUtils.releaseConnection(conn, m_ds);
            }
        }
    }

    @Transactional
    public void putWithTryWithResources(int id) {
        try (Connection conn = getConnection();
             CallableStatement statement = conn.prepareCall(String.format("INSERT INTO test VALUES (%s)", id)))
        {
            statement.execute();
        }
        catch (SQLException sql) {
            throw new RuntimeException("failed during insert",sql);
        }
    }

    public List<Integer> getValues() {
        try (Connection conn = getConnection();
             CallableStatement statement = conn.prepareCall("SELECT id FROM test"))
        {
            statement.execute();
            try (ResultSet rs = statement.getResultSet()) {
                List<Integer> values = new ArrayList<Integer>();
                while(rs.next()){
                    values.add(rs.getInt(1));
                }
                return values;
            }
        }
        catch (SQLException sql) {
            throw new RuntimeException("failed during insert",sql);
        }
    }
}

Spring context

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="  
            http://www.springframework.org/schema/beans        
            http://www.springframework.org/schema/beans/spring-beans.xsd   
            http://www.springframework.org/schema/tx    
            http://www.springframework.org/schema/tx/spring-tx.xsd">

    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver" />
        <property name="url" value="jdbc:hsqldb:mem:testdb" />
    </bean>

    <bean id="sampleDao" class="com.creaseol.dao.SampleDao">
        <constructor-arg ref="dataSource"/>
    </bean> 

    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>
    <tx:annotation-driven transaction-manager="transactionManager" />
</beans>

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.creaseol</groupId>
    <artifactId>SpringDBJava7Test</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <properties>
        <spring.version>4.1.1.RELEASE</spring.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!-- TEST DEPENDENCIES -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <!-- our in-memory database provider -->
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.3.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.7</source>
                    <target>1.7</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Test Class

package com.creaseol;

import static org.junit.Assert.assertEquals;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.creaseol.dao.SampleDao;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:test-context.xml")
public class TestTryCatch {

    @Autowired
    private DataSource dataSource;

    @Autowired
    protected SampleDao m_dao;

    protected Connection getConnection(){
        return DataSourceUtils.getConnection(dataSource);
    }

    @Before
    public void setup() throws SQLException{
        Connection conn = getConnection();
        CallableStatement statement = conn.prepareCall("CREATE TABLE test (id INTEGER)");
        statement.execute();
        statement.close();
        close(conn);
    }

    protected void close(Connection conn) {
        if (conn != null){
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }

    @After
    public void tearDown() throws SQLException {
        Connection conn = getConnection();
        CallableStatement statement = conn.prepareCall("DROP TABLE test");
        statement.execute();
        statement.close();
        close(conn);
    }

    @Test
    public void testVanilla(){
        m_dao.putWithNormalTryCatch(42);
        assertEquals(1,m_dao.getValues().size());
        assertEquals(42,(int)m_dao.getValues().get(0));
    }

    @Test
    public void testWithResources(){
        m_dao.putWithTryWithResources(100);
        assertEquals(1,m_dao.getValues().size());
        assertEquals(100,(int)m_dao.getValues().get(0));
    }
}

Answer 1

If you have some resource that you want to use with a try-with-resources statement, but it either isn't AutoCloseable (say, a java.util.concurrent.locks.ReentrantLock that you want to be unlocked at the end of the block), or you don't want to close but rather to perform some other operation on as in the case of your database connection that you want to release back to the pool, you can wrap it in a wrapper that calls whatever operation you want to have performed instead of close().

public class ReleasableConnection implements AutoCloseable {
    private final Connection connection;
    private final DataSource dataSource;
    private boolean released;

    public ReleaseableConnection(Connection connection, DataSource dataSource) {
        this.connection = connection;
        this.dataSource = dataSource;
        released = false;
    }

    public Connection getConnection() {
        return connection;
    }

    @Override
    public void close() {
        if (!released) {
            DataSourceUtils.releaseConnection(connection, dataSource);
            released = true; // make it idempotent
        }
    }
}

Then you can use it in a try-with-resources block as follows:

try (ReleasableConnection rc = new ReleasableConnection(getConnection(), m_ds)) {
    Connection conn = rc.getConnection();
    // do something with the connection
    // it will be released automatically
}