• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Ram Golam
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1209
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 237
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic