NULL handling with subselect in Hibernate Criteria API

Question

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?

Update: ----------------------------

Péters idea with the sqlRestriction results in a where clause like this:

        ...
        and (
            nhmcode1_.valid_from = (
                select
                    max(sub_.valid_from) as y0_ 
                from
                    nhm_code sub_ 
                where
                    sub_.valid_from<=? 
                    and sub_.lc_id=this_.id
            ) 
            or (
                nhmcode1_.valid_from is null 
                and sub.validFrom is null
            )
        )
        ...

which in turn result in:

ORA-00904: "SUB_"."VALIDFROM": ungültiger Bezeichner

the error message meaning 'invalid identifier'

Just found this: http://www.opendocs.net/javadoc/hibernate/3/org/hibernate/criterion/Subqueries.html ... maybe that is of some use? — Jens Schauder, Dec 10 '10 at 12:40

score 5 · Answer 1 · edited Apr 07 '10 at 16:46

5

You could try something like this instead of the problematic line:

resultCriterion.add(
  Restrictions.or(
    Restrictions.and(
      Restrictions.isNull("validFrom"),
      Restrictions.sqlRestriction("sub.validFrom is null")
    ),
    Property.forName("validFrom").eq(subselect)
  )
);

This may not work straight away, but hopefully helps.

edited Apr 07 '10 at 16:46

Jens Schauder

77,657
34
181
348

answered Apr 07 '10 at 15:20

Péter Török

114,404
31
268
329

Hi Peter, unfortunately this is only 50% of the solution. To match my requirements it would need to be: resultCriterion.add(Restrictions.or(Restrictions.and( Restrictions.isNull("validFrom"), Restrictions.isNull(subselect)), Property.forName("validFrom").eq(subselect))); but isNull can't take a subquery (and if it would, it would probably duplicate the suquery in the sql which would be ugly, although it wouldn't kill me). – Jens Schauder Apr 07 '10 at 15:33
@Jens I just updated the example to (hopefully) deal with this. – Péter Török Apr 07 '10 at 15:35

Jens Schauder · Accepted Answer · 2012-08-17T18:26:33.053

It really looks like this is one more limitation of the Criteria API.

I found that it is actually not that difficult to create your own Criterion (or set of Criterions) for this kind of thing.

The biggest problem is you'll basically have to go without any documentation. Grab some Implementation which is similar to what you want to do. Tweek it, see what sql it generates, rinse and repeat.

Not fun, but it works.

Sorry I don't have the implementation available for the problem in the question.

NULL handling with subselect in Hibernate Criteria API

2 Answers2