Joining on a varchar to a char does not work in JPA (oracle)

Question

I have an existing DB that two tables are joined based on a string. The problem is that one table column is defined as a varchar2 and the other is a char. Oracle will pad the char with spaces and the varchar2 will not so that when JPA tries to join the two one has space and one does not so the equality is not met.

How can I get the join to work in JPA? Is there a JPA annotation that would do the trick?

Answer 1

What I finally did was to use @Subselect.

@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
@javax.persistence.Entity
@Table( schema="SECURITYDBO", name = "XREFERENCE")
@Subselect("SELECT " +
" ,cast( trim(aCharColumn) as varchar(100) ) AS aCharColumn" + // NEED TO TRIM SO THAT JPA INNER JOINED BETWEEN 2 tables WHICH IS A VARCHAR AND THIS ONE THERE WILL NOT BE ANY SPACES PADDED
"FROM MYTABLE ")
public class MyJPA extends XReference{
....