CODEWITHSUNDEEP
javahibernatenullhql
Shayan
Shayan

Reputation: 2828

Null in HQL Expressions

I want to know if HQL expressions are null-safe or not? For example, consider this named query

SELECT a
FROM A a
where a.f=:f

in which f is a field of type String, Double, Date, etc. Then I use it like:

session.getNamedQuery("myNamedQuery").setString("f", myFValue).uniqueResult();

If both a.f and myFValue are null I want the condition to be true and if just one of them is null I want it to be false.

If it is not null-safe, how can I handle that?

Regards

Upvotes: 3

Views: 7889

Answers (5)

egallardo
egallardo

Reputation: 1284

They are not. Try these scenarios out to handle your specific case:

select a from A a where ((:f is null and a.f is null) or a.f = :f) and ...

If your parameter String is null then the query will check if the row's status is null as well. Otherwise it will resort to compare with the equals sign.

If you needed to skip the :status where_clause altogether; you can code like so:

select a from A a where (:f is null or a.f = :f) and ...

This second query is equivalent to:

if(status != null){
  sql.append(" a.f = :f and ");
}

Upvotes: 2

Danny Stevens
Danny Stevens

Reputation: 41

They are not null safe. Using criteria this would do the trick

public static void addNullSafeEqualsRestriction(Criteria criteria, String propertyName, Object object) {
    if (object == null) {
        criteria.add(Restrictions.isNull(propertyName));
    } else {
        criteria.add(Restrictions.eq(propertyName,object));
    }
}

Upvotes: 0

Suresh Atta
Suresh Atta

Reputation: 122026

They are not null safe.

HQL translates your HQL query to SQL and then it substitute your parameters.SO it won't rewrite query from param = ? to param is null.

Go for Criteria API and use Restrictions.isNull("f");

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 692271

No, they aren't null-safe. They're translated directly to SQL, and obey the same rules. So if yo want to test for null, you must use is [not] null.

So if the f parameter can be null, you'll have to use two different HQL queries, or build it dynamically, or use a Criteria query to build a dynamic query.

Upvotes: 1

LaurentG
LaurentG

Reputation: 11827

You should use is null or is not null. See HQL "is null" And "!= null" on an Oracle column

Upvotes: 0

Related Questions