[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
[eclipselink-users] 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?
--
View this message in context: http://www.nabble.com/Query-using-WHERE---IN-on-a-Join-Table-Question-tp24214950p24214950.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.