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

[sonavor <jw@xxxxxxxxxxxxxx>]

Thanks for the information Chris.  

I did try a few tests with the EclipseLink ExpressionBuilder.  I was able to
get that to work with a ReadAllQuery but I still had to build the expression
where I am do "OR" operations between the conditions -

     Expression exp = null;
     ExpressionBuilder eb = new ExpressionBuilder(TestListing.class);
     for ( Iterator it=kwList.iterator();it.hasNext(); ) {
            TestKeywords tkw = (TestKeywords)it.next();
            Expression expKw =
eb.anyOf("testKeywordsCollection").equal(tkw);
            if ( exp == null ) {
                exp = expKw;
            } else {
                exp = exp.or(expKw);
            }
      }
     
       ReadAllQuery readAllQuery = new ReadAllQuery(TestListing.class);
       readAllQuery.setSelectionCriteria(exp);

...then get the ActiveSession from the EntityManager and run the query...

I think I will go with the EntityManager.createQuery where I build up the
"OR" operator on the submitted keyword Id values for now.

When EclipseLink 2.0 is available I will retry the "IN" operation.  I don't
see a download for EclipseLink 2.0 on the web site.



Christopher Delahunt wrote:
> 
> 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?
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>   
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 

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