JPQL query --- how to use 'is null'

Question

i use the following query in JPQL to query the people whose address column is empty.

List rl = em.createQuery( "select o from Person as o where o.address IS NULL" ).setFirstResult( 0).setMaxResults( 50).getResultList(); ...

this line of code always return an empty list, obviously the table does has entries that match the condition.

class Person { Address address; String name; ... } class Address { String name; ... }

anyone knows what's wrong with this jpql statement? thanks in advance.

by the way, i find that when i use "is not null", it will generate the right result list. but if use "is null", every time it will generate nothing at all — Mike
are you using @OneToMany & what are referential integrity constraints. — Nayan Wadekar
yes, does that affact JPA query? as below: @ManyToOne( fetch = FetchType.EAGER,cascade = { CascadeType.PERSIST, CascadeType.MERGE } ) @Column( name = "AddressID" ) private Address address; — Mike

Nayan Wadekar Nayan Wadekar · Accepted Answer · 2010-12-19T17:58:08

As mentioned, address column is empty, then try using IS EMPTY expression instead of IS NULL.

em.createQuery( "SELECT o FROM Person o where (o.address.id IS NULL OR o.address.id = 0").setMaxResults(50).getResultList();

Check constraint according to id's datatype.

Also there is no need to mention setFirstResult(0) as it is not going to skip any results & without it, by default all matching results will be fetched.

JPQL query --- how to use 'is null'

1 Answers