This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes JPA native Query - Select... where... IN, How to set list of values to setParameter()? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA native Query - Select... where... IN, How to set list of values to setParameter()?" Watch "JPA native Query - Select... where... IN, How to set list of values to setParameter()?" New topic
Author

JPA native Query - Select... where... IN, How to set list of values to setParameter()?

Lavanya ch
Ranch Hand

Joined: Dec 16, 2004
Posts: 75
Hi,

I'm using openJPA native query defined in mapping file.
I've a select query where I'm trying to pass a List of values as input parameter to it using IN clause.
select... where... IN

SQL works , but when I use the same query in JPA with a positional parameter, It fails..
How to pass list of values to Query??


Here is the code.



When I execute this code, I'm getting
input Param :: '504152335','504152610','504180177','968718337'
Results size:: 0

With NO exception , no results.

I've treid removing single quotes in param, same output with 0 results.

i've tried making the input parametre as List<String>



When I run this Im getting following exception



Has anyone know about this issue?

Thanks,
Lavanya.
Lavanya ch
Ranch Hand

Joined: Dec 16, 2004
Posts: 75
Just in case, if somebody wants to know,

JPA 1.0 specification doesn't support passing list of values to native queries as I read somewhere.
it works with JPQL.

For native queries we have 2 work aorunds.
1. make the native named query IN caluse have as many number of positional parameters as many you have them in list.
In my case,

It kind of limits the dynamic nature of list. But if it's a list of String values, you can have max. number of positional parameters in the query and set the required number of parameters in the code, and for the rest of the excess positional parameters set null string. It works!

2. go for dynamic native query.
Here the query is appended with the list of params.

Thanks!
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: JPA native Query - Select... where... IN, How to set list of values to setParameter()?
 
Similar Threads
Select clause for listing cities for refined search
open JPA throws exception on using Enum
jsp form loading issue
iterating with a scriptlet, can't get the values into js arr
Iterator in Struts 1