How to manually start a transaction on a shared EntityManager in Spring?

I have a `LocalContainerEntityManagerFactoryBean` as `EntityManager` instance.

To quickly drop a full tables' content, I want to run the following code:

@Service
public class DatabaseService {
@Autowired
private EntityManager em;

@Transactional
public void clear() {
em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate();
}
}

Result:

ERROR org.springframework.integration.handler.LoggingHandler: javax.persistence.TransactionRequiredException: Executing an update/delete query
at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:71)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
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 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.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)



If I make this change:

public void clear() {
em.getTransaction().begin();
em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate();
}

Result:

ERROR org.springframework.integration.handler.LoggingHandler: java.lang.IllegalStateException: Not allowed to create transaction on shared EntityManager - use Spring transactions or EJB CMT instead
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:245)
at com.sun.proxy.$Proxy84.getTransaction(Unknown Source)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
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 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.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)


I also tried spring-data-jpa, but also fails:

public interface MyRepository extends CrudRepository<MyEntity, Integer> {
@Query(value = "TRUNCATE TABLE MyTable", nativeQuery = true)
@Modifying
public void clear();
}

So, how can I create a transaction and run the truncate in a shared spring context?

The Spring application is started using:
`SpringApplication.run(AppConfig.class, args);` having:

@Bean
public JpaTransactionManager transactionManager() {
return new JpaTransactionManager(emf);
}

Spring Data JPA automatically runs CRUD method in transactions for you (without needing to set up anything except a transaction manager). If you want to use transactions for your query methods, you can simply add `@Transactional` to these:

interface MyRepository extends CrudRepository<MyEntity, Integer> {

@Transactional
@Modifying
@Query(value = "TRUNCATE TABLE MyTable", nativeQuery = true)
void clear();
}

On a more general note, what you have declared here is logically equivalent to `CrudRepository.deleteAll()`, except that it (your declaration) doesn't honor JPA-level cascades. So I wondered that's really what you intended to do. If you're using Spring Boot, the activation and transaction manager setup should be taken care of for you.

If you want to use `@Transactional` on the service level, you need to setup both a `JpaTransactionManager` *and* activate annotation based transaction management through either `<tx:annotation-driven />` or `@EnableTransactionManagement` (looks like the activation was the missing piece on your attempt to create transactions on the service layer).

`@Transactional` annotation should not be applied on Dao method but on a service method. Although your code says `DatabaseService` is a service, but by inserting `EntityManger` inside a service does not make any sense.

Correct way to implement is to create a Dao like below.

@Repository
public class DatabaseDao {
@PersistenceContext
private EntityManager em;

public void clear() {
em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate();
}
}

Then call the dao method from a service method with `@Transactional` annotation.

@Service
public class DatabaseService {
@Autowired
private DatabaseDao dao;

@Transactional
public void clear() {
dao.clear();
}
}

Also, add `@EnableTransactionManagement` in your `Configuration` class

As a workaround I now created a new `EntityManager` explicit using the `EMF`, and starting the transaction manually.

@Autowired
private EntityManagerFactory emf;

public void clearTable() {
EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate();
tx.commit();
em.close();
}

That's probably not ideal, but works for the moment.

You should use `TransactionTemplate` object to manage transaction imperatively:

transactionTemplate.execute(
status -> em.createNativeQuery("TRUNCATE TABLE MyTable").executeUpdate());

To create `TransactionTemplate` just use injected `PlatformTransactionManager`:

transactionTemplate = new TransactionTemplate(platformTransactionManager);

And if you want to use new transaction just invoke

transactionTemplate.setPropagationBehavior(
TransactionDefinition.PROPAGATION_REQUIRES_NEW);