aspose file tools*
The moose likes EJB and other Java EE Technologies and the fly likes How to write SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "How to write SQL "IN" clause in EJB-QL" Watch "How to write SQL "IN" clause in EJB-QL" New topic
Author

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

Ram Golam
Greenhorn

Joined: Oct 04, 2003
Posts: 1
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

Joined: Mar 06, 2001
Posts: 1209
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

Joined: May 26, 2003
Posts: 30752
    
156

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Juan Rolando Prieur-Reza
Ranch Hand

Joined: Jun 20, 2003
Posts: 236
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 ]

Juan Rolando Prieur-Reza, M.S., LSSBB, SCEA, SCBCD, SCWCD, SCJP/1.6, IBM OOAD, SCSA
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to write SQL "IN" clause in EJB-QL