I was assigned to set up a project using BoneCP with jOOQ and Spring, but I ran into some difficulties. Performing individual inserts in my MySQL database works fine, but it takes almost 20 minutes with 190,000 objects, so to speed things up I want to use batch inserts 100 at a time. However, this raises the following exception:
org.springframework.transaction.TransactionSystemException: Could not roll back JDBC transaction; nested exception is java.sql.SQLException: Connection is closed! at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:288) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:849) at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:826) at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:496) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:266) 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:644) at com.theshahin.service.YmsLinkDataService$$EnhancerBySpringCGLIB$$b9b6e447.create(<generated>) at com.theshahin.integration.YmsLinkDataServiceTest.foo(YmsLinkDataServiceTest.java:76) 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.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:74) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:232) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89) 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:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71) at org.junit.runners.ParentRunner.run(ParentRunner.java:309) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:175) at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:53) at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:123) at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:104) 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.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164) at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110) at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:175) at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:107) at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:68) Caused by: java.sql.SQLException: Connection is closed! at com.jolbox.bonecp.ConnectionHandle.checkClosed(ConnectionHandle.java:459) at com.jolbox.bonecp.ConnectionHandle.rollback(ConnectionHandle.java:1270) at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:285) ... 44 more
(It may be worth mentioning that this exception is thrown in the very first batch request, so requests were not executed before it). This is my applicationContext.xml, which is based on one of the jOOQ tutorials (you can find it here: http://www.jooq.org/doc/3.3/manual/getting-started/tutorials/jooq-with-spring/ ):
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <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" xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc" 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-3.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd"> <context:component-scan base-package="com.theshahin" /> <context:property-placeholder location="classpath:application.properties" ignore-resource-not-found="false"/> <tx:annotation-driven transaction-manager="transactionManager"/> <bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close"> <property name="driverClass" value="${db.driver}"/> <property name="jdbcUrl" value="${db.url}"/> <property name="username" value="${db.username}"/> <property name="password" value="${db.password}"/> <property name="idleConnectionTestPeriod" value="60"/> <property name="idleMaxAge" value="240"/> <property name="maxConnectionsPerPartition" value="30"/> <property name="minConnectionsPerPartition" value="10"/> <property name="partitionCount" value="3"/> <property name="acquireIncrement" value="5"/> <property name="statementsCacheSize" value="100"/> <property name="releaseHelperThreads" value="3"/> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="transactionAwareDataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy"> <constructor-arg ref="dataSource" /> </bean> <bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider"> <constructor-arg ref="transactionAwareDataSource" /> </bean> <bean id="dsl" class="org.jooq.impl.DefaultDSLContext"> <constructor-arg ref="config" /> </bean> <bean id="jooqToSpringExceptionTransformer" class="com.theshahin.exception.JOOQToSpringExceptionTransformer"/> <bean class="org.jooq.impl.DefaultConfiguration" name="config"> <constructor-arg index="0" ref="connectionProvider" /> <constructor-arg index="1"><null /></constructor-arg> <constructor-arg index="2"><null /></constructor-arg> <constructor-arg index="3"> <list> <bean class="org.jooq.impl.DefaultExecuteListenerProvider"> <constructor-arg index="0" ref="jooqToSpringExceptionTransformer"/> </bean> </list> </constructor-arg> <constructor-arg index="4"><null /></constructor-arg> <constructor-arg index="5"><value type="org.jooq.SQLDialect">${jooq.sql.dialect}</value></constructor-arg> <constructor-arg index="6"><null /></constructor-arg> <constructor-arg index="7"><null /></constructor-arg> </bean>
This is the code used to store records in a MySQL database. (Note: the comment code is the one I use for individual inserts)
@Service public class YmsLinkDataService extends BaseService { @Transactional public void create(List<YmsLinkDataRecord> records) { dsl.batchInsert(records).execute();
Here is a test script from which an error is generated (I know that in fact it is not testing anything at the moment. I will do this as soon as it successfully saves the DB):
@Test public void batchInsert() throws InterruptedException, SQLException { int batchCount = 0; List<YmsLinkDataRecord> batchRecords = Lists.newArrayList(); for (YmsLinkDataRecord ld : ConfigurationToYmsLinkDataRecord.convert( config)) { batchCount++; batchRecords.add(ld); if (batchCount == 100) { ldService.create(batchRecords); batchRecords.clear(); batchCount = 0; } } ldService.create(batchRecords); }
Any help would be greatly appreciated!
source share