I'm trying to use JPARepository in Spring Boot to delete records that are less than a certain date, for for a given userid

Should be something like this Delete * from [table] where expiration_date < [date] and userid = [userid]

I thought I should be able to use one of the automatically generated methods

    int deleteByExpiryDateBeforeAndUser(Date date, User user);

But this is generating a Select and not a Delete. What am I doing wrong?

Update

Entity class

@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() {
    }
}

Repository class

@Repository
public interface RefreshTokenRepository extends JpaRepository<RefreshToken, Long> {
    Optional<RefreshToken> findByToken(String token);

     @Modifying
    void deleteByUserIdAndExpiryDateBefore(Long userId, Date expiryDate);

    int deleteByUser(User user);
}

Here's how I'm calling it

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

CodePudding user response：

You see a select statement because Spring Data first loads entities by condition.

Then once entities became 'managed' Spring Data issues a delete query for each entity that was found.

If you want to avoid redundant SQL query - you have to consider @Query annotation.

Then your code will look like this:

@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);

    //...
}