How to write a finder query that takes a collection paramete

[jwiegley]

I have an older BES codebase which I'm trying to port to JBoss. Since that codebase targets EJB CMP 1.x -- and has no concept of local interfaces, etc. -- the best approach so far seems to be to get JBoss to deploy those 1.x beans as they are.

However, BES allows for finder queries like this:

<finder>
 <method-signature>findByResourceList( String[] asResourceIDList )</method-signature>
 <where-clause> MyResourceID in ( :asResourceIDList ) </where-clause>
</finder>

I thought that in my jbosscmp-jdbc.xml file, I could just write this to emulate the above:

<query>
 <query-method>
 <method-name>findByResourceList</method-name>
 <method-params>
 <method-param>java.lang.String[]</method-param>
 </method-params>
 </query-method>
 <declared-sql>
 <where>ID in ({0})</where>
 </declared-sql>
</query>

However, while this builds, deploys and executes without a hitch, it does not find anything. I'm not sure what substitution syntax {0} results in in this case.

Anyway, can anyone offer a clue as to how to pass a collection as a finder parameter, and then query my database to check for any entries whose ID matches any member of that collection?

Thanks,
John

[roist]

you may want to check in your server.log (which is quite helpful to me, to understand finding and optimized loading-issues) that the resulting prepared-statement is indeed not what you wanted.

the resulting statement will be ... where ID IN (?), which can't work, since you'd like to have where ID IN(?,?,?) and every parameter filled in. else your db will believe its just one parameter of the ID#type.

a quick and dirty workaround: concatenate the string with some delimiter before passing it to the finder, and write a little database function that explodes your delimiter-seperated list.

best would of cause be to set up cmr properly - but this seems like hard work for a whole application.

oracle-compatible where clause+impl:

public xxx findByResourceList (String[] asResourceIDList) {
 StringBuffer res = new StringBuffer();
 res.append(',');
 for(String s: asResourceIDList)
 res.append(s).append(',');
 return findByResourceListDummy(res.toString());
}

<query>
 <query-method>
 <method-name>
 findByResourceListDummy
 </method-name>
 <method-params>
 <method-param>java.lang.String</method-param>
 </method-params>
 </query-method>

 <declared-sql>
 <where>instr({0}, ',' || id || ',')>0</where>
 </declared-sql>
</query>

though i'm not sure if you can do that function stuff here, it should be possible somewhere;)