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.

Do you have to check before or after the join if the dates are null? — FaltFe
– FaltFe, Commented Sep 2, 2020 at 11:06
Then what is your problem with :endDate IS NULL respectively endDate IS NOT NULL? — FaltFe
– FaltFe, Commented Sep 2, 2020 at 11:22
i get an error like "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet" — Kropius Dop
– Kropius Dop, Commented Sep 2, 2020 at 11:37

Conor Costello · Accepted Answer · 2020-09-03 08:52:16Z

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.

answered Sep 3, 2020 at 8:52

Conor Costello

714 bronze badges

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

Comments

BendaThierry.com · Accepted Answer · 2020-09-02 12:36:49Z

0

Maybe you can build your SQL query with a and COALESCE(field, default_value_for_datetime_field). So you will not get NULL values.

You can build a default value as the start of the current month, of the current week, or any datetime value which could be a fair substitution for your model domain.

answered Sep 2, 2020 at 12:36

BendaThierry.com

2,1281 gold badge16 silver badges17 bronze badges

Collectives™ on Stack Overflow

Check if java Timestamp is null in jpql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related