小贝子编程

Java 7尝试使用spring事务处理资源会导致提交时连接关闭



我正试图将一些现有的Java 6代码升级到Java 7中，在处理由spring事务管理的JDBC连接时，我面临着新的try with resources语法的问题。数据库连接由Oracle UCP管理，并使用Spring提供的DataSourceUtils类进行检索。然而，为了为这个问题创建一个简单的示例，我使用了HSQLDB 2.3.2和Apache commons dbcp 2.0.1。两种设置都会出现相同的问题。当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

我创建了一个简单的Maven项目来展示这个问题。所需的四个文件如下。我有一个DAO，它有以下两个用@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);
    }
}

第一种方法putWithNormalTryCatch使用老式的try-catch块，putWithTryWithResources使用Java 7 try with resources。请注意，使用DataSourceUtils.getConnection来检索连接，并使用DataSourceUtils.releaseConnection来释放连接。

我认为问题在于，使用资源的尝试实际上是在调用关闭连接，而不是将其释放到池中。您真正想要的是连接上对close()的调用DataSourceUtils.releaseConnection。

我可以简单地将DataSource封装在TransactionAwareDataSourceProxy中，但根据文档，这应该不是必要的。有更好的方法吗？

完整代码

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上下文

<?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>

测试等级

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));
    }
}

如果您有一些资源要与try-with-resources语句一起使用，但它不是AutoCloseable（例如，您想在块末尾解锁的java.util.concurrent.locks.ReentrantLock），或者您不想关闭，而是想对其执行一些其他操作，例如在您想释放回池的数据库连接的情况下，您可以将它包装在一个包装器中，该包装器调用您想要执行的任何操作，而不是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
        }
    }
}

然后你可以在资源块中使用它，如下所示：

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

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium