Eclipse Community Forums: EclipseLink » IN clause in @NamedNativeQuery

Home » Eclipse Projects » EclipseLink » IN clause in @NamedNativeQuery(IN clause not working when used in @NamedNativeQuery)

Show: Today's Messages :: Show Polls :: Message Navigator

IN clause in @NamedNativeQuery [message #646278]

Thu, 23 December 2010 04:29

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?

Report message to a moderator

Re: IN clause in @NamedNativeQuery [message #646377 is a reply to message #646278]

Thu, 23 December 2010 15:12

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

Report message to a moderator

Re: IN clause in @NamedNativeQuery [message #647083 is a reply to message #646377]

Tue, 04 January 2011 14:35

Chris Delahunt

Messages: 1389
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

Report message to a moderator

Re: IN clause in @NamedNativeQuery [message #647209 is a reply to message #647083]

Wed, 05 January 2011 04:23

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:42:12 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter