You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Kariem Hussein <ka...@gmail.com> on 2015/07/16 14:30:11 UTC
Question regarding "constant" parameter replacement in JPQL
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
Re: Question regarding "constant" parameter replacement in JPQL
Posted by Kariem Hussein <ka...@gmail.com>.
On Mon, Jul 20, 2015 at 4:58 PM, Kariem Hussein <ka...@gmail.com>
wrote:
> [...] 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?
>
I just filed OPENJPA-2601 [1] for this and I have prepared a pull request
[2]. I hope it is possible to backport this to 2.3, because we currently
cannot go to 2.4.
Thank you,
Kariem
[1] https://issues.apache.org/jira/browse/OPENJPA-2601
[2] https://github.com/apache/openjpa/pull/2
Re: Question regarding "constant" parameter replacement in JPQL
Posted by Mark Struberg <st...@yahoo.de>.
Oh gosh, just found the following which gets executed _every_ time:
private static boolean isOraclePreparedStatement(Statement stmnt) {
try {
return Class.forName("oracle.jdbc.OraclePreparedStatement").
isInstance(stmnt);
} catch (Exception e) {
return false;
}
}
Please tell me I’m wrong, but we do a Class.forName for _every_ setString on an nchar, etc field?
Imo we can easily improve this by doing the Class.forName and getField just once and store it.
There is even a bit more reflection stuff which could get cached imo.
Will create a separate jira for it.
LieGrue,
strub
> Am 20.07.2015 um 16:58 schrieb Kariem Hussein <ka...@gmail.com>:
>
> 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 <ka...@gmail.com>
> 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
>>
>>
Re: Question regarding "constant" parameter replacement in JPQL
Posted by Rick Curtis <cu...@gmail.com>.
This looks like it might be a bug? As far as I can tell we're parsing the
hint value and then using it at runtime.... but I can't find any tests
where we actually exercise this functionality. Perhaps you could put
together a small OpenJPA UT that recreates the problem[1]? Also, what
version of OpenJPA are you using?
[parse] -- org.apache.openjpa.persistence.XMLPersistenceMetaDataParser
<eclipse-javadoc:%E2%98%82=openjpa-persistence/src%5C/main%5C/java%3Corg.apache.openjpa.persistence%7BXMLPersistenceMetaDataParser.java%E2%98%83XMLPersistenceMetaDataParser>
.startQueryHint(...)
[runtime] -- org.apache.openjpa.persistence.EntityManagerImpl
<eclipse-javadoc:%E2%98%82=openjpa-persistence/src%5C/main%5C/java%3Corg.apache.openjpa.persistence%7BEntityManagerImpl.java%E2%98%83EntityManagerImpl>
.createNamedQuery(...)
[1] http://openjpa.apache.org/testing.html
On Mon, Jul 20, 2015 at 9:58 AM, Kariem Hussein <ka...@gmail.com>
wrote:
> 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 <ka...@gmail.com>
> 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
> >
> >
>
--
*Rick Curtis*
Re: Question regarding "constant" parameter replacement in JPQL
Posted by Kariem Hussein <ka...@gmail.com>.
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 <ka...@gmail.com>
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
>
>