How to use Hibernate eqOrIsNull ()

Question

I have two rows in MySQL such as

+---------+---------+ | foo | bar | +---------+---------+ | | NULL | | | | +---------+---------+ 

Where the empty lines are "" .

Now I want to get both of them. I use Criteria and Restrictions.eqOrIsNull() for both columns, but always return only one row.

The code is like this

 criteria.add(Restrictions.eqOrIsNull("foo", "")); .add(Restrictions.eqOrIsNull("bar", "")); 

And when I add criteria only on foo , it returns two rows. But for bar it returns only the second, which is empty.

javadoc says Apply an "equal" constraint to the named property. If the value is null, instead apply "is null". Apply an "equal" constraint to the named property. If the value is null, instead apply "is null". So am I mistaken, or should it be used in other ways?

UPDATE :
Sorry, I'm so sloppy. Doc makes that clear. This method works in accordance with the value symbol passed to it, and not the actual value of the named property stored in the database.
Download source package on github :

 public static Criterion eqOrIsNull(String propertyName, Object value) { return value == null ? isNull( propertyName ) : eq( propertyName, value ); } 

So, in my case, eqOrIsNull returns eq("") . I had to use the eq and isNull , as Gregory answered.

Answer 1

Check if this code does what you want -

 criteria.add(Restrictions.or(Restrictions.eq("foo", ""), Restrictions.isNull("foo"))) .add(Restrictions.or(Restrictions.eq("bar", ""), Restrictions.isNull("bar"))); 

This snippet uses the Hibernate 3.5.0-CR-2 API.

Answer 2

Apply an "equal" constraint to a named property. If null, use null instead.

This means that is null will only apply if NULL is passed as a value. If you specify any other string as a value, only equal will be used for this.

This is useful when you are not sure about the actual value passed at runtime as an argument to the parameter value. . In this scenario, in the traditional way, either you need to set non-zero verification criteria and records based on a non-zero condition, or you need to write the criteria as indicated in Gregory's answer.

Remembering all these facts, you should get an answer to your question. You only get strings that contain an empty value, not a NULL value, because you specified an empty string as the second argument. If you specify NULL as the second argument, you will only get strings that are NULL.

Let me know if this is helpful to you.