如何将Oracle过期查询改写为支持多天数列表参数的JPQL动态查询

JPQL作为跨数据库的标准查询语言，不直接支持Oracle特有的SYS.ODCINUMBERLIST集合类型、TABLE()函数语法，你可以根据业务场景选择以下两种实现方案：

方案1：JPQL标准实现（跨数据库兼容） #

思路是提前在Java层把传入的天数列转换成目标日期列表，直接用IN条件匹配，完全符合JPQL规范，不需要依赖数据库特定语法。

步骤1：定义对应实体类 #

@Entity
@Table(name = "PASS_EXP")
public class PassExp {
    @Id
    private Long id;
    @Column(name = "EXPIRY_DT")
    private LocalDateTime expiryDt;
    // 其余字段、get/set方法省略
}

步骤2：编写JPQL查询（Spring Data JPA示例） #

public interface PassExpRepository extends JpaRepository<PassExp, Long> {
    @Query("SELECT p FROM PassExp p WHERE FUNCTION('TRUNC', p.expiryDt) IN :targetDates")
    List<PassExp> findByTruncExpiryDtIn(List<LocalDate> targetDates);
}

步骤3：参数预处理 #

调用查询前把传入的天数列转换为目标日期列表：

// 传入的天参数
List<Integer> days = List.of(1,3,5,7,15);
// 转换为目标日期列表
List<LocalDate> targetDates = days.stream()
        .map(day -> LocalDate.now().plusDays(day))
        .toList();
// 执行查询
List<PassExp> result = passExpRepository.findByTruncExpiryDtIn(targetDates);

方案2：JPA原生查询（保留Oracle原有逻辑，性能更优） #

如果项目仅对接Oracle数据库，推荐直接用原生查询保留原有SQL逻辑，避免TRUNC(EXPIRY_DT)函数导致索引失效，查询性能更好。

原生查询写法（Spring Data JPA示例） #

public interface PassExpRepository extends JpaRepository<PassExp, Long> {
    @Query(value = "SELECT p.* " +
            "FROM PASS_EXP p " +
            "INNER JOIN TABLE(SYS.ODCINUMBERLIST(:days)) t " +
            "ON p.expiry_dt >= TRUNC(SYSDATE) + t.COLUMN_VALUE " +
            "AND p.expiry_dt < TRUNC(SYSDATE) + t.COLUMN_VALUE + 1",
            nativeQuery = true)
    List<PassExp> findExpiringPasses(@Param("days") List<Integer> days);
}

注意事项 #

如果运行时报集合类型不匹配错误，可升级Oracle JDBC驱动到19c及以上版本，低版本驱动需要手动将List<Integer>转换为Oracle ARRAY类型后再传入参数。

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