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.

Answer 1

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.