0

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

Atul Kumbhar
  • 1,073
  • 16
  • 26
  • Is this your actual code? The double quote and/or right parenthesis seem misplaced. – mustaccio Nov 12 '18 at 02:55
  • That was typo. Thanks – Atul Kumbhar Nov 12 '18 at 04:28
  • That looks like a DB exception - what is the SQL that is generated? You'll have to check your database docs to see if "column = null" is supported, or if it is the "null is null" it doesn't like. – Chris Nov 12 '18 at 21:54
  • This is my named query: SELECT o FROM ORDER o WHERE (o.quoteNumber = :quoteNumber) OR (:quoteNumber IS NULL)) and this is generate one: SELECT * FROM ORDER o WHERE (o.quoteNumber = NULL) OR (NULL IS NULL)) – Atul Kumbhar Nov 14 '18 at 01:19
  • Do you expect some kind of dynamic adjustment of the Named Query (null parameter not being part of the where clause)? – briadeus Nov 15 '18 at 16:09
  • I expect to skip the clause at runtime if quoteNumber passed is null – Atul Kumbhar Nov 16 '18 at 00:12

1 Answers1

2

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.

briadeus
  • 550
  • 1
  • 6
  • 13
  • Thanks for your answer. Do you mean the answer provided [here](https://stackoverflow.com/questions/28554798/jpa-query-to-handle-null-parameter-value) is wrong? – Atul Kumbhar Nov 19 '18 at 02:02
  • Your welcome, I have updated my answer based on your comment. – briadeus Nov 19 '18 at 10:34