Spring Data JPA delete native query throwing excep

Question

I have a User entity and a Role entity. The relationship is defined like this:

@OneToMany
@JoinTable(name="USER_ROLES", inverseJoinColumns=@JoinColumn(name="ROLE_ID"))
private List<Role> roles = null; 

Now, when I delete a role, I need to delete the role from all the users that have that role. Normally you'd do something like this by looking up all the users with this role, removing the role from the list, and saving the user. However, when there could be over a million users, I don't want to be looping over this many entities in the app. So, I'm wanting to use a native query to remove rows from the USER_ROLES join table. I tried adding this to my repository:

@Query(value="DELETE FROM user_roles WHERE role_id = ?1", nativeQuery=true)
public void deleteRoleFromUsersWithRole(Long roleId);

However, when I do this, I see the following in the logs:

[EL Fine]: sql: 2013-11-02 14:27:14.418--ClientSession(707349235)--Connection(2096606500)--Thread(Thread[http-bio-8080-exec-4,5,main])--DELETE FROM user_roles WHERE role_id = ?
   bind => [1000110139999999953]
[EL Fine]: sql: 2013-11-02 14:27:14.478--ClientSession(707349235)--Thread(Thread[http-bio-8080-exec-4,5,main])--SELECT 1
[EL Warning]: 2013-11-02 14:27:14.482--UnitOfWork(1795045370)--Thread(Thread[http-bio-8080-exec-4,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: No results were returned by the query.
Error Code: 0
Call: DELETE FROM user_roles WHERE role_id = ?
    bind => [1000110139999999953]
Query: DataReadQuery(sql="DELETE FROM user_roles WHERE role_id = ?")

I don't understand what No results were returned by the query. is saying. The record does get deleted from the database, but this exception is causing everything to blow up.

Can someone please tell me what I'm doing wrong here?

Answer 1

A method annotated with @Query executes a query in order to read from the database. Not to update the database. To do that, as the documentation indicated, you need to add the @Modifying annotation to the method:

All the sections above describe how to declare queries to access a given entity or collection of entities. Of course you can add custom modifying behaviour by using facilities described in Section 1.3, “Custom implementations for Spring Data repositories”. As this approach is feasible for comprehensive custom functionality, you can achieve the execution of modifying queries that actually only need parameter binding by annotating the query method with @Modifying:

Example 2.13. Declaring manipulating queries

@Modifying
@Query("update User u set u.firstname = ?1 where u.lastname = ?2")
int setFixedFirstnameFor(String firstname, String lastname);

This will trigger the query annotated to the method as updating query instead of a selecting one.

Answer 2

use this two annotations

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.transaction.annotation.Transactional;

@Modifying
@Transactional