Using JPA with Java Spring Boot. Having Issue with optional parameter. JDBC could not determine data type

Question

As stated in the title, I'm facing this issue when optional paramter(endDate) is null. It doesn't throw any error when both parameters are provided. I tried usin cast null as timestamp with time zone, cast as timestamp to both params in the query and it throws the same error. Please advise.

@Query("""
SELECT sets.companyId, COUNT(sets)
FROM WarehouseSetsEntity sets
WHERE (:endDate IS NULL AND sets.importDate >= :beginDate)
OR (:endDate  IS NOT NULL AND sets.importDate BETWEEN :beginDate  AND :endDate)
GROUP BY sets.companyId""")
List<Object[]> fetchCompanyByDateRange(@Param("beginDate")  OffsetDateTime beginDate,  @ Param("endDate") @NullableOffsetDateTime endDate);

Error:org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select wse1_0.companyid,count(wse1_0.setid) from sets wse1_0 where (coalesce(?,'1970-01-01T00 00:00Z') is null and wse1_0.import_date>=?) or (? is not null and wse1_0.import_date between ? and ?) group by wse1_0.companyid] [ERROR: could not determine data type of parameter $3] [n/a]; SQL [n/a]

Amit J. · Accepted Answer · 2025-03-20 04:33:18Z

1

Can you try using below code?

WHERE sets.importDate >= :beginDate AND (sets.importDate <= :endDate OR :endDate IS NULL)

edited Mar 20 at 4:33

answered Mar 11 at 4:58

Amit J.

381 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using JPA with Java Spring Boot. Having Issue with optional parameter. JDBC could not determine data type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related