One addition (my question is still open).
I can confirm that a valid workaround for this problem is to use
setHint("openjpa.hint.UseLiteralInSQL", "true") and updating to OpenJPA 2.4.0
which is available in Maven central since a few days.
Henno
-----Oorspronkelijk bericht-----
Van: Henno Vermeulen [mailto:[email protected]]
Verzonden: donderdag 23 april 2015 11:49
Aan: [email protected]
Onderwerp: criteria API generates a parameter for literal in group by but does
not provide the value
Hello,
I have a query created using the criteria API where I group by an expression
that contains a small calculation using literal values.
OpenJPA generates the correct SQL but does not provide the value of the
generated parameter in the group by clause. The query fails with a SQL
exception "The value is not set for the parameter number 9.".
I can reproduce the issue with a minimal example. Suppose we have a person
class with integer age and length columns and we wish to select the average
length grouped by the person's age / 10:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Double> query =
cb.createQuery(Double.class);
Root<Person> person = query.from(Person.class);
Expression<Double> averageLength =
cb.avg(person.<Integer> get("length"));
CriteriaQuery<Double> select =
query.select(averageLength);
select.groupBy(cb.quot(person.<Integer> get("age"),
cb.literal(10)));
// optional where, useful to ensure parameters are
logged
select.where(cb.gt(person.<Integer> get("age"),
cb.literal(20)));
System.out.println("result: " +
em.createQuery(query).getResultList());
Whe running this query with trace and displaying parameters on I get:
1067 testPU TRACE [main] openjpa.Query - Executing query: Query:
org.apache.openjpa.kernel.QueryImpl@be4f81; candidate class: class
entities.Person; query: null
1108 testPU TRACE [main] openjpa.jdbc.SQL - <t 5763249, conn 7326702>
executing prepstmnt 26531336 SELECT AVG(t0.length) FROM Person t0 WHERE (t0.age
> ?) GROUP BY (t0.age / ?) [params=(int) 20]
You can clearly see that the query has two parameter placeholders but only one
value is provided.
Shall I report this as a bug or am I doing something wrong in my code?
(As a workaround I can call setHint("openjpa.hint.UseLiteralInSQL", "true") on
em.createQuery(query). This doesn't work in my application because there is a
bug where boolean literals aren't correctly handled:
https://issues.apache.org/jira/browse/OPENJPA-2534. I think this is solved in
the upcoming release.)
Thank you,
Henno
