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    Why doesnt Hibernate translate a null string correctly to  is null   and rather and wrongly creates   null    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

User · Answer

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

User · Answer

this seems to work as wel -    Override public List lt SomeObject gt  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

User · Answer

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

User · Answer

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

User · Answer

You can use   Restrictions eqOrIsNull  status   status    insted of   status    null   Restrictions isNull  status     Restrictions eq  status   status

User · Answer

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

User · Answer

For an actual HQL query   FROM Users WHERE Name IS NULL

User · Answer

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

User · Answer

HQL supports coalesce  allowing for ugly workarounds like   where coalesce c status   no-status     coalesce  status   no-status

User · Answer

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

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

Examples related to java

Examples related to hibernate

Examples related to null