Invalid generated SQL for Restriction.in()

calin.pavel · **Joined:** Thu May 18, 2006 6:25 am **Posts:** 4

It seems that the SQL generated by Hibernate in case of using
criteria.add(Restriction.in("id", collection));
to filter data, can not be executed by databases if the collection is NULL or EMPTY.

This happens because the generated SQL looks like:
select * from USERS where id in ()

and this is for sure not supported by SQL syntax.

Does anybody have any idea how could I change the code to have the same functionality (select must not return any record)?

For this test I used:
Hibernate 3.0.5
MySQL 4.0.12

r1ch · **Joined:** Wed Aug 25, 2004 6:23 am **Posts:** 91

That's kind of a weird requirement, but how about:

Restrictions.eq(Integer.valueOf(1), Integer.valueOf(0)),

calin.pavel · **Joined:** Thu May 18, 2006 6:25 am **Posts:** 4

Yes, may be this sample looks very stupid because I wanted to emphasize the generation of the SQL.

But in reality, I used that piece of code to write a general filtering mechanism that applies for any kind of entity/class.
And in that case I don't know the TYPE of the attribute that I filter. The only thing I know is the name of the attribute (member of Hibernate class) as string and the vaue to compare with (in case of IN operation is a collection).

For my special scenario with IN operation I tried to create a collection with some dummy values but don't know what objects to put in (what type and what values).
That means I have to generate an object of a type which for sure does not fulfill my condition. Eg: in the sample provided by you if I have an user with id=1 that user will be returned, but I don't want to get back any ser.
select * from user where id in () -- I expect to return no records

yinli.xiao · **Joined:** Wed May 17, 2006 12:07 pm **Posts:** 4

I met the same problem in my program.
I wanted to use Restrictions.in to filter my result set.

crit.add(Restrictions.in( "study", studies ));

But when the studies parameter is empty, a SQL exception throws out.
Since I don't know when the "studies" is null or not null. I have to add additional logic to check wether "studies" is null, if it is null then return null as well. Do you think it is a ok way to do that?

CWitherow · **Posted:** Tue May 23, 2006 2:25 pm

calin,

You said have a general filtering mechanism so how about:

Code:

if ((collection == null) || (collection.size() == 0)) {
    // Null or empty collection, so no results should be returned.
    crit.add( Restrictions.sqlRestriction( "0 = 1" ) );
} else {
    crit.add( Restrictions.in( "propName", collection ) );
}

That way, you don't need to know the object type of the property.

Good luck,
Curtis ...

calin.pavel · **Joined:** Thu May 18, 2006 6:25 am **Posts:** 4

Thank you for your suggestion. It's really a good idea.

jbosseur · **Posted:** Wed May 24, 2006 8:11 am

But to be honest I think it would be better if hibernate would resolve this for us.....