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

jpql
Mike picture Mike · Dec 19, 2010 · Viewed 47.8k times · Source

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

Nayan Wadekar picture Nayan Wadekar · Dec 19, 2010

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.

Related questions

Adding IN clause List to a JPA Query

I have built a NamedQuery that looks like this: @NamedQuery(name = "EventLog.viewDatesInclude", query = "SELECT el FROM EventLog el WHERE el.timeMark >= :dateFrom AND " + "el.timeMark <= :dateTo AND " + "el.name IN (:inclList)") What I want to do is …

Read More
JPQL IN clause: Java-Arrays (or Lists, Sets...)?

I would like to load all objects that have a textual tag set to any of a small but arbitrary number of values from our database. The logical way to go about this in SQL would be to build an "…

Read More
IN-clause in HQL or Java Persistence Query Language

I have the following parametrised JPA, or Hibernate, query: SELECT entity FROM Entity entity WHERE name IN (?) I want to pass the parameter as an ArrayList<String>, is this possible? Hibernate current tells me, that java.lang.ClassCastException: …

Read More