Hibernate: How to set NULL query-parameter value with HQL?

Question

How can I set a Hibernate Parameter to "null"? Example:

Query query = getSession().createQuery("from CountryDTO c where c.status = :status  and c.type =:type")
.setParameter("status", status, Hibernate.STRING)
.setParameter("type", type, Hibernate.STRING);

In my case, the status String can be null. I have debugged this and hibernate then generates an SQL string/query like this ....status = null... This however does not Work in MYSQL, as the correct SQL statement must be "status is null" (Mysql does not understand status=null and evaluates this to false so that no records will ever be returned for the query, according to the mysql docs i have read...)

My Questions:

1. Why doesnt Hibernate translate a null string correctly to "is null" (and rather and wrongly creates "=null")?

2. What is the best way to rewrite this query so that it is null-safe? With nullsafe I mean that in the case that the "status" String is null than it should create an "is null"?

Harlow

For allo those interested in a solution I think the Criteria API is one way to go. But I am still not really convinced as it bloads the code horribly and using HQL would be much more cleaner. Maybe the real Solution is in implementing your own Hibernate Type (I have implemented one for ENUMs but these self-implemented Types, at least the basic ones have big disadvantages over the bulid in hibernate types in Querying with HQL (unless you also extend the HQL parser?) what makes this a huge projekt and requires a lot of Hibernate knowledge...(continued in part 2) – Harlow

Part2: Maybe the easiest (on the other hand defenitely NOT a Best Practice) is directly editing the Hibernate String Type class itself and add the missing logic. This should only be a view lines to check for null String values and act accordingly... – Harlow

Just ran into this too...my god this is braindead. – Zincography

I seriously hate hibernate, this is absolutely stupid – Decade

So all these years later, is there a solution to this yet without using the Criteria API? – Osorio

@Osorio I cannot believe that there isn't yet! – Haul

Answer 1

1. I believe hibernate first translates your HQL query to SQL and only after that it tries to bind your parameters. Which means that it won't be able to rewrite query from param = ? to param is null.

2. Try using Criteria api:

   Criteria c = session.createCriteria(CountryDTO.class);
   c.add(Restrictions.eq("type", type));
   c.add(status == null ? Restrictions.isNull("status") : Restrictions.eq("status", status));
   List result = c.list();
   

Massotherapy

Hello Sergey, Thanks for your answer. I actually implemented it (already before you posted your answer) also with the Criteria API. But I nevertheless think this is not the "ideal solution" to the problem. Ok to be honest, sometimes "oddities/bugs" do not have a real good solution....I have marked your answer as the "solution" to to problem. – Harlow

you are welcome :) but what do you expect from ideal solution? if you are fine with criteria api instead of hql, you can always create your own factory method for 'null-safe' Criterion. – Massotherapy

Great answer! This simple fix helped me to query against a nullable field without resorting to string-building the query! – Queridas

Answer 2

This is not a Hibernate specific issue (it's just SQL nature), and YES, there IS a solution for both SQL and HQL:

@Peter Lang had the right idea, and you had the correct HQL query. I guess you just needed a new clean run to pick up the query changes ;-)

The below code absolutely works and it is great if you keep all your queries in orm.xml

from CountryDTO c where ((:status is null and c.status is null) or c.status = :status) and c.type =:type

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.

Notes:

The issue may be a specific MySql quirk. I only tested with Oracle.

The above query assumes that there are table rows where c.status is null

The where clause is prioritized so that the parameter is checked first.

The parameter name 'type' may be a reserved word in SQL but it shouldn't matter since it is replaced before the query runs.

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

from CountryDTO c where (:status is null or c.status = :status) and c.type =:type

and it is equivalent to:

sql.append(" where ");
if(status != null){
  sql.append(" c.status = :status and ");
}
sql.append(" c.type =:type ");

Neuroticism

why not from CountryDTO c where (:status is not null and c.status = :status) and c.type =:type ? – Nelan

@Nelan With or the query will yield: from CountryDTO c where (true) and c.type =:type and yours will yield: from CountryDTO c where (false) and c.type =:type, which will give you no rows. – Neuroticism

@Nelan You can also try this: select 'data' from dual where (1=2) and this: select 'data' from dual where (1=1) to see the difference. – Neuroticism

Answer 3

The javadoc for setParameter(String, Object) is explicit, saying that the Object value must be non-null. It's a shame that it doesn't throw an exception if a null is passed in, though.

An alternative is setParameter(String, Object, Type), which does allow null values, although I'm not sure what Type parameter would be most appropriate here.

Jovita

Hello skaffman. Thank you very much for your help! When asking the question I actually thought I am just to silly to find a solution. But its seems that this is a real "oddity" of hibernate. Most likely I will have to write my own Nullable-String-Type or I will have to switch to Criteria Queries... After digging deeper it seems that the current implementation of the Hibernat.STRING Type is not nullable: docjar.com/html/api/org/hibernate/type/StringType.java.html. (But shouldn't the MySQL JConnector implement the PreparedStatment.setString correctly??) Thanks Tim. – Harlow

Just for information, it's actually a "feature" of JDBC: bugs.sun.com/bugdatabase/view_bug.do?bug_id=4312435 Hibernate uses JDBC's PreparedStatement.setNull() to bind null parameters – Supranational

Answer 4

It seems you have to use is null in the HQL, (which can lead to complex permutations if there are more than one parameters with null potential.) but here is a possible solution:

String statusTerm = status==null ? "is null" : "= :status";
String typeTerm = type==null ? "is null" : "= :type";

Query query = getSession().createQuery("from CountryDTO c where c.status " + statusTerm + "  and c.type " + typeTerm);

if(status!=null){
    query.setParameter("status", status, Hibernate.STRING)
}


if(type!=null){
    query.setParameter("type", type, Hibernate.STRING)
}

Paratroops

Looks like old sql query building to me. Stick to the criteria api for stuff like this. Hand written hql is extremely error prone. – Oxpecker

Answer 5

HQL supports coalesce, allowing for ugly workarounds like:

where coalesce(c.status, 'no-status') = coalesce(:status, 'no-status')

Vostok

Answer 6

I did not try this, but what happens when you use :status twice to check for NULL?

Query query = getSession().createQuery(
     "from CountryDTO c where ( c.status = :status OR ( c.status IS NULL AND :status IS NULL ) ) and c.type =:type"
)
.setParameter("status", status, Hibernate.STRING)
.setParameter("type", type, Hibernate.STRING);

Baseline

Hellp Peter, thank you very much for you help! I dont know why, but it seems that hibernate always evaluates the ( c.status = :status OR ( c.status IS NULL AND :status IS NULL ) to c.status = null (according to my mysql query log). I also changed the order to (( c.status IS NULL AND :status IS NULL ) OR c.status = :status) with the same result. Thanks Tim – Harlow

Answer 7

For an actual HQL query:

FROM Users WHERE Name IS NULL

Greed

Answer 8

You can use

Restrictions.eqOrIsNull("status", status)

insted of

status == null ? Restrictions.isNull("status") : Restrictions.eq("status", status)

Pinite

Answer 9

Here is the solution I found on Hibernate 4.1.9. I had to pass a parameter to my query that can have value NULL sometimes. So I passed the using:

setParameter("orderItemId", orderItemId, new LongType())

After that, I use the following where clause in my query:

where ((:orderItemId is null) OR (orderItem.id != :orderItemId))

As you can see, I am using the Query.setParameter(String, Object, Type) method, where I couldn't use the Hibernate.LONG that I found in the documentation (probably that was on older versions). For a full set of options of type parameter, check the list of implementation class of org.hibernate.type.Type interface.

Hope this helps!

Pedaiah

Answer 10

May be someone are looking for this

sqlQuery.setParameter("paramName", null, StandardBasicTypes.INTEGER)

Annoyance

Answer 11

this seems to work as wel ->

@Override
public List<SomeObject> findAllForThisSpecificThing(String thing) {
    final Query query = entityManager.createQuery(
            "from " + getDomain().getSimpleName() + " t  where t.thing = " + ((thing == null) ? " null" : " :thing"));
    if (thing != null) {
        query.setParameter("thing", thing);
    }
    return query.getResultList();
}

Btw, I'm pretty new at this, so if for any reason this isn't a good idea, let me know. Thanks.

Approachable

Shouldn't you be using the is operator: i.e. ... + " t where t.thing " + ((thing == null) ? "is null" : "= :thing") – Foyer

Could you elaborate on the difference between those two? – Approachable

The use of = null will cause an exception. Although it may depend on which dbms you run this. In SQL you have the same issue, also there you have to use is null and not = null. The thing with null is that some databases regard it not really as a value for emptyness, but rather as an undetermined value, meaning that null does not equal null. Therefor the null = null would never be true. See: #5066992 – Foyer