Hibernate @Where软删除在升级到spring boot 3后导致JPA连接问题的实体

Question

我遇到了一个问题。我跑弹簧靴3。它在春季启动2我有这样一种关系(简化为问题)

组中有用户。用户有令牌

@Entity(name = "Group")
@Where(clause = "not is_deleted or is_deleted is null")
@SQLDelete(sql = "UPDATE group SET is_deleted = true, updated_at = NOW() WHERE id=?")
class GroupEntity(
@OneToMany(mappedBy = "group", cascade = [CascadeType.ALL], fetch = FetchType.EAGER)
val users: MutableSet<UserEntity> = HashSet()

@Entity(name = "User")
@Where(clause = "not is_deleted or is_deleted is null")
@SQLDelete(sql = "UPDATE user SET is_deleted = true, updated_at = NOW() WHERE id=?")
class UserEntity(
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "group_id", nullable = true)
val group: GroupEntity,
@OneToOne(cascade = [CascadeType.ALL], mappedBy = "user", fetch = FetchType.EAGER)
var token: TokenEntity?

@Entity(name = "Token")
@Where(clause = "not is_deleted or is_deleted is null")
@SQLDelete(sql = "UPDATE token SET is_deleted = true, updated_at = NOW() WHERE id=?")
class TokenEntity(
@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "user_id", nullable = true)
val user: UserEntity

现在我有一个测试，检查我是否可以删除用户。作为这个测试的一部分，我想取一个组，只有一个已删除的用户使用标准jparerepository。findById。然而，这总是给出结果:

NotFoundException(detail=Group with id 9aee9ef0-ac33-11ec-b909-0242ac120002 not found, throwable=null)

，即使它确实存在于test db中。我已经启用hibernate sql调试选项，并发现结果查询是这样的:

select * from group f1_0
left join user v1_0 on f1_0.id=v1_0.group_id
left join token t1_0 on v1_0.id=t1_0.user_id where
(not v1_0.is_deleted or v1_0.is_deleted is null) 
and f1_0.id='9aee9ef0-ac33-11ec-b909-0242ac120002' 
and (not f1_0.is_deleted or f1_0.is_deleted is null)

我期望得到一个结果，我得到一个组实体与空用户设置(因为他们都被删除)。然而这一行-

(not v1_0.is_deleted or v1_0.is_deleted is null)

将放在where子句中会产生完全不同的行为——如果删除组内的所有用户，它将不返回任何内容。我所期望和想要的是这个查询:

select * from group f1_0
left join user v1_0 on f1_0.id=v1_0.group_id and
(not v1_0.is_deleted or v1_0.is_deleted is null)
left join token t1_0 on v1_0.id=t1_0.user_id where
f1_0.id='9aee9ef0-ac33-11ec-b909-0242ac120002' 
and (not f1_0.is_deleted or f1_0.is_deleted is null)

，将用户的已删除部分移动到连接子句中。我只是不知道如何让hibernate做那个

Answer 1

我也面临同样的情况。

<dependency>
<groupId>org.hibernate.orm</groupId>
<artifactId>hibernate-core</artifactId>
<!-- to prevent the bug from 6.1.6.Final    https://hibernate.atlassian.net/browse/HHH-15902 -->
<version>6.2.0.CR2</version>
</dependency>

根据
Andrey B. Panfilov提到的bug将hibernate和spring boot更新到3.0.2对我的情况很有帮助。

Answer 2

发帖后我尝试了另一件事:将@Fetch(FetchMode.SELECT)添加到用户集合使其工作，但是我仍然不满意这个解决方案真的，我担心这不能像我期望的那样开箱工作，并且我可能在其他地方/情况下有类似的问题，我可能没有测试

Answer 3

请看下面文章的第4节:https://www.baeldung.com/spring-jpa-soft-delete。如果您想查询软删除的数据，它告诉您不要使用@Where，而使用@SQLDelete,@FilterDef和@Filter。然后，在使用存储库之前，将过滤器应用于实体管理器的会话。完成后请记住禁用过滤器。

Answer 4

从2升级到Spring Boot后。从x到3.0.2，我遇到了这个问题。Hibernate生成的SQL语句有一个空的"where";导致SQL语法错误异常的子句

我更新了hibernate-core到6.1.7。问题终于解决了。谢谢@niels-siebert。