How can I prevent OpenJPA from replacing "constant" parameters in my queries?

Question

I seem to have a problem with a query on a CHAR field that is incorrectly filtered when OpenJPA replaces my constant value with an SQL parameter.

Example

Given this table in Oracle

create table PERSON (
  id char(10) not null,
  type char(3) not null,
  primary key (id)
)

with three different values for type: WTW, WAI, V

and the corresponding entity

@Entity
public class Person {
   String id;
   String type;
}

I use the following query from an orm.xml file:

<named-query name="person.v">
  <query>
    select p
    from Person p
    where p.type = 'V'
  </query>
</named-query>

Problem

When I run this through an EntityManager provided by OpenJPA, the query changes to

select p.id, p.type
from PERSON p
where p.type = ? 

and OpenJPA passes the value "V" as parameter. The previously "constant" value for type is now an SQL parameter. The problem lies in the fact that for the char(3) type column, Oracle will store

"V  "

and this is not equal to the value passed by OpenJPA as a parameter. Again: without parameters, i.e. just using the string in SQL that I use in JPQL, everything works just fine.

I assume OpenJPA performs this replacement in order to minimize the query cache by normalizing all queries, and I understand that this makes a big difference for a lot of people, but I think this is a problem in my case.

My question is now: how can I prevent OpenJPA from doing this replacement? I know that I won't have different permutations of this query at runtime. Is there a configuration property or a query hint that I can use?

Answer 1

Besides just changing the column to a VARCHAR, I have two workarounds that are not very nice:

1. Adapt the JPQL to where p.type = 'V ' ("V" followed by two spaces).
2. Use a native query which won't be optimized by OpenJPA.

In (1), I know about the underlying char(3) and the idea of JPQL is to abstract this away, so this goes a little bit against the idea of using JPA.

With (2) I have to rewrite this simple query just a little bit, but it just does not seem right to use a native query just to avoid problems created by good intentions of my persistence provider.