We are working with a legacy database for a new application and we are using EclipseLink 2.5.1.
We are getting some problems mapping the contents of this DB. It is working using MS SQL Server, but we are having problems when using DB2.
Changing the design of the tables is not an option as it will break a legacy application using the same database.
To describe the problem using pseudo code lets assume we have three tables.
TableA. Each row in this table defines some kind of entity called EntityA. The key for each row is a string and is stored in column KeyA. Column key a is defined as CHAR( 8 ). The key is allowed to be shorter than 8 characters. If that is the case the string is right padded with spaces.
TableB. Each row in this table defines some kind of entity called EntityB. The key for each row is a string and is stored in column KeyB. Column key a is defined as CHAR(4). The key is allowed to be shorter than 4 characters. If that is the case the string is right padded with spaces.
TableC. Each row defines a relationship between EntityA and EntityB. Each EntityA can have a relation to one or more EntityB. The table has two columns KeyParent and KeyChild. Both are defined as CHAR(14).
In KeyParent a key for a EntityA is stored and in KeyChild a key for a EntityB is stored. Because those fields are longer than the corresponding key fields in TableA and TableB the strings are right padded th 14 charaters.
And the JPA mappings
In the EntityA class we have:
@Table(name="TableA")
public class EntityA {
@Id
@Column(name="KeyA")
private String key;
}
In the EntityB class we have:
@Table(name="TableB")
public class EntityB {
@Id
@Column(name="KeyB")
private String key;
}
In the ABRelation class we have:
@Table(name="TableC")
public class ABRelation {
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="KeyParent", referencedColumnName="KeyA", nullable=false, insertable=false, updatable=false)
private EntityA parent;
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="KeyChild", referencedColumnName="KeyB", nullable=false, insertable=false, updatable=false)
private EntityB child;
}
The problem in this example is that we don't get any ABRelations, even though there are a number of them defined in TableC. The problem exists when using a DB2 or a Oracle database in MS SQL Server it works.
So my assumption is that it has to do with the fact that we have CHAR fields with different length storing the same key and when joining we don't get a hit.
So the questions are:
1. Is the above assumption correct?
2. Is there a way to tweak the JPA definition so when joining two char columns of different length we ignore the right padded spaces?
So in some kind of very simplified pseudo SQL way what we want to achieve is:
What we do today is:
SELECT * from TableC c, TableA a, TableB b where c.KeyParent = a.KeyA and c.KeyChild = b.KeyB
What we like to do is something like:
SELECT * from TableC c, TableA a, TableB b where RTRIM(c.KeyParent) = RTRIM(a.KeyA) and RTRIM(c.KeyChild) = RTRIM(b.KeyB)
] 
Search
Help
Register
Login
Home
