Just wanted to mention that JPQL allows collection parameter in the IN clause.
String jpql = "select count(e.eid), e from Entity e, Property p where
e.eid=p.refersTo and p.type IN ( ?1 ) group by e.eid order by
count(e.eid) desc";
In this case, you can set a Collection object to the parameter:
Query q = em.createQuery(jpql);
List coll = new ArrayList();
coll.add("A");
coll.add("B");
coll.add("C");
q.setParameter(1, coll);
Internally, openjpa will loop through the collection and generate a SQL that
has the same number of parameter markers as the number of elements in your
collection, and set parameters accordingly. The push-down sql will look like:
select ....... where ... p.typeIN (?1, ?2, ?3) ....
-Fay
----- Original Message ----
From: Fay Wang <[email protected]>
To: [email protected]
Sent: Monday, August 31, 2009 8:59:56 PM
Subject: Re: OpenJPA1.2.2 - NativeQuery - IN clause
Hi,
For native query:
String sql = "select count(e.eid), e.* from Entity e, Property p where
e.eid=p.refersTo and p.type IN ( ?1 ) group by e.eid order by count(e.eid)
desc";
As you observed, the following parameter setting will work, since you have only
one parameter marker:
query.setParameter(1, "A");
If you want to match with more than one value, you have to modify your sql, for
example, to
p.type IN ( ?1, ?2, ?3)
and in the parameter setting, you have to set it in accordance with your
parameter markers:
query.setParameter(1, "A");
query.setParameter(2, "B");
query.setParameter(3, "C");
This is the SQL syntax, and openjap simply sends this request to the backend
server without any further modifications. Setting the parameter value to an
array or a collection will not work. Hope this helps.
Regards,
Fay
----- Original Message ----
From: HaricotBean <[email protected]>
To: [email protected]
Sent: Monday, August 31, 2009 3:28:28 PM
Subject: OpenJPA1.2.2 - NativeQuery - IN clause
Which type is NativeQuery expecting for a parameter that maps to a IN clause?
Through trial and error I found that Collections cause an error to be throw
and Arrays execute but return no results. However if I pass a single String
value it works...
@NamedNativeQuery(name = "Entity.mostRefersType", query = "select
count(e.eid), e.* from Entity e, Property p where e.eid=p.refersTo and
p.type IN ( ?1 ) group by e.eid order by count(e.eid) desc;",
resultSetMapping = "MostUsedResults"),
Surprisingly (to me at least) the following has been my observation:
query.setParameter(1, "A"); // successful
query.setParameter(1, {"A","B"}); // executes but fails to match
Collection<String> set = new ArraySet(); set.add("A)";
query.setParameter(1, set); // throws invalid parameter exception on execute
Does anyone have any clues as to what the right approach might be to use a
named query. The alternative is to build a string representation of the sql
statement but I would like to find the answer.
Regards ...
--
View this message in context:
http://n2.nabble.com/OpenJPA1-2-2-NativeQuery-IN-clause-tp3556433p3556433.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
