Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Query using WHERE - IN on a Join Table Question 
Hello,
IN is supported, but in JPA 1.0, IN cannot take a collection as a parameter which is why you get an exception. EclipseLink native expressions have always allowed taking a collection as a parameter if you are able to build your query that way instead, or if you can upgrade, support was just added to EclipseLink 2.0 so that JPQL will also allow taking the collection and is in the more recent nightly builds (added 2009-06-10 through bug 249224).. 

Best Regards,
Chris

sonavor wrote:

I am using Eclipselink and have three tables I am testing.  I am
experimenting with have a many-to-many relationship between a main, listing
table and a keyword table.
The database is MySql.

One table is called TEST_LISTING with these columns -
LISTING_ID integer (PK)
LISTING_NAME varchar(40)

Second table is called TEST_KEYWORDS with these columns -
KW_ID integer (PK)
KW_NAME varchar(24)

Third table is a join table called KW_MAP with these columns -
LISTING_ID integer  (PK)
KW_ID integer         (PK)

So, using Netbeans 6.1, I generated two JPA entity classes -
TestListing.java
TestKeywords.java

that have a many-to-many relationship to each other via the KW_MAP table.

This all works and I can use a join query like this -

My method receives an ArrayList of Integer values that are the Id of
selected TEST_KEYWORDS called tkwIdArray.

        StringBuffer qSB = new StringBuffer();
        qSB.append("SELECT t FROM TestListing as t ");
        qSB.append("JOIN t.testKeywordsCollection as tkw ");
        qSB.append("WHERE ");
for ( int i=0;i<tkwIdArray.length;i++ ) { 
            Integer tkid = tkwIdArray[i];
            if ( i == 0 ) {
                qSB.append("tkw.kwId =
:arg").append(String.valueOf(i)).append(" ");
            } else {
                qSB.append("OR tkw.kwId =
:arg").append(String.valueOf(i)).append(" ");
            }
        }
Query q = em.createQuery(qSB.toString()); 
        for ( int i=0;i<tkwIdArray.length;i++ ) {
           q.setParameter("arg"+String.valueOf(i), tkwIdArray[i]);
        }
List<TestListing> tList = q.getResultList(); 

Although that works I would like to create the query where it looks more
simple by using "IN"

I tried this -

I create a List<Integer> named "idList" that is an ArrayList of the
submitted TEST_KEYWORDS Id values.

List<TestListing> tList = em.createQuery("SELECT t FROM TestListing as t
JOIN t.testKeywordsCollection as tkw WHERE tkw.kwId IN (?1)")
                                        .setParameter(1,
idList).getResultList();

When I try this query I get -

java.lang.IllegalArgumentException: You have attempted to set a value of
type class java.util.ArrayList for parameter 1 with expected type of class
java.lang.Integer from query string SELECT t FROM TestListing as t JOIN
t.testKeywordsCollection as tkw WHERE tkw.kwId IN (?1).

Does Eclipselink not support using the "IN" attribute in a query?

As a different kind of test I rewrote the join query to find TEST_LISTING
records by joining on the TEST_KEYWORDS.KW_NAME column so I could try the
"IN" with a String Array.  I can get the "IN" to work with a hard-coded
String array like -

List<TestListing> tList = em.createQuery("SELECT t FROM TestListing as t
JOIN t.testKeywordsCollection as tkw WHERE tkw.kwName IN
('italian','food')").getResultList();

but when I change it to this -

List<String> kwnList = new ArrayList<String>();
        kwnList.add("italian");
        kwnList.add("food");
        List<TestListing> tList = em.createQuery("SELECT t FROM TestListing
as t JOIN t.testKeywordsCollection as tkw WHERE tkw.kwName IN (?1)")
                 .setParameter(1, kwnList).getResultList();

I get the error -
java.lang.IllegalArgumentException: You have attempted to set a value of
type class java.util.ArrayList for parameter 1 with expected type of class
java.lang.String from query string SELECT t FROM TestListing as t JOIN
t.testKeywordsCollection as tkw WHERE tkw.kwName IN (?1)

Is there a way to do this using Eclipselink or should I just stick with my
iteration construction of the query using "OR" clauses?

Back to the top