Check null parameter in NamedQuery

Question

When a column is numeric type I usually use this parameter verification in my named queries:

(-1 = ?1 OR column = ?1)

But with a date filter using between i can't do the same type of verification:

(p.date between ?1 and ?2)

The solution I found was to add a new parameter checking whether the date is null:

(dateInitial == null || dateFinal == null)

In named query:

(?3 = true OR p.date between ?1 and ?2)

• Is there a solution that i don't need to add another parameter? Using the existing parameters.

Answer 1

Instead of

(p.date between ?1 and ?2)

You can try

(p.date < ?1 or ?1 is null) and (p.date > ?2 or ?2 is null)