Sorry, I probably confuse you about the JPQL and native (SQL) query.
The Collection<String> approach will work with JPQL, but not with SQL (native
query, regardless it is named or not).
For JPQL, the Query object is created in the following way:
Query q = em.createQuery("select e from Entity1 e where e.name in (?1)");
For SQL or native query, the Query object is created in the following way:
Query q = em.createNativeQuery("select e.* from Entity1Table e where e.name in
(?1)");
For the SQL, you either have to provide the literals in your IN clause as you
did, e.g.
in ("A", "B", "C")
or you have to set the parameter markers, e.g.,
in (?1, ?2, ?3)
and then set parameters to the query accordingly.
----- Original Message ----
From: HaricotBean <[email protected]>
To: [email protected]
Sent: Tuesday, September 1, 2009 7:10:45 AM
Subject: Re: OpenJPA1.2.2 - NativeQuery - IN clause
Thanks to all.
I did try the Collection<String> approach but OpenJPA generates an error and
complains about the underlying type (ArrayList, HashSet, etc.) being an
invalid type for th expression. Not being familiar with the OpenJPA source
I took a quick look at fisheye for
org.apache.openjpa.persistence.query.ArrayExpression and it seems to suggest
that it expects an String[]. That failed to produce the expect results.
In the end I fell back to generating the sql string manually and abandoning
NamedQuery, e.g:
private static String mostRefersType = "select count(e.eid), e.* from Entity
e, Property p where e.eid=p.refersTo and p.type IN ( #### ) group by e.eid
order by count(e.eid) desc;";
public static List<EntityHolder> findMostUsed(Registry registry,
Collection<String> types, int limit) {
...
sql = mostRefersType.replace("####", asString(types));
q = registry.getEntityManager().createNativeQuery(sql, "MostUsedResults");
list = (List<Object[]>) q.getResultList();
...
}
private static String asString(Collection<String> types) {
StringBuffer typesAsString = new StringBuffer();
int i = 0;
for (String type : types) {
if ( typesAsString.length() > 0 ) typesAsString.append(",");
typesAsString.append("'"+type+"'");
}
return typesAsString.toString();
}
This approach works fine. I have to assume that there is either a bug in
the Native Named Query processing or some requirement that isnt very well
documented.
Regards....
--
View this message in context:
http://n2.nabble.com/OpenJPA1-2-2-NativeQuery-IN-clause-tp3556433p3560749.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
