I've changed from Spring-Boot 3.1.4 to 3.3.0 and now one JPA Query that queries an Oracle database is not working anymore:
@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') + 1
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);
...
Error: Caused by: java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: Operand of + is of type 'java.lang.Object' which is not a numeric type (it is not an instance of 'java.lang.Number', 'java.time.Temporal', or 'java.time.TemporalAmount')
I know that in this version Spring-Boot use Hibernate 6 and it is complaining about the operator +
, but how can I add one day on a JPA Query string?
I did as @Yann39 said and changed the parameter to LocalDateTime and passed Java date already adding one day to the endDate.
@Query(value = """
SELECT new
...
LEFT JOIN cc.company com
LEFT JOIN fpl.fileParameter fp
LEFT JOIN fpl.person p
WHERE fpl.actionTime
BETWEEN :startDate AND :endDate
AND at.id IN (2,3,4,5,6,7,8,9)
ORDER BY fpl.id DESC
""")
Page<LogForSendingParameterQuery> findLogForSendingParameter(LocalDateTime startDate, LocalDateTime endDate, Pageable pageable);
And added two methods to convert date from String to LocalDateTime and to add one day:
@NotNull
private static LocalDateTime getLocalDateTime(String date) {
return LocalDate.parse(date).atTime(ZERO, ZERO, ZERO);
}
@NotNull
private static LocalDateTime getLocalDateTimePlusOneDay(LocalDateTime date) {
return date.plusDays(ONE_DAY);
}