NULL handling with subselect in Hibernate Criteria API
- by Jens Schauder
I'm constructing a Hibernate Criterion, using a subselect as follows
DetachedCriteria subselect =
DetachedCriteria.forClass(NhmCode.class, "sub"); // the subselect selecting the maximum 'validFrom'
subselect.add(Restrictions.le("validFrom", new Date())); // it should be in the past (null needs handling here)
subselect.add(Property.forName("sub.lifeCycle").eqProperty("this.id")); // join to owning entity
subselect.setProjection(Projections.max("validFrom")); // we are only interested in the maximum validFrom
Conjunction resultCriterion = Restrictions.conjunction();
resultCriterion.add(Restrictions.ilike(property, value)); // I have other Restrictions as well
resultCriterion.add(Property.forName("validFrom").eq(subselect)); // this fails when validFrom and the subselect return NULL
return resultCriterion;
It works ok so far, but the restriction on the last line before the return statement is false when validFrom and subselect result in NULL.
What I need is a version which handles this case as true. Possibly by applying a NVL or coalesce or similar.
How do I do this?
