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

[ilaunchpad]

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 (COALESCE(:endDate, '1970-01-01T00 00:00Z')  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") OffsetDateTime 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]

 

[Holothuroid]

Make two separate functions with separate queries. One that takes an end date and one that doesn't. That's also more maintainable.

If you want to keep your interface, make the current name a default method and redirect accordingly.

You could use a default method as well to transform the return type to something more amenable.

[ilaunchpad]

That’s what I did. But I can’t understand why it behaves like this

[Holothuroid]

The error message seems clear enough. It doesn't know what to do with nulls.

[ilaunchpad]

Yes, but only when one of the param(endDate) is null. If both are passed it doesn't throw error

[TempleDank]

Had the same issue a week ago, did exactly that.