How to query a column which value is null in JPA?

Question

I am using JPA namedQuery to select data from DB.

 @NamedQuery(name = "Concept.findByRefTableNull", query = "SELECT c FROM Concept c WHERE c.conceptName = :conceptName and c.refTable = :refTable"),

///
 List<Concept> attributeList 
                = em.createNamedQuery("Concept.findByRefTableNull")
                .setParameter("conceptName", "student").
                setParameter("refTable", null).
                getResultList();

        System.out.println(attributeList.size()); //return 0

The List size is 0, but I am sure it should have records. The reason is the refTable. How to query a column which value is null in JPA ?

Thanks.

score 38 · Accepted Answer · answered Jun 12 '11 at 22:51

38

Just change your query to

 @NamedQuery(name = "Concept.findByRefTableNull", query = "SELECT c FROM Concept c WHERE c.conceptName = :conceptName and c.refTable IS NULL"),

answered Jun 12 '11 at 22:51

adarshr

61,315
23
138
167

score 1 · Answer 2 · answered Mar 07 '16 at 09:42

1

I'm not sure if this is a Spring Data JPA feature only but I just write a method in the repository interface of the form findByFieldName(Type field) where fieldName is the name of the column I need to do a null check on. I then use this method and pass null as a parameter.

answered Mar 07 '16 at 09:42

Samantha Catania

5,116
5
39
69

That won't work when your argument data type is a primitive one. – Raj Nov 30 '16 at 12:30
@Raj Yes, this is why most people recommend not to use primitive data types for fields in entity classes. However, for primitives one needs to define defaults like e.g. 0 or -1 for integers, and let JPA search for it. – PeMa Nov 16 '18 at 13:34

score 1 · Answer 3 · answered Jan 08 '19 at 12:24

1

You can use Criteri Api , like this :

private Specification<Object> nullData(String field) {
    return (root, query, cb) ->
            cb.isNull(cb.lower(root.get(field)));
}

answered Jan 08 '19 at 12:24

saeedeh

343
2
7

score -1 · Answer 4 · answered Apr 19 '13 at 09:04

-1

i would avoid Queries altogether - it pretty much negates the sense of persistence-abstraction. Create dynamic queries via the Criteria API instead, this will ensure portability AND you will be able to switch databases to some extent without much hassle

answered Apr 19 '13 at 09:04

name

41
2

9

Having JPQL queries is still an abstraction layer and allows you to switch the database as well as when using criteria. – noone Jan 17 '15 at 17:38

How to query a column which value is null in JPA?

4 Answers4

Linked