CODEWITHSUNDEEP
javasqlhibernatejpajpql
Chris Garsonn
Chris Garsonn

Reputation: 777

How to run custom update query on JpaRepository?(TransactionRequiredException)

I am trying to write 2 native SQL queries in spring,

My query annotation with SQL in service class is:

    @Transactional
    @Scheduled(cron = "0 0/1 * * * *")
    void testMethod() {

    String query = "UPDATE BUDGET_GROUP_SPEND_SUMMARY BSS, USER U, EMPLOYEE_MOVE_STAGE EMS, BUDGET B, SERVICE_GROUP SG " +
                "SET BSS.BULK_RECALC_LOCK = true " +
                "WHERE BSS.EMPLOYEE_ID = U.USER_ID " +
                "AND U.USER_ID = EMS.USER_ID " +
                "AND BSS.BUDGET_GROUP_ID = B.BUDGET_ID " +
                "AND B.BUDGET_SERVICE_GROUP_ID IS NOT NULL " +
                "AND EMS.MOVE_STAGE_ID IN (1,2) " +
                "AND U.USER_CUSTOMER_STATUS IN ('MOVE_COMPLETED', 'LOST', 'GET_FEEDBACK', 'CLOSED_NO_FEEDBACK', 'ON_ASSIGNMENT') " +
                "AND B.BUDGET_SERVICE_GROUP_ID = SG.SERVICE_GROUP_ID " +
                "AND SG.MOVE_STAGE_ID = 1";

        Query nq = entityManager.createNativeQuery(query);

        nq.executeUpdate();
    }

But I received this error:

2019-09-12 18:28:00,012 ERROR [] o.s.s.s.TaskUtils$LoggingErrorHandler:95 - Unexpected error occurred in scheduled task.
javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:54)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

If I use entityManager.getTransaction().begin(); and entityManager.getTransaction().commit();

then its throwing :

2019-09-12 18:36:00,007 ERROR [] o.s.s.s.TaskUtils$LoggingErrorHandler:95 - Unexpected error occurred in scheduled task. java.lang.IllegalStateException: Not allowed to create transaction on shared EntityManager - use Spring transactions or EJB CMT instead at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManager

Why do I get this error?

Or is there any other better way to implement custom update query with Jpa Repository?

Upvotes: 0

Views: 227

Answers (1)

Taras Boychuk
Taras Boychuk

Reputation: 2439

  1. I assume Spring will not create a transaction proxy since your method is not public.
  2. You should always try to avoid manual transaction management if you are using declarative Spring Transaction Manager
  3. Your application should have a separate layer that handles DB calls. In your case, it's JPA repositories. So it's better to implement a query in a custom Jpa repository, inject its instance here and invoke appropriate method.

Upvotes: 1

Related Questions