[
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
|
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.