Hi (again),
I've asked the same question on StackOverflow [1] and was pointed to a
similar question [2] and actually found the well-hidden option to change
this behavior by setting the hint UseLiteralInSql to true [3].
It seems to work, when I set this on the javax.persistence.Query, but it
does not work, when I set the appropriate hint in orm.xml:
<named-query name="person.v">
<query>
select p
from Person p
where p.type = 'V'
</query>
<hint name="openjpa.hint.UseLiteralInSQL" value="true" />
</named-query>
Something wrong with this, or is there a limitation I am running into?
Thank you very much,
Kariem
[1] How can I prevent OpenJPA from replacing “constant” parameters in my
queries?
http://stackoverflow.com/questions/31516813
[2] jpa namedquery with literals changed to prepared statement
http://stackoverflow.com/questions/28317482
[3] Javadoc org.apache.openjpa.kernel.QueryHints.HINT_USE_LITERAL_IN_SQL
http://openjpa.apache.org/builds/2.4.0/apidocs/org/apache/openjpa/kernel/QueryHints.html#HINT_USE_LITERAL_IN_SQL
On Thu, Jul 16, 2015 at 2:30 PM, Kariem Hussein <[email protected]>
wrote:
> Hi there,
>
> I am in the process of migrating a big (old) code base from JPA 1 with
> Hibernate 3.3 to JPA 2 with OpenJPA. I've had a problem with a query that
> used to work in the old version and now does not and I wanted to know
> whether my reasoning is correct.
>
> I have already reduced the problem description to the minimal and I hope I
> did not lose relevant pieces of information on the way.
>
> Given this table in Oracle
>
> create table PERSON (
> id char(10) not null,
> type char(3) not null,
> primary key (id)
> )
>
> There are a lot of rows with in total three different types "WTW", "WAI",
> "V" (to be honest, I don't know what they stand for). However, we have an
> entity to work with this table:
>
> @Entity
> public class Person {
> String id;
> String type;
> }
>
> The following query is used in the application from an orm.xml file:
>
> <named-query name="person.v">
> <query>
> select p
> from Person p
> where p.type = 'V'
> </query>
> </named-query>
>
> As the `type` field is `char(3)`, Oracle will store `V ` ('V' followed
> by two spaces) for the string "V".
>
> In Hibernate, I did not have a problem with this query, but with OpenJPA,
> there is some magic performance improvement on this query that reduces the
> number of queries by normalizing permutations -- at least that is what I
> think why my query was translated this way -- which results in the
> following SQL being sent to the DB
>
> select p.id, p.type
> from PERSON p
> where p.type = ?
>
> The "constant" parameter for `type` in my query was replaced with an SQL
> parameter and the OpenJPA log shows that "V" is passed as value. I believe
> that because of this replacement I do not get any results anymore.
>
> It works if I do one of the following
>
> - (a) Adapt the JPQL query to `where p.type = 'V '`, effectively knowing
> about the underlying `char(3)` field.
> - (b) Use a native query. OpenJPA will then not try to "improve" my query
> in a way that changes its semantics.
>
> Is there something I can do to improve this behavior in JPA? Is there any
> benefit in replacing a "constant" parameter in a named query that is only
> used in this way (there are no permutations).
>
> Shouldn't the parameter be converted correctly (including padding) into
> the DB type? Is this a bug, or do I have to specify some kind of hint?
>
>
> Thank you for your comments,
> Kariem
>
>
