2

The following is an extract from a larger Criteria Query:

...
Expression literal = builder.literal((String) "%" + value + "%");

List<Predicate> globalPredicate = new ArrayList<>();

globalPredicate.add(builder.equal(subject.get(Subject_.email), literal));

predicates.add(builder.or(globalPredicate.toArray(new Predicate[globalPredicate.size()])));  
...

There is a condition that exists where I explicitly want to pass in null and find the Subject_.email who are null. I tried passing in null and found that the criteria API wants a nullLiteral(). I tried conditionally replacing literal with nullLiteral() when it was appropriate but then I get nothing returned at all.

What is the best practice way of creating a query that will match null values for the specified attribute in the DB?

snieguu
  • 1,945
  • 2
  • 15
  • 33
tarka
  • 4,997
  • 10
  • 47
  • 74

2 Answers2

3

I normally use Expression#isNull(). Using your example:

globalPredicate.add(subject.get(Subject_.email).isNull());

As far as whether or not this is considered "best practice", it is demonstrated in Oracle's Java EE Tutorial 40.3.5.1 The Expression Interface Methods.

thomas.mc.work
  • 6,314
  • 2
  • 25
  • 40
DannyMo
  • 10,284
  • 4
  • 30
  • 37
  • Ok, but `null` is not obligatory it's an optional value. The rest of the time I need to pass in a literal. So are you saying that the best way is to conditionally switch between my statement and yours if I detect a `null` value. – tarka Aug 14 '13 at 20:15
  • Yep, that's what I suggest. I suspect the other approaches get translated into something along the lines of `email = NULL` [which is not what you want](http://stackoverflow.com/questions/9581745/sql-is-null-and-null) in the case where the value passed in is `null`. You could verify this by inspecting the generated native queries. – DannyMo Aug 14 '13 at 20:28
-1

Null or NotNull can be used om criteriaQuery like below:

// IS [NOT] NULL
    Predicate n1 = cb.isNull(name);
    Predicate n2 = cb.isNotNull(name);

Use this link for reference : http://www.objectdb.com/java/jpa/query/jpql/comparison

Bot
  • 1
  • 1