JPARepository - delete使用日期比较与派生查询

Question

我试图在Spring Boot中使用jparerepository来删除小于特定日期的记录，对于给定的userid

应该是这样的Delete * from [table] where expiration_date < [date] and userid = [userid]

我想我应该能够使用其中一个自动生成的方法

int deleteByExpiryDateBeforeAndUser(Date date, User user);

但是这生成的是Select而不是Delete。我做错了什么?

实体类

@Getter
@Setter
@ToString
@Entity(name = "refresh_token")
public class RefreshToken {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;
@OneToOne
@JoinColumn(name = "user_id", referencedColumnName = "id")
private User user;
@Column(nullable = false, unique = true)
private String token;
@Column(nullable = false)
private Date expiryDate;
public RefreshToken() {
}
}

<<p>库类/em>

@Repository
public interface RefreshTokenRepository extends JpaRepository<RefreshToken, Long> {
Optional<RefreshToken> findByToken(String token);
@Modifying
void deleteByUserIdAndExpiryDateBefore(Long userId, Date expiryDate);
int deleteByUser(User user);
}

我是这样命名它的

@Transactional
public void deleteExpiredTokens(User user) {
refreshTokenRepository.deleteByUserIdAndExpiryDateBefore(user.getId(), new Date());
}

Answer 1

您将看到一个select语句，因为Spring Data首先根据条件加载实体。

一旦实体被"管理"，Spring Data就会对找到的每个实体发出删除查询。

如果你想避免冗余的SQL查询-你必须考虑@Query注释。

然后你的代码看起来像这样:

@Repository
public interface RefreshTokenRepository extends JpaRepository<RefreshToken, Long> {
// ...

@Query(value = "DELETE FROM refresh_token WHERE user_id =:userId AND expiry_date < :expiryDate", nativeQuery = true)
@Modifying
void deleteByUserIdAndExpiryDateBefore(Long userId, Date expiryDate);
//...
}