Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

JdbcTemplate query close database connection

Tags:

spring

hibernate

jpa

jdbctemplate

I use jpa with hibernate. I have following method:

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

}

firstJDBCTemplateQuery works, but it closes connection to database. When second secondJDBCTempolateQuery is executed

java.sql.SQLException: Connection is closed exception

is thrown what causes

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

My configuration: EDIT 

     <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" />
        ...

I don't know why 'firstJDBCTemplateQuery' close db connection. How to resolve this problem?

StackTrace:

    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

EDIT I checked secondJDBCTemplateQuery in stacktrace.

EDIT

I use org.springframework.security.provisioning.JdbcUserDetailsManager

firstJDBCTemplateQuery is createUser(UserDetails user)

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

EDIT DEBUG RESULT:

Beigin transaction on startup 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.

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

    }

}
like image 365
Mariusz Avatar asked Sep 25 '13 10:09

Mariusz

2 Answers

Actually, I have had that same issue recently...

After debugging trough the Hibernate code, I noticed Hibernate 4 calls HibernateJpaDialect.releaseConnection at some point. Comments before suggest that it's only to release the connection, but not to close it since it's the connection used by the transactional context. However, that releaseConnection method does in fact call JdbcUtils.closeConnection(con). The HibernateJpaDialect class responsible is actually part of the spring framework, not hibernate.

In the end, this issue is reported by Spring as a bug (SPR-10395) and should be fixed in release 3.2.3 or above. So in the end, you can use Hibernate 4.2 but you'll have to upgrade spring (orm) in that case: 

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>3.2.3</version>
</dependency>
like image 78
Kevin Chabot Avatar answered Oct 12 '22 15:10

Kevin Chabot

I think that there is a bug in hibernate. I changed 

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

to 

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

and it works.

like image 24
Mariusz Avatar answered Oct 12 '22 17:10

Mariusz
Sign in to Comment

Related questions

Spring 5 supports Java 9 Flow APIs in its reactive feature?
Can we use @Autowired in a Tasklet in Spring Batch?
Spring WebFlux: Serve files from controller
How to properly throw MethodArgumentNotValidException
Spring Boot 2. Hikari Connection Pool optimization
Spring Data JPA 'jpaMappingContext' error, IllegalStateException: Expected to be able to resolve a type but got null
NoClassDefFoundError ErrorCoded
Setup Spring security to redirect user to login page if not authenticated
@ExceptionHandler doesn't handle the thrown exceptions
ViewScoped Bean cause NotSerializableException
EHCache how to check if something is in the cache or not?
Use one Message as argument in other Spring Messages (properties file)
Is it ok to use DataSourceTransactionManager for ORM persistence instead of HibernateTransactionManager?
How to force disable JSR-303 support in Spring 3?
How to read multiple properties having the same keys in Spring?
Is there a way to give persistenceUnitName for Spring's LocalContainerEntityManagerFactoryBean without persistence.xml?
handling wrapped exceptions in spring mvc
@Indexed annotation ignored when using named collections
Priority between properties in HibernateJPAVendorAdapter and JPAProperty
Making Existing Spring Batch Application run on multiple nodes

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!