javahibernatecriteriadetachedcriteria

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


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...


Solution

  • 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))