6

I just realized that JPQL where clause does not work as I expected when any of the parameters is null. For instance this simple JPQL query

SELECT en FROM Entity en WHERE en.name = :name

does not return any results when the name parameter is null even if there are entities with name set to null in the database.

In that case Hibernate executes SQL with WHERE entity.NAME = null. Obviously this is not handled by databases because standard defines IS NULL for null comparisons instead(see SQL is null and = null). JPQL has IS NULL operator as well(http://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbvi) that behaves exactly like in SQL(http://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbvr).

I thought this is a frequent case but a quick search did not give my any interesting results.

So, is there any way to include null values in the query? So far I came up with the following:

SELECT en FROM Entity en WHERE (:name IS NULL AND en.name IS NULL) OR en.name = :name

It works fine but it does not look elegant, especially in larger queries.

Bonus question: Why does JPQL mimic this weird aspect of SQL?

Community
  • 1
  • 1
Tomasz W
  • 2,263
  • 1
  • 17
  • 22
  • Well for the same reason why oracle uses is null instead of =null. https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm – Zeus Dec 10 '14 at 18:02
  • The same thing is also applicable while working without an ORM framework. Let's have an SQL statement like - `SELECT * FROM table_name WHERE column_name=:name`. If the value of the bound named parameter is `null` then, the statement is not going to magically change to reflect an `IS NULL` condition automatically. Isn't it? – Tiny Dec 10 '14 at 18:27
  • except this is JPQL **not** SQL, so the implementation is perfectly capable of deciding its SQL accordingly and should cope. – Neil Stockton Dec 10 '14 at 18:32
  • your solution is still a good answer – TMT020 Oct 07 '15 at 12:37

1 Answers1

3

Not all JPA implementations would convert en.name = :name with name parameter as null to entity.NAME = null. The implementation I use (DataNucleus JPA), for example, converts it to entity.NAME IS NULL IIRC.

JPQL doesn't tell an implementation what SQL to execute, just what the user can define. The rest is for the implementation to decide. It's for the user to decide which implementation to use.

myselfmiqdad
  • 2,518
  • 2
  • 18
  • 33
Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
  • Thanks for the answer. Unfortunately, I cannot simply change Hibernate to other JPA implementation. Besides, in my option DataNucleus does not comply fully with JPA standard. http://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbvr states that "Two NULL values are not equal. Comparing two NULL values yields an unknown value." – Tomasz W Dec 11 '14 at 15:57
  • From what I remember the auto-convert they use for "IS NULL" when null parameter is an option, which people can use direct use of parameter literals should they so wish. So compliance is not an issue (and not in all of my usage). Besides they also support "other" datastores and some SQL92 convention has no meaning in those datastores, yet "IS NULL" does. – Neil Stockton Dec 11 '14 at 16:26