Hibernate HQL compare timestamp to null

Question

I have the following HQL and I would like to know how it handles comparing date column to null. For example what happens in case the givenTime parameter is null. I checked and got empty results. Is it always the case? is it documented?

select mo from MyClassMO mo
where mo.creationDate is not null and mo.creationDate >= (:givenTime)

and what if the givenTime replaced with inner select query which returns null? thanks

if givenTime is NULL you must manage manually. – Joe Taras Nov 18 '16 at 08:39 — Joe Taras, Nov 18 '16 at 08:39

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

I know you asked about hql but for handling such a cases I prefer using something called Criteria in hibernate. It can be mixed up easy with SQL.

JPA and Hibernate - Criteria vs. JPQL or HQL

Criteria criteria = session
            .createCriteria(MyClassMO.class)
               .add(Restrictions.isNotNull("creationDate"));
if (givenTime!=null){
    criteria.add(Restrictions.ge("creationDate", givenTime));//ge - greater equals
}
List<MyClassMO> result = criteria.list();

edited May 23 '17 at 11:59

Community

1
1

answered Nov 18 '16 at 09:23

daredesm

597
2
7
22

and what if the givenTime replaced with inner select query which returns null? – user1116377 Nov 20 '16 at 14:47

Joe Taras · Answer 2 · 2016-11-21T07:56:41.840

If your parameter is nullable, you must manage it manually.

In base of your applied logic, you can write the query.

I suppose some cases (tell me if your is one of these or another).

Case 1: Not consider givenTime in filter

You can wirte this query:

String hql = "select mo from MyClassMO mo" + 
     " where mo.creationDate is not null";

if (givenTime != null) {
    hql += " and mo.creationDate >= (:givenTime)";
}

Case 2: if givenTime is null, put the current date

String hql = "select mo from MyClassMO mo" + 
    " where mo.creationDate is not null " +
    " and mo.creationDate >= COALESCE(:givenTime, current_date())";

Case 3: if givenTime is in subquery returns null, put the current date

String hql = "select mo from MyClassMO mo" + 
    " where mo.creationDate is not null " +
    " and mo.creationDate >= "
    " (SELECT COALESCE(:giventime, current_date()) " +
    "  FROM yourtable WHERE conditions";

and what if the givenTime replaced with inner select query which returns null? — user1116377, Nov 20 '16 at 14:47
If the givenTime is NULL with coalesce you'll get the current date. — Joe Taras, Aug 03 '18 at 14:49

Hibernate HQL compare timestamp to null

2 Answers2