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
