Spring Boot Records older then 5 years in Oracle

Question

Query:

@Query("select o from Order o where o.creationDate < add_months(sysdate, -60)")

add_months is not possible in JPA query. How can I get records older then five years?

Answer 1

The JPQL does not have this kind of function to work with dates.

However, you have two options:

1. Use native SQL with the option nativeQuery = true
2. Compute the date on your Java code and pass the result to the query

So, you can use the native SQL adding the attribute nativeQuery to the @Query:

@Query(nativeQuery = true, value = "select * from Order o where o.creationDate < add_months(sysdate, -60)")

The use of the nativeQuery can be trick when mounting the entity. But this kind of problem is out of the scope of the question.

The another alternative is still use JPQL, but calculating the old date before call the SQL:

LocalDateTime oldDate = LocalDateTime.now().minusMonths(60);

Changing the @Query to receive the date as parameter:

@Query("select o from Order o where o.creationDate < ?1 ")
List<Order> findOldOrders(LocalDateTime oldDate);

And calling it:

List<Order> orders = repository.findOldOrders(oldDate);