Query using WHERE - IN on a Join Table Question
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?