Java Spring Data JPA 删除本机查询抛出异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19746668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring Data JPA delete native query throwing exception
提问by dnc253
I have a Userentity and a Roleentity. The relationship is defined like this:
我有一个User实体和一个Role实体。关系定义如下：
@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_ROLESjoin table. I tried adding this to my repository:
现在，当我删除一个角色时，我需要从拥有该角色的所有用户中删除该角色。通常，您会通过查找具有此角色的所有用户、从列表中删除该角色并保存该用户来执行类似操作。但是，当用户数可能超过一百万时，我不想在应用程序中遍历这么多实体。所以，我想使用本机查询从USER_ROLES连接表中删除行。我尝试将其添加到我的存储库中：
@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.
我不明白No results were returned by the query.在说什么。该记录确实从数据库中删除，但这个异常导致一切都炸了。
Can someone please tell me what I'm doing wrong here?
有人可以告诉我我在这里做错了什么吗？
采纳答案by JB Nizet
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 documentationindicated, you need to add the @Modifyingannotation to the method:
用@Query 注释的方法执行查询以从数据库中读取。不更新数据库。为此，如文档所示，您需要将@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
以上所有部分都描述了如何声明查询以访问给定的实体或实体集合。当然，您可以使用第 1.3 节“Spring Data repositories 的自定义实现”中描述的工具添加自定义修改行为。由于这种方式对于全面的自定义功能是可行的，您可以通过使用@Modifying注解查询方法来实现修改实际上只需要参数绑定的查询的执行：
例 2.13。声明操作查询
@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.
这将触发注释到方法的查询作为更新查询而不是选择查询。
回答by Achraf Farouky
use this two annotations
使用这两个注释
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.transaction.annotation.Transactional;
@Modifying
@Transactional
