java - Spring Data JPA删除 native 查询抛出异常

Question

我有一个 User 实体和一个 Role 实体。关系定义如下:

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

现在，当我删除一个角色时，我需要从所有拥有该角色的用户中删除该角色。通常，您会通过查找具有该角色的所有用户、从列表中删除该角色并保存该用户来执行此类操作。但是，当用户可能超过一百万时，我不想在应用程序中循环访问这么多实体。因此，我想使用 native 查询从 USER_ROLES 连接表中删除行。我尝试将其添加到我的存储库:

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

但是，当我这样做时，我会在日志中看到以下内容:

[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 = ?")

我不明白 No results were returned by the query. 是什么意思。该记录确实已从数据库中删除，但此异常导致一切崩溃。

谁能告诉我我在这里做错了什么？

Answer 1

使用@Query 注释的方法执行查询以便从数据库中读取。不更新数据库。为此，如 the documentation指出，您需要在方法中添加 @Modifying 注释:

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.