openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Fay Wang <fyw...@yahoo.com>
Subject Re: OpenJPA1.2.2 - NativeQuery - IN clause
Date Tue, 01 Sep 2009 03:59:56 GMT
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 <bill@wilkins-family.org.uk>
To: users@openjpa.apache.org
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.



      


Mime
View raw message