2

So in MySQL, NULL != NULL. So if you write a query like the following:

SELECT id, city, last_updated, street_address, zipcode, state_id FROM addresses WHERE street_address = null

You will get 0 rows, even if you have a column where street_address is null.

EclipseLink is generating this SQL:

[EL Fine]: 2014-01-27 18:07:38.388--ServerSession(2079453576)--Connection(1753616363)--Thread(Thread[Default JMS Resource Adapter-worker- - 3,5,main])--SELECT id, city, last_updated, street_address, zipcode, state_id FROM addresses WHERE (street_address = ?) bind => [null]

Which will never match anything. How do I get EclipseLink to use: is null rather than = null?

EDIT:

Thank you for the answers, but if you read my question, I already know about querying for IS NULL... But my queries don't always query for null. I'm asking how do you get EclipseLink to generate that SQL automatically? For instance, if I have a named query, and I set a parameter to null, how do I get EclipseLink to generate IS NULL not = NULL

Jonathan S. Fisher
  • 8,189
  • 6
  • 46
  • 84

4 Answers4

4

According to EclipseLink JPA UserGuide it does support IS NULL / IS NOT NULL operators so you should be able to write your SQL statement as follows:

SELECT ... FROM ... WHERE street_address IS NULL

Edit

For instance, if I have a named query, and I set a parameter to null, how do I get EclipseLink to generate IS NULL not = NULL

Let SQL handle this for you. In your example:

String sql = "SELECT ... FROM ... WHERE (street_address = :address OR street_address IS NULL)";
Query query = em.createQuery(sql);
query.setParameter("address", address.getStreetAddress());

If address.getStreetAddress() is not null then first OR expression is the relevant to the query. Otherwise still match the second expression.

dic19
  • 17,821
  • 6
  • 40
  • 69
3

First, thank you everyone for the answers. It turns out, this is a limitation of MySQL, it's sort of odd that NULL != NULL for an RDBMS and EclipseLink does not handle this scenario gracefully.

EclipseLink can create proper IS NULL sql by disabling query parsing cache. You potentially could take a performance hit. I measured mine with a profiler and saw less than 0.01ms difference (basically outside the ability for me to measure accurately). Your mileage will vary of course, and I recommend using a profiler to see if it actually is a big deal. This does not mean you are disabling prepared statements, which would be a massive performance hit. This is relatively minor for my cases.

Essentially you need to set a query hint before executing the query. The hit you are looking for is here: http://www.eclipse.org/eclipselink/api/2.0/org/eclipse/persistence/config/QueryHints.html#PREPARE

Jonathan S. Fisher
  • 8,189
  • 6
  • 46
  • 84
0

This is not possible. The query is created independent of the parameters that are set later, it will not take the specific values into account. I don't see a possibility to build a JPQL query that takes care of both cases and delivers your desired result.

So you have to check for null manually, i.e. in code. Either you build two queries that you choose dependent of the value of the parameter or you use the criteria API to dynamically build the query. In this case (if it stays this simple with only one parameter) the first variant is preferable; if you have a higher or variable number of parameters you should use the latter.

Hauke Ingmar Schmidt
  • 11,559
  • 1
  • 42
  • 50
-1

There's a mysql command called isnull() , use this SELECT id, city, last_updated, street_address, zipcode, state_id FROM addresses WHERE isnull(street_address)

Spyros Sakellaropoulos
  • 69
  • 1
  • 7