Spring Data JPA Native SQL Query DELETE SQL Query

Question

I use @Modifying annotation together with @Query annotation to perform SQL DELETE query and delete the record from a database table.

@Modifying
@Query(value = "DELETE FROM CUSTOMERS  where CUSTOMERS.ID =:customersId and CUSTOMERS.USER_ID  = :userId and CUSTOMERS.USER_ID  = :sellerId", nativeQuery = true)
void deleteContributeur(@Param("customersId") Long customersId, @Param("userId") Long userId, @Param("sellerId") Long sellerId);

Error:

Exception in xxx.xxx.xx with cause = 'javax.persistence.TransactionRequiredException: Executing an update/delete query' and exception = 'Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query'

is this part correct "CUSTOMERS.USER_ID = :userId and CUSTOMERS.USER_ID = :sellerId"" — Shameera Anuranga
– Shameera Anuranga, Commented Sep 9, 2019 at 8:53

Mercer · Accepted Answer · 2019-09-09 09:03:39Z

4

Annotate the service method with @Transactional. Your query is wrong. Try this:

@Modifying
@Transactional
@Query(value = "DELETE FROM CUSTOMERS  where CUSTOMERS.ID =:customersId and CUSTOMERS.USER_ID  IN (:userId,:sellerId)", nativeQuery = true)
void deleteContributeur(@Param("customersId") Long customersId, @Param("userId") Long userId, @Param("sellerId") Long sellerId);

answered Sep 9, 2019 at 9:03

Mercer

10k32 gold badges115 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andronicus · Accepted Answer · 2019-09-09 08:43:21Z

1

You need a transaction to run this query. There are many ways, but the simplest one would be to annotate the service method with @Transactional. But remember it hat to be public and invoked from other bean (wrapped in proxy).

answered Sep 9, 2019 at 8:43

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Comments

Aaron Guilbot · Accepted Answer · 2019-09-09 08:45:50Z

1

Method had default visibility. Therefore the proxy mechanism was not active! After changing to public everthing works as expected!

@Modifying
@Transactional
@Query(value = "DELETE FROM CUSTOMERS  where CUSTOMERS.ID =:customersId and CUSTOMERS.USER_ID  = :userId and CUSTOMERS.USER_ID  = :sellerId", nativeQuery = true)
void deleteContributeur(@Param("customersId") Long customersId, @Param("userId") Long userId, @Param("sellerId") Long sellerId);

I use the @Transactional annotation from Spring package like @org.springframework.transaction.annotation.Transactional Then it should work

answered Sep 9, 2019 at 8:45

Aaron Guilbot

1992 silver badges14 bronze badges

Comments

barbsan · Accepted Answer · 2019-09-09 09:12:22Z

1

It's transactional problem - you need to add @Transactional. See this guide.

edited Sep 9, 2019 at 9:12

barbsan

3,46811 gold badges23 silver badges29 bronze badges

answered Sep 9, 2019 at 8:45

jemmy Negatron

212 bronze badges

Collectives™ on Stack Overflow

Spring Data JPA Native SQL Query DELETE SQL Query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related