[eclipselink-users] Query using WHERE - IN on a Join Table Question

[sonavor <jw@xxxxxxxxxxxxxx>]

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?










-- 
View this message in context: http://www.nabble.com/Query-using-WHERE---IN-on-a-Join-Table-Question-tp24214950p24214950.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.