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; 
}

I want use this method without the native query. like findByNationalId(String id). like this — Akila Ekanayake, Jul 17 '20 at 13:48
this method is used to delete records older than 6 months. transaction_cre_sys_date is the date variable. It is saved as a localDateTime type — Akila Ekanayake, Jul 17 '20 at 13:49
I don't understand. Do you want to delete or select the records? — Simon Martinelli, Jul 17 '20 at 13:53
public class Transaction { private Integer id; private String transactionSource; private String transactionId; private String responseCode; private String failureReason; private LocalDateTime transactionCreSysDate; } @JensSchauder — Akila Ekanayake, Jul 17 '20 at 14:37
Please don't post code in comments, add it to the question by editing it. It's much easier to read that way. — Jens Schauder, Jul 17 '20 at 14:41
@SimonMartinelli SQL query works fine. I want to learn how to use that with jpa derived query — Akila Ekanayake, Jul 17 '20 at 14:42
Side note: `LocalDateTime` is a bad choice because it does not denote a point in time. Use `Instant` instead. — Jens Schauder, Jul 17 '20 at 14:48
@JensSchauder noted the mistake. Thank you for the pointer. Above sql query worked with LocalDateTime. That's why I used that type — Akila Ekanayake, Jul 17 '20 at 14:52

score 0 · Accepted Answer · answered Jul 17 '20 at 14:55

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.

Welcome to SO. If you find an answer helpful, please upvote it. If it also solved your problem, please accept it. — Jens Schauder, Jul 17 '20 at 15:05

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

1 Answers1