JPA entity with join possibly on null key

Question

I'm building an application based on JavaEE7 with JPA 2.1 and EJB 3.2. I'm trying to define entity with JPA based on the table from an Oracle database. I use EclipseLink as implementation of JPA.

My case is to retrieve translation stored on the database. All the translation are stored on a single table and identified with a key and a language id.

I can't change the way the database is organised and I'm a beginner in JPA.

---

Here an example of lines from the translation table:

TRANSLATION:

TRANS_ID | TRANS_KEY                     | TRANS_VALUE | LAN_ID
1        | APPVERSION_APPV_COMMENT_1215  | Du texte    | 1
2        | APPVERSION_APPV_COMMENT_1215  | Some text   | 2

Where LAN_ID : 1 correspond to the french translation and LAN_ID : 2 is the english translation.

The entity of TRANSLATION looks like this:

@Entity
@Table(name = "TRANSLATION")
@NamedQueries({
    @NamedQuery(name="Translation.findAll", query="SELECT trans FROM Translation trans")
})
public class Translation implements Serializable {

    @Id
    @Column(name = "TRANS_ID", nullable = false)
    private Integer translationId;

    @Column(name = "TRANS_KEY", nullable = false)
    private String translationKey;

    @Column(name = "TRANS_VALUE")
    private String translationValue;

    @Column(name = "LAN_ID", nullable = false)
    private Integer languageId;

}

---

And here is a possible case for a table using translations:

APPVERSION :

APPV_ID | APPV_COMMENT
0       | APPVERSION_APPV_COMMENT_1215
1       | (null)
2       | some texte with no reference to translation table

Here are the tree possible case I can have in column witch use translation. The third case is due to historical reasons.

I would like to implement a JPA entity to represent the table APPVERSION where I could retrieve the translation without using JPQL.

I tried an entity looking like this (simplified version):

@Entity
@Table(name = "APPVERSION")
@NamedQueries({
    @NamedQuery(name="AppVersion.findAll", query="SELECT app FROM AppVersion app")
})
public class AppVersion implements Serializable {

    @Id
    @Column(name = "APPV_ID", nullable = false)
    private Integer applicationVersionId;

    @OneToMany()
    @JoinColumn(name = "TRANS_KEY", referencedColumnName = "APPV_COMMENT", nullable = true)
    //As a beginer who don't get it all I also tryed the following. With quite the same result
    //@JoinColumn(name = "APPV_COMMENT", referencedColumnName = "TRANS_KEY", nullable = true)
    private List<Translation> applicationVersionComment;

}

With, the following

@JoinColumn(name = "TRANS_KEY", referencedColumnName = "APPV_COMMENT", nullable = true)

I got an error saying:

Caused By: Exception [EclipseLink-6094] (Eclipse Persistence Services - 2.6.1.v20150916-55dc7c3): org.eclipse.persistence.exceptions.QueryException
Exception Description: The parameter name [APPV_COMMENT] in the query's selection criteria does not match any parameter name defined in the query.

And with the following

@JoinColumn(name = "APPV_COMMENT", referencedColumnName = "TRANS_KEY", nullable = true)

I got an error saying:

Caused By: Exception [EclipseLink-6094] (Eclipse Persistence Services - 2.6.1.v20150916-55dc7c3): org.eclipse.persistence.exceptions.QueryException
Exception Description: The parameter name [TRANS_KEY] in the query's selection criteria does not match any parameter name defined in the query.

I would like to know how to handle such case with JPA entity. For historical reason the data are quite messy and sadly I have to deal with it as it is.