my dog learned polymorphism*
The moose likes Object Relational Mapping and the fly likes [JPQL] IN clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "[JPQL] IN clause" Watch "[JPQL] IN clause" New topic
Author

[JPQL] IN clause

Fabian Angy
Ranch Hand

Joined: Oct 27, 2008
Posts: 90
Hi!

I've got a problem with my "IN" clause.
If i do "select DISTINCT a from A a, IN(a.errors) ae where ae.errorCode IN (:errorCode)"
And if I have one value in errorCode (setErrorCode("xxx") it works but if I have more than one value (setErrorCode("xxx, yyy")) it doesn't work!
In fact, when i have more than one value, I use a method which returns each value with " ' ' ", by example i'll have "select DISTINCT a from A a, IN(a.errors) ae where ae.errorCode IN ('xxx', 'yyy')" and not "IN ('xxx, yyy')".

Someone knows why it doesn't work?

Thanks in advance!


SCJP 5
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
To bind an collection parameter you need to remove the brackets,

i.e.
select DISTINCT a from A a join a.errors ae where ae.errorCode IN :errorCode

otherwise use,

select DISTINCT a from A a join a.errors ae where ae.errorCode IN (:errorCode1, :errorCod2, :errorCode3)


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Fabian Angy
Ranch Hand

Joined: Oct 27, 2008
Posts: 90
My problem is that all of values are in only one variable :/ I can't do :errorCode1, :errorCode2, ... and without brackets, it doesn't work, i've got an error!
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
What is the error? What JPA provider and version are you using?
Fabian Angy
Ranch Hand

Joined: Oct 27, 2008
Posts: 90


with EclipseLink 1.0.2
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
This was not supported in JPA 1.0, but is in JPA 2.0. You should upgrade to the latest EclipseLink 2.3. I think it was fixed in 1.1, so you need at least 1.1.
Fabian Angy
Ranch Hand

Joined: Oct 27, 2008
Posts: 90
Oh ok!

But I don't understand why it works when I put myself the values (by example: IN ('000', '111')) but not when I put a parameter (IN (:errorCode)) :/ If it supports the values hardcoded, why not the parameter?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: [JPQL] IN clause