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

Question

Hello,

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 Hibernat 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"?

Thank you very much! Tim

Answer 1

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.

Answer 2

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

Answer 3

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();

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