How does hibernate use an empty string for an equality restriction?

Question

I have a column that potentially has some bad data and I can't clean it up, so I need to check for either null or empty string. I'm doing a Hibernate Criteria query so I've got the following that returns 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 can't get it to properly return the results I'd expect. So as an experiment I changed the second restriction to read:

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

And it started returning the expected results, so is hibernate incorrectly translating my empty string (e.g. "") into a SQL empty string (e.g. ''), or am I just doing this 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, '');

You criteria query

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 it be Oracle?

Answer 2

It seems like you're doing it wrong. null in Java maps to NULL in SQL, and empty String ("") in Java maps to empty string in SQL ('')