Hibernate Query Returning a Null Value

Question

I'm using Hibernate/JPA to run the query below and I was expecting row 3 in the Table below to get returned when I pass in a null value into the Query. Both the EXPOSURE_TYPE and MESSAGE_FORMAT are defined as a Strings:

MESSAGE_FORMAT      EXPOSURE_TYPE
XSD.001.001.01      1       
XSD.001.001.01      2
XSD.001.001.01      null        <====expecting this to get returned when i set the value to null

My Query:

Query q = em.createQuery("SELECT m FROM Message m WHERE m.messageFormat = :messageFormat AND m.exposureType = :exposureType");
q.setParameter("messageFormat", messageFormat);
q.setParameter("exposureType", exposureType);
try
{
    message = (Message) q.getSingleResult();

}
catch(NoResultException nre){
    //some logging
}
catch(Exception ex){
    //some logging
}
return message;

= null does not work. Read more here : https://stackoverflow.com/questions/2749044/what-is-null-and-is-null — Simon Martinelli, Sep 27 '18 at 09:24
Depending on your database, the syntaxes "thing = null" and "thing is null" are treated differently, as is required by standard SQL. Also `getSingleResult()` requires that an exception is thrown if there are no results, or there is more than one result. A null is never returned, except in a few versions of Hibernate, where that was an unintended regression bug. — coladict, Sep 27 '18 at 09:36

score 1 · Answer 1 · answered Sep 27 '18 at 09:29

1

It's not possible to query for = null you have to use is null.

So if you want to find EXPOSURE_TYPE that is null the query must look like:

EXPOSURE_TYPE is null

-

Read more about that topic here. what is "=null" and " IS NULL"

answered Sep 27 '18 at 09:29

Simon Martinelli

34,053
5
48
82

Thanks for your response, i tried using `AND m.exposureType is :exposureType` but the query throws a NoResultException. Any other ideas? – Orby Sep 27 '18 at 09:37
I was able to get it working using the following Syntax: SELECT m FROM Message m WHERE m.messageFormat = :messageFormat AND m.exposureType is null or m.exposureType = :exposureType" – Orby Sep 27 '18 at 10:03
You cannot use is with a variable – Simon Martinelli Sep 27 '18 at 10:28

Hibernate Query Returning a Null Value

1 Answers1