3

I get a TransactionRequiredException when trying to run update query using jpql but if I do an update using spring data provided save() method, it works fine.

My Test method:

service.save(object); --- This Update works- calls spring data save()
service.updateTable1gBycolumnpk(object);-- This update throws exception

My service method:

    @Transactional
    public int updateTable1gBycolumnpk(Table1Object object){
        return repo.updateTable1gBycolumnpk(object.getcolumnpk(), object.getname());
    }

My Repository method:

    @Modifying(clearAutomatically = true)
    @Query("UPDATE Table1 a SET a.columnpk = :columnpk WHERE a.name = :name")
    public int updateTable1gBycolumnpk(@Param("columnpk") String columnpk, @Param("name") long name);

EDIT: Spring configuration

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context" 
    xmlns:tx="http://www.springframework.org/schema/tx" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:jpa="http://www.springframework.org/schema/data/jpa"
    xmlns:repository="http://www.springframework.org/schema/data/repository"
    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
        http://www.springframework.org/schema/data/jpa
        http://www.springframework.org/schema/data/jpa/spring-jpa.xsd
        http://www.springframework.org/schema/data/repository
        http://www.springframework.org/schema/data/repository/spring-repository.xsd
        ">

    <!-- Scans the classpath for annotated components that will be auto-registered as Spring beans -->
    <context:component-scan base-package="org" />
    <!-- Activates various annotations to be detected in bean classes e.g: @Autowired -->
    <context:annotation-config />

    <!-- Start Test Configurations -->
    <bean id="testDataSource1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:ABC" />
        <property name="username" value="sys as sysdba" />
        <property name="password" value="SYS2013" />
    </bean>

    <bean id="entityManagerFactory" 
            class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
            p:packagesToScan="org.schema1.entities"
            p:dataSource-ref="testDataSource1"
            >
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="generateDdl" value="true" />
                <property name="showSql" value="true" />

            </bean>
        </property>
        <property name="jpaProperties">
               <props>
                    <prop key="hibernate.hbm2ddl.auto">none</prop>
                    <prop key="hibernate.default_schema">schema1</prop>
                    <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
               </props>
        </property>
    </bean>
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" >
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>
    <jpa:repositories base-package="org.schema1.repositories" entity-manager-factory-ref="entityManagerFactory" transaction-manager-ref="transactionManager"/> 





    <bean id="testDataSource2" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:DEF" />
        <property name="username" value="sys as sysdba" />
        <property name="password" value="SYS2013" />
    </bean>

    <!-- Entity Manager Factory -->
    <bean id="schema2EntityManagerFactory" 
            class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
            p:packagesToScan="org.schema2.entities"
            p:dataSource-ref="testDataSource2"
            >
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="generateDdl" value="true" />
                <property name="showSql" value="true" />
            </bean>
        </property>
        <property name="jpaProperties">
               <props>
                    <prop key="hibernate.hbm2ddl.auto">none</prop>
                    <prop key="hibernate.default_schema">schema2</prop>
                    <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
               </props>
        </property>
    </bean>


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

    <jpa:repositories base-package="org.schema2.repositories" entity-manager-factory-ref="schema2EntityManagerFactory" transaction-manager-ref="schema2TransactionManager"/>

    <!-- enable the configuration of transactional behavior based on annotations -->
    <tx:annotation-driven />


</beans>

Stacktrace:

Exception in thread "main" org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:413)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:131)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy40.updateTable1gBycolumnpk(Unknown Source)
    at org.frb.stls.ginvoicing.apputils.processor.services.dag.DagShdServiceImpl.updateDagShdBycolumnpk(DagShdServiceImpl.java:51)
    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:333)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy42.updateDagShdBycolumnpk(Unknown Source)
    at test.Test.processorSchematest(Test.java:97)
    at test.Test.main(Test.java:54)
Caused by: javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:71)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:238)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:100)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:91)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:462)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:440)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 23 more
Nero
  • 422
  • 8
  • 22
  • Yes, the JPA spec requires that any update to data has to be within a transaction. – Neil Stockton Nov 29 '16 at 15:56
  • @NeilStockton I do have Transactional annotation in my service method – Nero Nov 29 '16 at 15:57
  • 2
    Try adding `@Transactional` in the repository. Reason `save` method works is because it has `@Transactional` in repository. – Raghav Nov 29 '16 at 15:57
  • @iNan Adding the Transactional on the repository method seems to have worked. But do you know why the Transactional annotation on the service method didn't work? – Nero Nov 29 '16 at 16:04
  • @Nero There might be some issue with your configuration. If you could update with more details I can see what is happening – Raghav Nov 29 '16 at 16:11
  • @iNan I have added my spring configuration file. I have 2 datasources defined and I am running the update on the second datasource(testDataSource2). Thank you – Nero Nov 29 '16 at 16:35
  • @Nero Are you using interface for your service? – Raghav Nov 29 '16 at 16:42
  • @iNan yes I am using an interface – Nero Nov 29 '16 at 16:44
  • 1
    @Nero did you find an answer, I'm facing the same. – newday Sep 11 '17 at 16:35
  • You can use JPA Entity Manager to start transaction on your own. [Here is my extended answer in a parallel thread](https://stackoverflow.com/a/48956029/447672) – adlerer Feb 23 '18 at 21:28

0 Answers0