Could not open JDBC Connection for transaction

Question

I would like to report a configuration problem that seems strange to me. Here's the application context configuration file I'm using

  <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
           xsi:schemaLocation="http://www.springframework.org/schema/beans
         http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <context:annotation-config/>

<bean id="propertyConfigurer"
      class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="locations">
        <list>
            <value>classpath:jdbc.properties</value>
        </list>
    </property>
</bean>

<bean id="processingStratagyContainer" class="ru.rt.mnp.translator.converter.process.ProcessingStratagyContainer">
    <property name="fileProcessingStratagyMap">
        <map>
            <entry key="Port_Increment" value-ref="portIncrementProcessingStratagy"/>
            <entry key="Return_Increment" value-ref="returnIncrementProcessingStratagy"/>
            <entry key="Port_All" value-ref="portAllProcessingStratagy"/>
        </map>
    </property>
</bean>

<!--�������� ������������������ ������������������ ������������-->
<bean id="portIncrementProcessingStratagy"
      class="ru.rt.mnp.translator.converter.process.PortIncrementProcessingStratagy">
    <property name="rowSizeColumnPosition" value="11"/>
    <property name="columnBindings">
        <map>
            <!--first has index 0-->
            <entry key="1" value="number[java.lang.String]"/>
            <entry key="5" value="old_route[java.lang.String]"/>
            <entry key="6" value="new_route[java.lang.String]"/>
            <entry key="9" value="region_code[java.lang.String]"/>
            <entry key="10" value="port_date[java.util.Date]{yyyy-MM-dd'T'HH:mm:ssXXX}"/>
        </map>
    </property>
</bean>

<bean id="returnIncrementProcessingStratagy"
      class="ru.rt.mnp.translator.converter.process.ReturnIncrementProcessingStratagy">
    <property name="rowSizeColumnPosition" value="9"/>
    <property name="columnBindings">
        <map>
            <!--first has index 0-->
            <entry key="1" value="number[java.lang.String]"/>
            <entry key="5" value="old_route[java.lang.String]"/>
            <entry key="6" value="new_route[java.lang.String]"/>
            <entry key="7" value="region_code[java.lang.String]"/>
            <entry key="8" value="port_date[java.util.Date]{yyyy-MM-dd'T'HH:mm:ssXXX}"/>
        </map>
    </property>
</bean>

<bean id="portAllProcessingStratagy"
      class="ru.rt.mnp.translator.converter.process.PortAllProcessingStratagy">
    <property name="rowSizeColumnPosition" value="6"/>
    <property name="columnBindings">
        <map>
            <!--first has index 0-->
            <entry key="0" value="number[java.lang.String]"/>
            <entry key="3" value="new_route[java.lang.String]"/>
            <entry key="4" value="region_code[java.lang.String]"/>
            <entry key="5" value="port_date[java.util.Date]{yyyy-MM-dd'T'HH:mm:ssXXX}"/>
        </map>
    </property>
</bean>




<bean id="postProcessingStratagyContainer" class="ru.rt.mnp.translator.converter.postprocess.PostProcessingStratagyContainer">
    <property name="postProcessingStratagableMap">
        <map>
            <entry key="Port_Increment" value-ref="mnpTrfPartPostProcessingStratagy"/>
            <entry key="Return_Increment" value-ref="mnpTrfPartPostProcessingStratagy"/>
            <entry key="Port_All" value-ref="mnpTrfFullPostProcessingStratagy"/>
            <entry key="IncrementCounter" value-ref="incrementCounterPostProcessingStratagy"/>
            <entry key="HistoryRequestFull" value-ref="historyRequestFullPostProcessingStratagy"/>
            <entry key="HistoryRequestPart" value-ref="historyRequestPartPostProcessingStratagy"/>
        </map>
    </property>
</bean>


<bean id="mnpTrfPartPostProcessingStratagy"
      class="ru.rt.mnp.translator.converter.postprocess.MnpTrfPartPostProcessingStratagy" >
      <property name="placeInPostProcessingChain" value="1"/>
</bean>

<bean id="mnpTrfFullPostProcessingStratagy"
      class="ru.rt.mnp.translator.converter.postprocess.MnpTrfFullPostProcessingStratagy" >
    <property name="placeInPostProcessingChain" value="2"/>
</bean>

<bean id="incrementCounterPostProcessingStratagy"
      class="ru.rt.mnp.translator.converter.postprocess.IncrementCounterPostProcessingStratagy" >
    <property name="placeInPostProcessingChain" value="9"/>
</bean>

<bean id="historyRequestFullPostProcessingStratagy"
      class="ru.rt.mnp.translator.converter.postprocess.HistoryRequestFullPostProcessingStratagy" >
    <property name="placeInPostProcessingChain" value="4"/>
</bean>

<bean id="historyRequestPartPostProcessingStratagy"
      class="ru.rt.mnp.translator.converter.postprocess.HistoryRequestPartPostProcessingStratagy">
    <property name="placeInPostProcessingChain" value="3"/>
</bean>

<bean id="convertTask" class="ru.rt.mnp.translator.converter.job.ConvertTask"/>
<bean id="testTask" class="ru.rt.mnp.translator.converter.job.TestTask"/>
<bean id="historyRequestTask" class="ru.rt.mnp.translator.converter.job.HistoryRequestTask"/>

**<bean id="dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="${url}"/>
    <property name="username" value="****"/>
    <property name="password" value="****"/>
</bean>**

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

<bean id="mnpHistoryDao" class="ru.rt.mnp.translator.converter.storage.MnpHistoryDao">
    <property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg name="dataSource" ref="dataSource"/>
</bean>

<!-- specifing class and method that is going to be called on a specified
 time basis -->
<bean id="convertJob"
      class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
    <property name="targetObject" ref="convertTask"/>
    <property name="targetMethod" value="execute"/>
</bean>

<bean id="convertTestJob"
      class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
    <property name="targetObject" ref="testTask"/>
    <property name="targetMethod" value="execute"/>
</bean>

<bean id="historyRequestJob"
      class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
    <property name="targetObject" ref="historyRequestTask"/>
    <property name="targetMethod" value="execute"/>
</bean>

<!-- simple trigger specify repeat interval and delay time -->
<bean id="cronTrigger"
      class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
    <property name="jobDetail" ref="convertJob"/>
    <!--<property name="cronExpression" value="0 5 0,2,4,6,8,10,12,14,16,18,20,22 ? * *"/>-->
    <property name="cronExpression" value="0 15,45 * ? * *"/>
    <property name="startDelay" value="1000"/>
</bean>


<bean id="cronTestTrigger"
      class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
    <property name="jobDetail" ref="convertTestJob"/>
    <property name="cronExpression" value="0 0/10 * ? * *"/>
    <property name="startDelay" value="1000"/>
</bean>

<bean id="cronHistoryRequestTrigger"
      class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
    <property name="jobDetail" ref="historyRequestJob"/>
    <property name="cronExpression" value="0/10 * * ? * *"/>
    <property name="startDelay" value="1000"/>
</bean>

<!-- scheduler factory bean to bind,the executing code and time intervals
 together -->
<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
    <property name="configLocation" value="classpath:quartz.properties"/>
    <property name="jobDetails">
        <list>
            <ref bean="convertJob"/>
            <ref bean="convertTestJob"/>
            <ref bean="historyRequestJob"/>
        </list>
    </property>
    <property name="triggers">
        <list>
            <ref bean="cronTrigger"/>
            <ref bean="cronTestTrigger"/>
            <ref bean="cronHistoryRequestTrigger"/>
        </list>
    </property>
</bean>

But when run the converting action, i have this error:

Problems with processing file /***.zip org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Connection was closed in SingleConnectionDataSource. Check that user code checks shouldClose() before closing Connections, or set 'suppressClose' to 'true' at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:241) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:372) at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:417) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:255) 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.$Proxy5.processFile(Unknown Source) at ru.rt.mnp.translator.converter.job.ConvertTask.execute(ConvertTask.java:69) at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273) at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:311) at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113) at org.quartz.core.JobRunShell.run(JobRunShell.java:207) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560) Caused by: java.sql.SQLException: Connection was closed in SingleConnectionDataSource. Check that user code checks shouldClose() before closing Connections, or set 'suppressClose' to 'true' at org.springframework.jdbc.datasource.SingleConnectionDataSource.getConnection(SingleConnectionDataSource.java:189) at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:203)

Can anyone explain me what is the problem ? What should i do to fix this?

Thank you!

The stacktrace gives you the solution... Connection was closed in SingleConnectionDataSource. Check that user code checks shouldClose() before closing Connections, or set 'suppressClose' to 'true' at org.springframework.jdbc.datasource.SingleConnectionDataSource. — M. Deinum
– M. Deinum, Commented Aug 24, 2018 at 8:52
If i configure the SingleConnectionDataSource to prevent the connection from being closed by adding the following code to my DataSource configuration. Then it would looks like: <bean id="dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource"> <property name="driverClassName" value="org.postgresql.Driver"/> <property name="url" value="${url}"/> <property name="username" value="****"/> <property name="password" value="****"/> <property name="suppressClose"> <value>true</value> </property> </bean> — Archi
– Archi, Commented Aug 24, 2018 at 9:27
More or less. But why use the SingleConnectionDataSource instead of a proper connection pool? As that class is, as stated by the javadoc, This is primarily intended for testing.. — M. Deinum
– M. Deinum, Commented Aug 24, 2018 at 9:28
I've got this code from the previous administrator and i'am trying to understand and correct a code. If i replace SingleConnectionDataSource to DriverManagerDataSource without supressClose code seems like: <bean id=”dataSource” class=”org.springframework.jdbc.datasource.DriverManagerDataSource”> <property name=”driverClassName” value=”com.mysql.jdbc.Driver” /> <property name=”username” value="****" /> <property name=”password” value="****" /> <property name=”url” value=”jdbc:mysql://localhost:3306/codippa” /> </bean> But if i'am using a Postgres db? — Archi
– Archi, Commented Aug 24, 2018 at 9:46
DriverManagerDataSource isn't a connection pool. You want a pool of connections which you can reuse. — M. Deinum
– M. Deinum, Commented Aug 24, 2018 at 10:04

akarahman · Accepted Answer · 2021-05-31 04:52:09Z

I had to use SingleConnectionDataSource in a web application using SpringBoot. We were also using an ORM package for all db transactions and I had this same issue even with suppressClose configured to true. It works fine during the first login, but when the connection is left idle for a long time, then the below error occurs.

The error was

java.sql.SQLException: Connection was closed in SingleConnectionDataSource. Check that user code checks shouldClose() before closing Connections, or set 'suppressClose' to 'true'
    at org.springframework.jdbc.datasource.SingleConnectionDataSource.getConnection(SingleConnectionDataSource.java:167)

Finally configuring the data source as a session scoped object resolved it. It was a singleton before, re-configuring it to session scope resolved the issue.

<bean id="dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource"  scope="session">

Collectives™ on Stack Overflow

Could not open JDBC Connection for transaction

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related