Spring-batch+JPA+Hibernate+c3p0 not working

[Mohanraj Naidu]

I am trying out spring-batch with JPA+Hibernate and c3p0. Standalone example is working fine but this combination is giving problem while executing the test case. Below is my spring-configuration.xml

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${database.driver}" />
    <property name="jdbcUrl" value="${database.url}" />
    <property name="user" value="${database.user}" />
    <property name="password" value="${database.password}" />

    <property name="acquireIncrement" value="${acquireIncrement}" />
    <property name="acquireRetryAttempts" value="${acquireRetryAttempts}" />
    <property name="acquireRetryDelay" value="${acquireRetryDelay}" />
    <property name="autoCommitOnClose" value="${autoCommitOnClose}" />
    <property name="dataSourceName" value="${dataSourceName}" />
    <property name="idleConnectionTestPeriod" value="${idleConnectionTestPeriod}" />
    <property name="initialPoolSize" value="${initialPoolSize}" />
    <property name="minPoolSize" value="${minPoolSize}" />
    <property name="maxPoolSize" value="${maxPoolSize}" />
    <property name="maxIdleTime" value="${maxIdleTime}" />
    <property name="maxStatements" value="${maxStatements}" />
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="com.example.cns.*" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    </property>
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">validate</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.temp.use_jdbc_metadata_defaults">false</prop>
            <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
            <prop key="hibernate.cache.use_query_cache">true</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.connection.isolation">2</prop>
        </props>
    </property>
</bean>

<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" /> 

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
    </property>
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="jpaTemplate" class="org.springframework.orm.jpa.JpaTemplate">
    <constructor-arg>
        <ref bean="entityManagerFactory" />
    </constructor-arg>
</bean>


    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" p:dataSource-ref="dataSource" />

<bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="transactionManager" ref="transactionManager"/>
    <property name="databaseType" value="mysql"/>
    <property name="tablePrefix" value="BATCH_"/>
    <property name="isolationLevelForCreate" value="ISOLATION_DEFAULT"/>
</bean>

<bean id="jobLauncher" class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
    <property name="jobRepository" ref="jobRepository"/>
</bean>

<bean id="jobExplorer" class="org.springframework.batch.core.explore.support.JobExplorerFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="tablePrefix" value="BATCH_"/>
</bean>

<bean id="jobOperator" class="org.springframework.batch.core.launch.support.SimpleJobOperator">
    <property name="jobRepository" ref="jobRepository"></property>
    <property name="jobRegistry" ref="jobRegistry"></property>
    <property name="jobExplorer" ref="jobExplorer"></property>
    <property name="jobLauncher" ref="jobLauncher"></property>
</bean>

<bean id="jobRegistry" class="org.springframework.batch.core.configuration.support.MapJobRegistry"/>

<bean class="org.springframework.batch.core.configuration.support.JobRegistryBeanPostProcessor">
    <property name="jobRegistry" ref="jobRegistry"/>
</bean>

<bean id="calculateEventMetricsTask" class="com.example.cns.tasks.CalculateEventMetricsTask"/>
<bean id="calculateOnlineMetricsTask" class="com.example.cns.tasks.CalculateOnlineMetricsTask"/>
<bean id="longRunningBatchTask" class="com.example.cns.tasks.LongRunningBatchTask"/>

<batch:job id="calculateEventMetricsJob">
    <batch:step id="calculateEventMetrics">
        <tasklet ref="calculateEventMetricsTask"/>
    </batch:step>
</batch:job>

<batch:job id="calculateOnlineMetricsJob">
    <batch:step id="calculateOnlineMetrics">
        <tasklet ref="calculateOnlineMetricsTask"/>
    </batch:step>
</batch:job>

<batch:job id="longRunningBatchJob">
    <batch:step id="longRunningBatchStep">
        <tasklet ref="longRunningBatchTask"/>
    </batch:step>
</batch:job>

I am invoking the job as below from my service class

@Autowired private JobLauncher jobLauncher;

@Autowired JobRegistry jobRegistry;

JobExecution execution = jobLauncher.run(jobRegistry.getJob("calculateEventMetricsJob"), new JobParameters(parameters));

On execution of test case I am getting below exception

11:10:01.229 INFO [Grizzly-9998(1)] com.example.cns
[CustomerServiceImpl.java:170] - Job FlowJob : [name=calculateEventMetricsJob] org.springframework.orm.hibernate3.HibernateSystemException: commit failed; nested exception is org.hibernate.Transactio nException: commit failed at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.ja va:690) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java: 104) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:516) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTran sactionManager.java:754) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransaction Manager.java:723) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(Transact ionAspectSupport.java:394) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at com.sun.proxy.$Proxy64.getLastJobExecution(Unknown Source) at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:94) at com.example.cns.customer.service.impl.CustomerServiceImpl.executeBatchJob(CustomerServiceImpl.java:172) 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:307) 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.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at com.sun.proxy.$Proxy78.executeBatchJob(Unknown Source) at com.example.cns.customer.resource.CustomersResource.getAllCustomers(CustomersResource.java:310) 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 com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._d ispatch(AbstractResourceMethodDispatchProvider.java:205) at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDisp atcher.java:75) at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381) at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.doFilter(ServletAdapter.java:1059) at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.invokeFilterChain(ServletAdapter.java:999) at com.sun.grizzly.http.servlet.ServletAdapter.doService(ServletAdapter.java:434) at com.sun.grizzly.http.servlet.ServletAdapter.service(ServletAdapter.java:379) at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179) at com.sun.grizzly.tcp.http11.GrizzlyAdapterChain.service(GrizzlyAdapterChain.java:196) at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179) at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849) at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746) at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045) at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228) at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104) at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90) at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79) at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54) at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59) at com.sun.grizzly.ContextTask.run(ContextTask.java:71) at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532) at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513) at java.lang.Thread.run(Thread.java:724) Caused by: org.hibernate.TransactionException: commit failed at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:185) at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:75) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:512) ... 61 more Caused by: org.hibernate.TransactionException: unable to commit against JDBC connection at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doCommit(JdbcTransaction.java:116) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:178) ... 63 more Caused by: java.sql.SQLException: You can't operate on a closed Connection!!! at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106) at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65) at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:809) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doCommit(JdbcTransaction.java:112) ... 64 more Caused by: java.lang.NullPointerException at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:803) ... 65 more

I am not sure where I am going wrong. Any help in resolving this is appreciated.

Thanks
Mohan

[Jeanne Boyarsky]

It's hard to say without seeing your database code. Can you show the class you wrote that uses JdbcTemplate?