aspose file tools*
The moose likes Object Relational Mapping and the fly likes JPA Query using Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA Query using " where in" and setParameter" Watch "JPA Query using " where in" and setParameter" New topic
Author

JPA Query using " where in" and setParameter

Wally Hartshorn
Ranch Hand

Joined: Jan 30, 2003
Posts: 77
I'm using the JPA Query Language for the first time and have run into a problem. I'm trying to do a "select where in (list of values)" query, but can't figure out the syntax to avoid hard-coding the list of values.

This works:


Replacing the hard-coded values with a parameter and an array gives me a class cast exception:


I'm using JDK 1.5, Hibernate 3, and JPA.

Can anyone give me some pointers on how to do this? (Or a better way to accomplish it?)

Thanks!
Wally


Wally Hartshorn
Debu Panda
author
Ranch Hand

Joined: Jan 21, 2007
Posts: 100
JPA supports IN clause either with a static list or dynamic list with a sub query.

You can probably work around by dynamically building the query:

"select t from Thing t where t.status in ("+ statusCodes +")" )
.getResultList();


Author: EJB 3 In Action (http://manning.com/panda)
Dejan Mratinkovic
Ranch Hand

Joined: Nov 20, 2008
Posts: 65
Any updates on this? It's almost two years since original post. Is it possible that this is only way to create dynamic "where in"?

Can it be achieved in more elegant way?
Shailen Karambe
Greenhorn

Joined: Nov 05, 2008
Posts: 2
You can check this link JPA doc

and query help

let me know whether your problem got resolved..
Konstantin Yankovski
Greenhorn

Joined: Apr 25, 2009
Posts: 1
I do following and it works fine:


Hibernate generates following SQL for this JPA query:



'storeIds' parameter must be Collection, it doesn't work with an array!
Mandar Kaduskar
Greenhorn

Joined: May 01, 2008
Posts: 3
absolutely there is a solution two ways you can achieve this, as mentioned by Debu Panda you can create a comma separated String and dynamically add it to a query
like
String statesCode= "'A', 'E'";
select t from Thing t where t.status in (statesCode)")
.getResultList();

OR

List<String> inList = new ArrayList<String>();
inList.add("'A'");inList.add("'E'");
select t from Thing t where t.status in (:statesCode)")
.getResultList();
query.setParameter("statesCode", inList );

Hope this helps.
Mandar Kaduskar
Greenhorn

Joined: May 01, 2008
Posts: 3
Also dont forget that if your IN parameter has numeric data type like Long, smallInt, BigInt etc then you will need to create the list of the appropriate data type instead of Strings. Hope makes sense.
Amit Ghorpade
Bartender

Joined: Jun 06, 2007
Posts: 2716
    
    6

"app developer " please check your private messages for an important administrative matter. You can check them by clicking the My Private Messages link above.


SCJP, SCWCD.
|Asking Good Questions|
lilili bbb
Greenhorn

Joined: Sep 07, 2011
Posts: 1
try to change setParameter(1,something);
to setLong(1,something);
(there are setString etc..)
that helps me....
good luck!
Yasin Turk
Greenhorn

Joined: Feb 23, 2008
Posts: 4
You should use setParameterList() method for "in" type parameters
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: JPA Query using " where in" and setParameter