Spring Data JPA 删除本机查询抛出异常 [英] Spring Data JPA delete native query throwing exception

问题描述

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

@OneToMany@JoinTable(name="USER_ROLES", inverseJoinColumns=@JoinColumn(name="ROLE_ID"))私人列表<角色>角色 = 空；

现在，当我删除一个角色时，我需要从拥有该角色的所有用户中删除该角色.通常，您会通过查找具有此角色的所有用户、从列表中删除该角色并保存该用户来执行类似操作.但是，当用户可能超过一百万时，我不想在应用程序中遍历这么多实体.因此，我想使用本机查询从 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 = ?绑定 =>[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 警告]:2013-11-02 14:27:14.482--UnitOfWork(1795045370)--Thread(Thread[http-bio-8080-exec-4,5,main])--异常 [EclipseLink-4002](Eclipse 持久性服务 - 2.4.1.v20121003-ad44345):org.eclipse.persistence.exceptions.DatabaseException内部异常:org.postgresql.util.PSQLException:查询未返回任何结果.错误代码:0调用: DELETE FROM user_roles WHERE role_id = ?绑定 =>[1000110139999999953]查询:DataReadQuery(sql="DELETE FROM user_roles WHERE role_id = ?")

我不明白查询没有返回任何结果. 在说什么.记录确实从数据库中删除了，但这个异常导致一切都炸了.

有人可以告诉我我在这里做错了什么吗?

解决方案

用@Query 注释的方法执行查询以从数据库中读取.不更新数据库.要做到这一点，作为 文档指出，需要在方法中添加@Modifying注解:

<块引用>

以上所有部分都描述了如何声明查询以访问给定实体或实体集合.当然，您可以使用第 1.3 节Spring Data 存储库的自定义实现"中描述的工具添加自定义修改行为.由于这种方式对于全面的自定义功能是可行的，您可以通过@Modifying对查询方法进行注释来实现修改实际上只需要参数绑定的查询的执行:

例 2.13.声明操作查询

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

<块引用>

这将触发在方法中注释为更新查询而不是选择查询的查询.

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?

解决方案

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.

这篇关于Spring Data JPA 删除本机查询抛出异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！