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?
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?
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 (''
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With