You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by HaricotBean <bi...@wilkins-family.org.uk> on 2009/09/01 00:28:28 UTC
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.
Re: OpenJPA1.2.2 - NativeQuery - IN clause
Posted by Fay Wang <fy...@yahoo.com>.
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 <fy...@yahoo.com>
To: users@openjpa.apache.org
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 <bi...@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.
Re: OpenJPA1.2.2 - NativeQuery - IN clause
Posted by Fay Wang <fy...@yahoo.com>.
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 <bi...@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.
Re: OpenJPA1.2.2 - NativeQuery - IN clause
Posted by Fay Wang <fy...@yahoo.com>.
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 <bi...@wilkins-family.org.uk>
To: users@openjpa.apache.org
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.
Re: OpenJPA1.2.2 - NativeQuery - IN clause
Posted by HaricotBean <bi...@wilkins-family.org.uk>.
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.
Re: OpenJPA1.2.2 - NativeQuery - IN clause
Posted by Kevin Sutter <kw...@gmail.com>.
Hi,
This is not my area of expertise. But since ?1 refers to a single
parameter, and you mentioned that "A" is a valid value with successful
results, I would try something like " 'A', 'B' ". Another alternative would
be to list additional parameters, ie. ?1, ?2, ?3, etc. But, of course, this
would not be as flexible. Maybe somebody has more direct experience with
this usage...
Kevin
On Mon, Aug 31, 2009 at 5:28 PM, HaricotBean <bi...@wilkins-family.org.uk>wrote:
>
> 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.
>