javahibernatejpaspring-data-jpahibernate-6.x

How to add one day on JPA Query string - Hibernate 6.5.x


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?


Solution

  • 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);
        }