Filtering on a null value

aaronpau · **Joined:** Tue Jan 29, 2008 6:07 pm **Posts:** 5

I am trying to determine how to use null values in filters with Hibernate. Currently, I have a lookup table with two key columns: NAME1 and NAME2. NAME1 is required, while NAME2 is an optional (nullable) column. In certain scenarios, I want to query from the table where NAME1 has a value and NAME2 is null. From the code snip below, you can see that I set the "name2" filter parameter to null.

Unfortunately, when I run this, it throws an exception in the validate() method of the org.hibernate.impl.FilterImpl class. The method is testing the result from Map.get (specifically HashMap.get) for a null return value to determine if the value for each filter parameter is set. However, null is a VALID return value from HashMap.get -- see below:

JavaDoc from HashMap.get (emphasis mine) wrote:

Returns the value to which the specified key is mapped in this identity hash map, or null if the map contains no mapping for this key. A return value of null does not necessarily indicate that the map contains no mapping for the key; it is also possible that the map explicitly maps the key to null. The containsKey method may be used to distinguish these two cases.

In other words, org.hibernate.impl.FilterImpl.validate() is incorrect should be using the containsKey function to determine if the value has been set. Please suggest an alternative solution. Also, please fix this in a future version of Hibernate.

Hibernate version: 3.2.0 GA

Mapping:

Code:

<class name="NameLookup" table="NAME_LOOKUP">
 <composite-id name="id" class="NameLookupId">
  <key-property name="name1" column="NAME1" type="string"/>
  <key-property name="name2" column="NAME2" type="string"/>
 </composite-id>
 <property name="value" column="VALUE" type="string"/>
 <filter name="searchFilter" condition= "NAME1 = :name1 and NAME2 = :name2"/>
</class>
...
<filter-def name="searchFilter">
   <filter-param name="name1" type="string"/>
   <filter-param name="name2" type="string"/>
</filter-def>

Code:

Code:

session.enableFilter("searchFilter")
  .setParameter("name1", "test")
  .setParameter("name2", null);

Stack trace:

Quote:

org.hibernate.HibernateException: Filter [searchFilter] parameter [name2] value not set
at org.hibernate.impl.FilterImpl.validate(FilterImpl.java:145)
at org.hibernate.impl.SessionImpl.getEnabledFilters(SessionImpl.java:1063)
...

Name and version of the database you are using:Oracle 10i

ttimbul · **Joined:** Sat May 31, 2008 4:38 am **Posts:** 1

If you want to check for NULL, then wouldn't you need to rewrite your filter as:

Code:

:name1 = NAME1 and NAME2 IS NULL

In which case the validate() method does exactly what it's supposed to: warn you of potential error.

Cameron McKenzie · **Posted:** Mon Jun 09, 2008 9:52 am

Well, sometimes, approaching the problem from a slightly different angle can be helpful. One thing I might even recommend is just using the Criteria API, and leveraging the Hiberante Restrictions class.

http://www.hibernate.org/hib_docs/v3/api/org/hibernate/criterion/Restrictions.html

This class provides methods like isNull and isNotNull, and generates SQL queries based upon all of the various Criterion you need to use.

Here's a little tutorial on the Hiberante3 Criteria API for your viewing:

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi