1. 首页
  2. 问题
  3. JdbcTemplate查询关闭数据库连接
一尘不染

JdbcTemplate查询关闭数据库连接

hibernate

我在hibernate状态下使用jpa。我有以下方法：

@Transactional
public void myMethod(){
...
firstJDBCTemplateQuery();
secondJDBCTemplateQuery();
...

}

firstJDBCTemplateQuery可以，但是它关闭了与数据库的连接。当秒secondJDBCTempolateQuery被执行时

java.sql.SQLException: Connection is closed exception

引发什么原因

org.springframework.transaction.TransactionSystemException: Could not roll back JPA transaction ...

我的配置： 编辑

     <bean id="dataSource"
            class="org.apache.commons.dbcp.BasicDataSource">
            <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>


        <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
            <property name="entityManagerFactory" ref="emf" />

        </bean>

        <tx:annotation-driven transaction-manager="transactionManager" />
<bean id="emf"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
        </property>
        <property name="packagesToScan" value="com.emisoft.ami.user.domain" />

        <property name="jpaProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
                <prop key="hibernate.max_fetch_depth">3</prop>
                <prop key="hibernate.jdbc.fetch_size">50</prop>
                <prop key="hibernate.jdbc.batch_size">10</prop>
                <prop key="hibernate.show_sql">false</prop>
            </props>
        </property>

    </bean>


    <jpa:repositories base-package="com.emisoft.ami.user.repository"
        entity-manager-factory-ref="emf" transaction-manager-ref="transactionManager" />
        ...

我不知道为什么“ firstJDBCTemplateQuery”关闭数据库连接。如何解决这个问题？

堆栈跟踪：

    org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is java.sql.SQLException: Connection is closed.
        at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:296)
        at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:320)
        at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:214)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:140)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:103)
        at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:676)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:731)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:747)
        at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:782)
        at org.springframework.security.provisioning.JdbcUserDetailsManager.findGroupId(JdbcUserDetailsManager.java:373)
        at org.springframework.security.provisioning.JdbcUserDetailsManager.addUserToGroup(JdbcUserDetailsManager.java:301)
//////////////////////////////////////////////////This is secondJDBCTemplateQuery///////////
        at com.emisoft.ami.user.service.impl.UserServiceImpl.insert(UserServiceImpl.java:42)
///////////////////////////////////////////////////////////////////////////////////////////
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy46.insert(Unknown Source)
        at com.kulig.test.service.PaymentServiceContext.main(PaymentServiceContext.java:28)
    Caused by: java.sql.SQLException: Connection is closed.
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.checkOpen(PoolingDataSource.java:185)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.getMetaData(PoolingDataSource.java:244)
        at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:285)
        ... 29 more
    DEBUG: org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - Unable to translate SQLException with Error code '0', will now try the fallback translator
    DEBUG: org.springframework.orm.jpa.JpaTransactionManager - Initiating transaction rollback
    DEBUG: org.springframework.orm.jpa.JpaTransactionManager - Rolling back JPA transaction on EntityManager [org.hibernate.ejb.EntityManagerImpl@76c741]
    DEBUG: org.hibernate.engine.transaction.spi.AbstractTransactionImpl - rolling back
    DEBUG: org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction - re-enabling autocommit
    DEBUG: org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction - Could not toggle autocommit
    java.sql.SQLException: Connection is closed.
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.checkOpen(PoolingDataSource.java:185)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:327)
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.releaseManagedConnection(JdbcTransaction.java:127)
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:170)
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:209)
        at org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:106)
        at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:539)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:846)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:823)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:493)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:264)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy46.insert(Unknown Source)
        at com.kulig.test.service.PaymentServiceContext.main(PaymentServiceContext.java:28)
    DEBUG: org.springframework.orm.jpa.JpaTransactionManager - Closing JPA EntityManager [org.hibernate.ejb.EntityManagerImpl@76c741] after transaction
    DEBUG: org.hibernate.engine.jdbc.internal.LogicalConnectionImpl - Releasing JDBC connection
    DEBUG: org.hibernate.engine.jdbc.internal.LogicalConnectionImpl - Released JDBC connection
    ERROR: org.springframework.transaction.interceptor.TransactionInterceptor - Application exception overridden by rollback exception
    org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select id from groups where group_name = ?]; SQL state [null]; error code [0]; Connection is closed.; nested exception is java.sql.SQLException: Connection is closed.
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:676)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:731)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:747)
        at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:782)
        at org.springframework.security.provisioning.JdbcUserDetailsManager.findGroupId(JdbcUserDetailsManager.java:373)
        at org.springframework.security.provisioning.JdbcUserDetailsManager.addUserToGroup(JdbcUserDetailsManager.java:301)
        at com.emisoft.ami.user.service.impl.UserServiceImpl.insert(UserServiceImpl.java:42)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy46.insert(Unknown Source)
        at com.kulig.test.service.PaymentServiceContext.main(PaymentServiceContext.java:28)
    Caused by: java.sql.SQLException: Connection is closed.
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.checkOpen(PoolingDataSource.java:185)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:312)
        at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1446)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:583)
        ... 23 more
    Exception in thread "main" org.springframework.transaction.TransactionSystemException: Could not roll back JPA transaction; nested exception is javax.persistence.PersistenceException: unexpected error when rollbacking
        at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:543)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:846)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:823)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:493)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:264)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy46.insert(Unknown Source)
        at com.kulig.test.service.PaymentServiceContext.main(PaymentServiceContext.java:28)
    Caused by: javax.persistence.PersistenceException: unexpected error when rollbacking
        at org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:109)
        at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:539)
        ... 9 more
    Caused by: org.hibernate.TransactionException: rollback failed
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:215)
        at org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:106)
        ... 10 more
    Caused by: org.hibernate.TransactionException: unable to rollback against JDBC connection
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:167)
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:209)
        ... 11 more
    Caused by: java.sql.SQLException: Connection is closed.
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.checkOpen(PoolingDataSource.java:185)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:322)
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:163)
        ... 12 more

编辑 我检查secondJDBCTemplateQuery了堆栈跟踪。

编辑

我用 org.springframework.security.provisioning.JdbcUserDetailsManager

firstJDBCTemplateQuery 是 createUser(UserDetails user)

secondJDBCTemplateQuery 是 addUserToGroup(String username, String groupName)

public void createUser(final UserDetails user) {
        validateUserDetails(user);
        getJdbcTemplate().update(createUserSql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getPassword());
                ps.setBoolean(3, user.isEnabled());
            }

        });

        if (getEnableAuthorities()) {
            insertUserAuthorities(user);
        }
    }


public void addUserToGroup(final String username, final String groupName) {
        logger.debug("Adding user '" + username + "' to group '" + groupName + "'");
        Assert.hasText(username);
        Assert.hasText(groupName);

        final int id = findGroupId(groupName);
        getJdbcTemplate().update(insertGroupMemberSql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, id);
                ps.setString(2, username);
            }
        });

        userCache.removeUserFromCache(username);
    }

编辑调试结果 ：

启动Beginin交易myMethod()：

DEBUG: org.springframework.orm.jpa.JpaTransactionManager - Creating new transaction with name [com.emisoft.ami.user.service.impl.UserServiceImpl.insert]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
DEBUG: org.springframework.orm.jpa.JpaTransactionManager - Opened new EntityManager [org.hibernate.ejb.EntityManagerImpl@b18ac9] for JPA transaction
DEBUG: org.hibernate.engine.transaction.spi.AbstractTransactionImpl - begin
DEBUG: org.hibernate.engine.jdbc.internal.LogicalConnectionImpl - Obtaining JDBC connection
DEBUG: org.hibernate.engine.jdbc.internal.LogicalConnectionImpl - Obtained JDBC connection
DEBUG: org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction - initial autocommit status: true
DEBUG: org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction - disabling autocommit
DEBUG: org.springframework.orm.jpa.JpaTransactionManager - Exposing JPA transaction as JDBC transaction [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@940dc4]

//////////////////////////////// firstJDBCTemplateMethod：：///////////////
///////////////////

DEBUG: org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL update
DEBUG: org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [insert into users (username, password, enabled) values (?,?,?)]
DEBUG: org.springframework.jdbc.core.JdbcTemplate - SQL update affected 1 rows

//////////////////////////// secondJDBCTemplateMethod：////////
/////////////////////////////

DEBUG: org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
DEBUG: org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select id from groups where group_name = ?]
INFO : org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
INFO : org.springframework.jdbc.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
DEBUG: org.springframework.jdbc.support.SQLErrorCodesFactory - Looking up default SQLErrorCodes for DataSource [org.apache.commons.dbcp.BasicDataSource@150f6f]
WARN : org.springframework.jdbc.support.SQLErrorCodesFactory - Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is java.sql.SQLException: Connection is closed. ///This is the beginning of stacktrace which is located above.

编辑 PaymentServiceContext：

public class PaymentServiceContext {
    public static void main(String[] args) {
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext(
                "com/kulig/test/service/PaymentServiceTest-context.xml");

        UserService userService = context.getBean(UserService.class);
        ///CREATE POJO OBJECTS credentials and p
                ...
        userService.insert(credentials, p);

    }

}

阅读 355

收藏
2020-06-20

共1个答案

一尘不染

我认为hibernate中存在错误。我变了

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>4.2.5.Final</version>
</dependency>

至

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>4.1.12.Final</version>
</dependency>

而且有效。

2020-06-20