Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

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

 
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
 
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. :-(
 
author & internet detective
Posts: 40035
809
Eclipse IDE VI Editor Java
  • 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.
 
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 ]
 
This will take every ounce of my mental strength! All for a tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic