• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Tim Cooke
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
  • Liutauras Vilda
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
  • Piet Souris
  • Mikalai Zaikin
  • Himai Minh

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

Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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?

Pallavi ch
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

If you are using a wood chipper, you are doing it wrong. Even on this tiny ad:
Free, earth friendly heat - from the CodeRanch trailboss
    Bookmark Topic Watch Topic
  • New Topic