Hibernate criteria: why no Subqueries.isNull(DetachedCriteria)?

Question

I want to translate a script like this into criteria:

SELECT ...
FROM A
WHERE 
  A.some_date > (select bdate from B where ...)
  OR (select bdate from B where ...) IS NULL

So, an A should be returned if either A.Some_date > B.bdate or if B.bdate is NULL.

I was expecting there to be a Subqueries.notNull(DetachedCriteria) (like there is a SubQueries.notExists(DetachedCriteria)) but this method does not exist nor did I find something else to pull this off.

I could of course work around this by returning a count and check if this is > 0 or such but then I need to write 2 identical (except for the Projection) DetachedCriteria's.

Does anyone know if/how to have the is NULL check for the above case or why this isn't provided in the Hibernate criteria API? Perhaps there's a good reason...

Have you found a solution for this? Because I have the same need as you described above — Nathan Q, Mar 08 '12 at 11:58

score 0 · Answer 1 · answered Jun 02 '11 at 04:06

0

I think

Subqueries.eq(null, yourDetachedCriteria)

should work.

answered Jun 02 '11 at 04:06

Alex Gitelman

24,429
7
52
49

tx, I'll give this a try on Monday – Stijn Geukens Jun 04 '11 at 12:06
Unfortunately this didn't work; hibernate translates this into `? = (subquery)` where this should have been `(subquery) IS NULL` – Stijn Geukens Jun 06 '11 at 08:18
1

Hmm. I expected Hibernate to figure it. I guess it can't do it with prepared statements. I think it tries to use `org.hibernate.criterion.SimpleSubqueryExpression` which only does operation. But you can try to create your own `org.hibernate.criterion.SubqueryExpression` that would generate `is null` properly. It should be fairly simple. I suggest looking in the source for `org.hibernate.criterion.SimpleSubqueryExpression`. – Alex Gitelman Jun 06 '11 at 19:08
Tx, already thought about doing that, just seems strange to me that this isn't provided OOTB. Maybe I'll create a jira issue for this. – Stijn Geukens Jun 07 '11 at 19:28
There may be something we don't know. Ask on their forum as well. On the other hand, it does not sound as super typical use case. – Alex Gitelman Jun 07 '11 at 19:35
@AlexGitelman that's a good idea. according to you suggesstion,I write two class that show in the next answer. and it works great well – jack jin Jan 28 '21 at 04:32

score 0 · Accepted Answer · answered Jan 28 '21 at 04:38

class MySubqueries:

public class MySubqueries {
    public static Criterion isNull(DetachedCriteria dc) {
        return new IsNullSubqueryExpression(null, null, dc);
    }
}

class IsNullSubqueryExpression:

public class IsNullSubqueryExpression extends SubqueryExpression {

    protected IsNullSubqueryExpression(String op, String quantifier, DetachedCriteria dc) {
        super(op, quantifier, dc);
    }

    @Override
    protected String toLeftSqlString(Criteria criteria, CriteriaQuery criteriaQuery) {
        return "";
    }

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return super.toSqlString(criteria, criteriaQuery) + " IS NULL";
    }
}

use:

detachedCriteria.add(MySubqueries.isNull(subDetachedCriteria))

It's never too late for a solution :) – Stijn Geukens Feb 11 '21 at 13:45 — Stijn Geukens, Feb 11 '21 at 13:45

Hibernate criteria: why no Subqueries.isNull(DetachedCriteria)?

2 Answers2