Handling null parameters in JPQL

Question

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?

Answer 1

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.