Integration

I'm having a problem with a large legacy Oracle 9i database that I'm trying to use with TopLink JPA. All of the tables have a synthetic primary key with the datatype "char(32)", but the majority of the fields only use 16 - 20 of the 32 available (i.e. the rest are space padded).

The problem is that both relationships in the entity classes and parameterized JPA-QL queries fail to return any results. Here's an example set of tables and entities to demonstrate the problem:

CREATE TABLE test_foo (
id char(32) primary key,
foo_value varchar2(10)
);
CREATE TABLE test_bar (
id char(32) primary key,
test_foo_id char(32),
bar_value varchar2(10),
constraint fk_test_foo foreign key (test_foo_id)
references test_foo (id)
);

@Entity
@Table (name = "test_foo")
public class TestFoo
{
@Id
private String id;
public String getId () { return id; }
public void setId (String id) { this.id = id; }

@Column (name = "foo_value")
private String fooValue;
public String getFooValue() { return fooValue; }
public void setFooValue(String fooValue) { this.fooValue = fooValue; }

@OneToMany (mappedBy = "testFoo")
private List<TestBar> testBars;
public List<TestBar> getTestBars () { return testBars; }
public void setTestBars(List<TestBar> testBars) { this.testBars = testBars; }
}

@Entity
@Table (name = "test_bar")
public class TestBar
{
@Id
private String id;
public String getId() { return id; }
public void setId(String id) { this.id = id; }

@ManyToOne
@JoinColumn (name = "test_foo_id")
private TestFoo testFoo;
public TestFoo getTestFoo() { return testFoo; }
public void setTestFoo(TestFoo testFoo) { this.testFoo = testFoo; }

@Column (name = "bar_value")
private String barValue;
public String getBarValue() { return barValue; }
public void setBarValue(String barValue) { this.barValue = barValue; }
}

Now, let's say the following record exists in the table "test_foo":

id = '321', foo_value = 'foobar'

and then in "test_bar" I might have this:

id = '123', test_foo_id = '321', bar_value = 'barfoo'

If I execute a JPA-QL query as follows, I get no results:

Query query = entityManager.createQuery ("SELECT o FROM TestFoo o WHERE o.id = ?1");
query.setParameter (1, "321");
query.getResultList (); // empty list is returned

If I use the find method instead, it works fine, but then I can't get the related entity:

TestFoo foo = entityManager.find (TestFoo.class, "321");
List<TestBar> testBars = foo.getTestBars ();

testBars is an empty list in this case. This fails in both directions (i.e. if I try to get the TestFoo instance that owns a given TestBar instance).

I've run tests with other data types, and they don't have this problem. I suspect this is related to the Oracle JDBC driver -- since it doesn't handle the "char" type in prepared statements correctly. But since I'm working through TopLink JPA, I have no control over whether I use setString() or setFixedCHAR() for setting prepared statement parameters. Is there any other way around this problem? My constraints are: 1) I need continue to use char(32) as the pk field data type (unless no other solution exists but to change it), 2) the JPA implementation may end up changing at deploy-time so I can't write code specific to TopLink unless I can write more code to dynamically avoid the use of the TopLink specific code as needed, and 3) I can't turn off prepared statements for performance reasons.