How does hibernate use an empty string to limit equality?

Question

I have a column that potentially has some bad data, and I can't clear it, so I need to check for a null or empty row. I am making a request for Hibernation Criteria, so I have the following that is returning incorrectly right now:

Session session = getSessionFactory().openSession(); Transaction tx = session.beginTransaction(); Criteria myCriteria = session.createCriteria(Object); ... myCriteria.add(Restrictions.or(Restrictions.isNull("stringColumn"), Restrictions.eq("stringColumn", ""))); List<Objects> list = myCriteria.list(); 

I cannot get it to correctly return the expected results. Since in the experiment, I changed the second restriction on reading:

  Restrictions.eq("stringColumn", "''") 

And it started to return the expected results, so hibernate incorrectly translates my empty string (e.g. ") into an empty SQL string (e.g. ''), or am I just doing it wrong?

Answer 1

With HSQL (and Derby) and the following values:

  insert into FOO values ​​(1, 'foo');
 insert into FOO values ​​(2, 'bar');
 insert into FOO values ​​(3, NULL);
 insert into FOO values ​​(4, '');

Request Criteria

 Criteria crit = session.createCriteria(Foo.class); crit.add(Restrictions.or(Restrictions.isNull("name"), Restrictions.eq("name", ""))); crit.list(); 

returns:

 Foo [id=3, name=null] Foo [id=4, name=] 

As expected.

What database are you using? Could this be Oracle?

Answer 2

It seems you are doing it wrong. null in Java maps to null in SQL and an empty string ( "" ) in Java maps for empty rows in SQL ( '' )