How to write SQL "IN" clause in EJB-QL

[Ram Golam]

Hello All,
I want to create a Finder method on an Entity Bean which takes a Collection as an input parameter and returns a Collection.
e.g. Collection findByEmpIDs (Collection empIDs)
The effect should be similar to following SQL statement,
SELECT * FROM employees WHERE emp_id IN (list of employee IDs)
Any ideas? Thanks!
Ram

[Karthik Guru]

dont think this is possible using EJB-QL. The number of parameters to the IN clause s'd be known upfront.
Looks like the only option is to get everything and programmatically filter based on the input collection parameter that you have. :-(

[Jeanne Boyarsky]

If you have a large amount of data in the table and only need a small percentage of the employees, you can loop through using the find by primary key. The overhead from the extra queries would be smaller than the overhead from passing a lot of unneeded data.

[Juan Rolando Prieur-Reza]

Originally posted by Ram Golam:
e.g. Collection findByEmpIDs (Collection empIDs)
The effect should be similar to following SQL statement,
SELECT * FROM employees WHERE emp_id IN (list of employee IDs)

Good question.
This could be written as a correlated query in ordinary SQL.
e.g.
SELECT e
FROM Employees AS e
WHERE e IN ( SELECT t.id FROM NiceEmps.id )
You would have to create a second Entity and table for the purpose
of holding the Collection of employees (or at least their ids).
My example above calls this NiceEmps with an id field.
It would be very nice if EJB-QL would allow this by simply passing on
the IN(SELECT...) without doing anything with it.
This would be the "right" way to do what you are proposing in SQL land. Notice also, you would not have to pass a ?1 parameter unless you need to further constrain the NiceEmps.
Good Luck
[ October 06, 2003: Message edited by: john prieur ]