Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » IN clause in @NamedNativeQuery(IN clause not working when used in @NamedNativeQuery)
IN clause in @NamedNativeQuery [message #646278] Thu, 23 December 2010 04:29 Go to next message
Sarkan is currently offline Sarkan
Messages: 10
Registered: May 2010
Junior Member
Dear All,

I want to use @NamedNativeQuery for one of my query which is quite complex to write as @NamedQuery and it contains IN clause where we need to specify parameters dynamically.

When i wrote query in my code like:
SELECT [COLUMNS] FROM TABLE WHERE COLUMN IN ('A','B','C')
it works perfectly, but when i try to put this query inside my entity object as:
@NamedNativeQuery (name="XXX",query="SELECT [COLUMNS] FROM TABLE WHERE COLUMN IN ?1")
it doesn't work.

I have used IN clause as mentioned above in JPQLs using @NamedQuery and it works fine but when it comes to @NamedNativeQuery it stops working.

So i assume eclipselink doesn't support parameterized "IN clause" in case of @NamedNativeQuery. Is it TRUE?
Re: IN clause in @NamedNativeQuery [message #646377 is a reply to message #646278] Thu, 23 December 2010 15:12 Go to previous messageGo to next message
Rich MacDonald is currently offline Rich MacDonald
Messages: 18
Registered: December 2010
Junior Member
Yes, there is a bug in eclipselink using parametrized IN clauses with collections. It adds an additional parentheses. In the meantime, see the following post for how to hack a quick fix yourself: (my comments in the bug report)

https://bugs.eclipse.org/bugs/show_bug.cgi?id=328378
Re: IN clause in @NamedNativeQuery [message #647083 is a reply to message #646377] Tue, 04 January 2011 14:35 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1023
Registered: July 2009
Senior Member
Hello,

I do not believe 328378 is related, as 328378 deals with JPQL queries but the first post mentions using NamedNativeQuery - straight SQL queries.

Can you please post the exception you are getting? My guess is you are attempting to pass in a collection to
@NamedNativeQuery (name="XXX",query="SELECT [COLUMNS] FROM TABLE WHERE COLUMN IN ?1")
for the ?1 parameter, where as in the working query you have each parameter defined individually as in ('A','B','C'). Please note that Native queries are passed to the database exactly as defined, so I would suggest instead using:
@NamedNativeQuery (name="XXX",query="SELECT [COLUMNS] FROM TABLE WHERE COLUMN IN(?1,?2,?3)")
and then passing a collection with 3 arguments.

Or, you can try a JPQL query:
@NamedQuery (name="XXX",query="SELECT [COLUMNS] FROM TABLE WHERE COLUMN IN(?1)")
JPQL processing will create the appropriate sql based on the number of arguments if a collection is used for the ?1 parameter.

Best Regards,
Chris
Re: IN clause in @NamedNativeQuery [message #647209 is a reply to message #647083] Wed, 05 January 2011 04:23 Go to previous message
Sarkan is currently offline Sarkan
Messages: 10
Registered: May 2010
Junior Member
Hi Chris,

You're right the JPQL with parameter (IN ?1) works perfectly fine but in our case as i said the query is quite complex to be written as JPQL so we are using native query instead.

Now the problem with native query is we can not define (IN ?1) parameter as is does not work. And we dont have fixed number of parameters like 3 or so.

That's why we need one parameter (?1), which should accept collections otherwise we have make a comma separated string out of my collection and add it to the query dynamically Sad.

I would appreciate If you have a better idea to share.

[Updated on: Wed, 05 January 2011 04:26]

Report message to a moderator

Previous Topic:quoted identifiers
Next Topic:Named Query error
Goto Forum:
  


Current Time: Tue Sep 23 12:23:39 GMT 2014

Powered by FUDForum. Page generated in 0.03462 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software