« Return to Thread: Query using WHERE - IN on a Join Table Question

Query using WHERE - IN on a Join Table Question

by sonavor :: Rate this Message:

Reply to Author | View in Thread

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?









 « Return to Thread: Query using WHERE - IN on a Join Table Question