I want to remove native query from this method. How do I do it?

Question

@Modifying(clearAutomatically = true)
@Transactional 
@Query(value = "DELETE FROM transaction WHERE transaction_cre_sys_date <= (now() - interval 6 month)",nativeQuery = true) 
void deleteSixMonthOldTransactions();

This is my Transaction entity. I want to delete the records older than 6 months

public class Transaction { 
    private Integer id; 
    private String transactionSource; 
    private String transactionId; 
    private LocalDateTime transactionCreSysDate; 
}

Answer 1

Your comments seem to indicate that you want to create the same effect as the native query with a derived query.

This is not possible.

If taken literally you only don't want to use SQL, which would mean to use JPQL.

The problem with that is that JPQL has only very limited features for dealing with dates and that does not seem to include a way to add or substract months from a time stamp.

So what you could do is to use Spring Expression Language to pass in the cut of date.

Here is the canonical blog post how to use SpEL in Spring Data Queries.

Here is some discussion on how to manipulate dates in SpEL. How do I do date manipulation in SpEL?

But the real answer is: just keep the SQL.