Check if java Timestamp is null in jpql query

Question

I have the following code:

    @Query("select t from Training t join t.skills s join t.trainers tr join t.discipline d  where " +
            "(t.name in :names or :names is null) and (s.name in :skills or :skills is null) and" +
            " (t.location = :location or :location is null) and " +
            " (d.name = :discipline or :discipline is null) and " +
            "(tr.firstName in :trainers or :trainers is null) and " +
            " (((:endDate > t.endDate) and (:startDate < t.startDate)) or (:startDate is empty))")
    public List<Training> filterTrainings(List<String> names, List<String> skills, String location,String discipline,List<String> trainers,Timestamp endDate,Timestamp startDate);

and i need to check if :startDate and :endDate are null. Is there a way to do that?

The error i get is nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

when trying to check :startDate is null where start date is a Timestamp.

Answer 1

Could you try passing a LocalDateTime as a parameter instead of a Timestamp? java.sql.Timestamp might be causing you issues here. You can convert to a LocalDateTime by calling timestamp.toLocalDateTime()

Alternatively you could try passing the timestamp as a string into filterTrainings. If the timestamp is null before calling the filterTrainings method, assign an empty string. String _timestamp = timestamp == null ? "" : timestamp.toString()

Then, in your sql statement check if the string is empty .. ""=:timestamp OR function("to_timestamp", :timestamp, "yyyy-mm-dd hh:mm:ss.fffffffff"). The problem here is that we are using function to access native db commands.