Hi there,

I've got the following problem:
When I'm making a query using 'Criteria' and 'Restrictions', some objects are not retrieved at all , when a criteria field is null.
For example:

in a table EMPLOYEE (name, age, salary), I want to select an emplyee called 'Jean' (who exists), but 'salary' is null.

-----
List emps = session.createCriteria(Employee.class)
.add( Restrictions.eq("name",<name.property>) )
.add( Restrictions.eq( "salary", <salary.property> ) )
.list();
-----
But 'emps' remains an empty list.
And when I use following code:

-----
List emps = session.createCriteria(Employee.class)
.add( Restrictions.eq("name",<name.property>) )
.add( Restrictions.or(Restrictions.eq( "salary", <salary.property>), Restrictions.isNull(<salary.property>))) )
.list();
-----

All employees having a null salary are also retrieved.
Can anyone help? Thanks !!!


My environment:

Hibernate version: 3.0.5

Name and version of the database you are using: Oracle9i Enterprise Edition Release 9.2.0.5.0

Hi,

Hibernate wants you to check the null values. If the values are null then don't add it to the criteria.

i.e)
Criteria criteria = session.createCriteria(Employee.class)
if(<name.property> !=null)
criteria.add( Restrictions.eq("name",<name.property>) )
if(<salary.property> !=null)
criteria.add( Restrictions.eq( "salary", <salary.property> ) )
List emps = criteria.list();

Hi ramnath,

thanks for your reply.
The problem is not that the java property is null (it's not), but that DB content is null.
I hope you know what I'm talking about.
Thanks for your help

Hi,

I suppose you would have used SQL before for inserting the values in the table. Hibernate will not insert null values in the DB.

probably you will have to delete the null values from the DB.

I suppose you should have got a CGLIB error if you had a null values on the load query.