CODEWITHSUNDEEP
javasqlspringspring-bootspring-data
Aaron Guilbot
Aaron Guilbot

Reputation: 199

Spring Data JPA Native SQL Query DELETE SQL Query

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'

Upvotes: 2

Views: 5067

Answers (4)

jemmy Negatron
jemmy Negatron

Reputation: 21

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

Upvotes: 1

Mercer
Mercer

Reputation: 9986

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);

Upvotes: 4

Aaron Guilbot
Aaron Guilbot

Reputation: 199

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

Upvotes: 1

Andronicus
Andronicus

Reputation: 26076

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).

Upvotes: 1

Related Questions