Spring Boot中如何通过JPA查询Oracle中5年以上的旧记录？

Got it, let's figure out how to solve this since JPQL doesn't play nice with Oracle's add_months function out of the box. Here are three reliable approaches you can use:

This is the most straightforward fix because native SQL directly supports Oracle's built-in functions. Just add nativeQuery = true to your @Query annotation, and write the query using your actual database table and column names (not the entity class property names):

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query(value = "SELECT * FROM ORDER_TABLE o WHERE o.CREATION_DATE < add_months(sysdate, -60)", nativeQuery = true)
    List<Order> findOrdersOlderThan5Years();
}

Note: Replace ORDER_TABLE and CREATION_DATE with your actual database table name and column name for the creation date.

If you want a database-agnostic solution (so your code works if you switch to MySQL or PostgreSQL later), calculate the 5-year-old date in Java and pass it as a parameter to your JPQL query. This avoids relying on database-specific functions entirely:

First, define the repository method:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("select o from Order o where o.creationDate < :fiveYearsAgo")
    List<Order> findOrdersOlderThan5Years(@Param("fiveYearsAgo") LocalDate fiveYearsAgo);
}

Then, when calling the method, compute the date using Java's java.time API:

// Calculate the date exactly 5 years before today
LocalDate fiveYearsAgo = LocalDate.now().minusYears(5);
List<Order> oldOrders = orderRepository.findOrdersOlderThan5Years(fiveYearsAgo);

Pro tip: If your entity uses java.util.Date instead of LocalDate, adjust the calculation to use new Date(System.currentTimeMillis() - 5L * 365 * 24 * 60 * 60 * 1000) (though java.time is preferred for date handling).

If you really want to keep using your original JPQL query, you can register Oracle's add_months function with Hibernate (since Hibernate is the most common JPA implementation). Here's how:

1. Create a custom dialect class that extends Oracle's default dialect:

package com.yourproject.package; // Replace with your actual package

import org.hibernate.dialect.Oracle12cDialect;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.dialect.function.StandardSQLFunction;

public class CustomOracleDialect extends Oracle12cDialect {
    public CustomOracleDialect() {
        super();
        // Register the add_months function so JPQL recognizes it
        registerFunction(
            "add_months",
            new StandardSQLFunction("add_months", StandardBasicTypes.DATE)
        );
    }
}

2. Update your application properties to use this custom dialect:

# application.properties
spring.jpa.properties.hibernate.dialect=com.yourproject.package.CustomOracleDialect

Now your original JPQL query will work as expected:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("select o from Order o where o.creationDate < add_months(sysdate, -60)")
    List<Order> findOrdersOlderThan5Years();
}

内容的提问来源于stack exchange，提问作者farahm