Query using WHERE - IN on a Join Table Question

[sonavor]

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?

[christopher delahunt]

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@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[sonavor]

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
[e-mail]
https://dev.eclipse.org/mailman/listinfo/eclipselink-users