Optional parameters in named query in EclipseLink

Question

I am new to JPA named queries using EclipseLink and I want to "ignore" properties with null values in named query. I know that my question has been answered many times. e.g. JPA Query to handle NULL parameter value

However, in my case following format is not working

+ " AND (:quoteNumber IS NULL OR ord.quoteNumber = :quoteNumber)"

I am getting error 'ILLEGAL USE OF KEYWORD NULL'. I will be using CriteriaQuery now and just curious why it is not working in named query. Following are the DB2 and Eclipselink versions being used. eclipselink: 2.5.1 DB2: DSN11015

Answer 1

The JPA Specification says that

3.8.13 Named Queries
Named queries are static queries expressed in metadata. Named queries can be defined in the Java Persistence query language or in SQL. Query names are scoped to the persistence unit.

So you can't really expect them to change on runtime based on some null condition. Criteria Query, as you point out, is dynamic by nature, so would be the way to go.

EDIT based on comment:

 AND (ord.quoteNumber = :quoteNumber or :quoteNumber is null or :quoteNumber = '' 

does not change the query on runtime (does not skip the clause). It evaluates the clause as TRUE. The problem with DB2 (and Derby as far as I know) is, that they do not allow "non-typed Null to be sent to the backend" as per API PreparedStatement.setObject. You can test it by setting the type via casting

 AND (ord.quoteNumber = cast(:dfdTxt as integer) or cast(:dfdTxt as integer) is null or cast(:dfdTxt as integer) = ''

So this approach is DB Implementation specific and might change at some point.