I need to compare a nullable entity property via IN expression within the following JPQL query:
@NamedQuery(name = "query",
query = "SELECT e FROM MyEntity e WHERE e.status IN :statuses")
Now, I like the shown collection-valued input parameter statuses
to optionally contain null
as an element:
final List<MyEntity> actual = entityManager.createNamedQuery("query", MyEntity.class)
.setParameter("statuses", Arrays.asList(null, 1L))
.getResultList();
However with Hibernate/Derby an actual result list only contains entities with status 1L
but not null
.
I have not found anything in the JPA 2.2 specification about this case. Did I miss something or is this vendor-specific?
The answers to this question only solve part of my problem. In their proposed solutions, the null
comparison is hard-baked into the query and cannot be controlled via the collection-valued parameter.
CodePudding user response:
As a Java programmer, where null = null
yields true it might come as a surprise that in SQL (and JPQL) null = null
is itself null
which is "falsy". As a result, null in (null)
yields null as well.
Instead you need to treat null
seperately with a IS NULL
check: e.status IS NULL OR e.status IN :statuses
.
This is described in 4.11 Null Values of the JPA Specification:
- Comparison or arithmetic operations with a NULL value always yield an unknown value.
- Two NULL values are not considered to be equal, the comparison yields an unknown value.