Spring Boot升级后Hibernate 6.5.x中JPA查询如何加一天？

针对Hibernate 6对类型检查的严格要求，替代原TO_DATE(...) +1写法的方案如下：

• 使用Oracle原生日期间隔语法
  明确指定间隔类型，让Hibernate正确识别运算数的类型，替换原有的+1：

  @Query(value = """
       SELECT new ...
       LEFT JOIN cc.company com
       LEFT JOIN fpl.fileParameter fp
       LEFT JOIN fpl.person p
       WHERE fpl.actionTime 
        BETWEEN TO_DATE(:startDate, 'DD-MM-YYYY') AND TO_DATE(:endDate, 'DD-MM-YYYY') + INTERVAL '1' DAY
              AND at.id IN (2,3,4,5,6,7,8,9)
              ORDER BY fpl.id DESC
      """)
  Page<LogForSendingParameterQuery> findLogForSendingParameter(String startDate, String endDate, Pageable pageable);
  

• 使用Oracle NUMTODSINTERVAL函数
  通过函数将数值转换为日期间隔，通过Hibernate 6的类型校验：

  @Query(value = """
       SELECT new ...
       LEFT JOIN cc.company com
       LEFT JOIN fpl.fileParameter fp
       LEFT JOIN fpl.person p
       WHERE fpl.actionTime 
        BETWEEN TO_DATE(:startDate, 'DD-MM-YYYY') AND TO_DATE(:endDate, 'DD-MM-YYYY') + NUMTODSINTERVAL(1, 'DAY')
              AND at.id IN (2,3,4,5,6,7,8,9)
              ORDER BY fpl.id DESC
      """)
  Page<LogForSendingParameterQuery> findLogForSendingParameter(String startDate, String endDate, Pageable pageable);
  

• Java层预处理日期（推荐）
  避开JPQL中的日期运算，直接在调用前将endDate加一天后传入，兼容JPA标准且无数据库函数依赖：

  // 调用前处理日期（Java 8+时间API示例）
  String endDate = "31-12-2024";
  DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
  LocalDate endLocalDate = LocalDate.parse(endDate, formatter);
  LocalDate nextDay = endLocalDate.plusDays(1);
  String endDatePlusOne = formatter.format(nextDay);
  
  // 调用Repository方法
  logRepository.findLogForSendingParameter(startDate, endDatePlusOne, pageable);
  

  对应的JPQL简化为：

  @Query(value = """
       SELECT new ...
       LEFT JOIN cc.company com
       LEFT JOIN fpl.fileParameter fp
       LEFT JOIN fpl.person p
       WHERE fpl.actionTime 
        BETWEEN TO_DATE(:startDate, 'DD-MM-YYYY') AND TO_DATE(:endDate, 'DD-MM-YYYY')
              AND at.id IN (2,3,4,5,6,7,8,9)
              ORDER BY fpl.id DESC
      """)
  Page<LogForSendingParameterQuery> findLogForSendingParameter(String startDate, String endDate, Pageable pageable);
  

内容的提问来源于stack exchange，提问作者Aldo Inácio da Silva