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
  • 2,073
  • 2
  • 20
  • 39
tarka
  • 5,289
  • 10
  • 51
  • 75

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,404
  • 2
  • 26
  • 41
DannyMo
  • 11,344
  • 4
  • 31
  • 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