[eclipselink-users] Querying on null parameter values

[Phillip Ross <phillip.w.g.ross@xxxxxxxxx>]

Hi all... I'm looking for a little assistance on using named queries
using parameters that may have null values.  I'm positive this worked
before but it's not working for me now and I'm wondering if I'm
running into incorrect usage scenario or a bug.

Basically I have an entity with some fields that are nullable and a
named query that uses them in the query.  Example code:

query annotation:
@NamedQuery(name="Purchase.findByDetails", query="select p from
Purchase p where p.detail1 = :detail1 and p.detail2 = :detail2 and
p.detail3 = :detail3")

The property declarations in the Purchase entity:
private String detail1;
private String detail2;
private String detail3;

Example query code:
query = entityManager.createNamedQuery("Purchase.findByDetails");
query.setParameter("detail1", detail1);
query.setParameter("detail2", detail2);
query.setParameter("detail3", detail3);
purchases = query.getResultList();


So when the content of detail1, 2, 3 parameters are null, the query
fails to return records where the values of detail1/2/3 are null.
This is surprising as I was positive that the provider would
automatically handle the translation to database platform specific
clauses that would use "is null" predicates if necessary.  This is NOT
what happens with eclipselink 2.3.0 on postgresql or derby.  It simply
doesnt return the results.

Am I just doing this wrong and really have to dynamically build the
JPQL query to use JPQL "is null" with null checks on the parameter
values and all of that?  Or am I running into a bug?

Thanks
- Phillip