[FIXED] JPA @OneToOne Mapping a relationship with ReadOnly Oracle data without primary key

Issue

Preamble
An Oracle DB read-only(I don't have access) has the following two tables:

person

person table

| id   | name   | gender |
| --   | ------ | ------ |
| 2001 | Moses  | M      |
| 2002 | Luke   | M      |
| 2003 | Maryam | F      |

PK(id)

reference

reference table

| sep   | guid     | table_name |
| ---   | -------- | ---------- |
| 2001  | EA48-... | person     |
| 2002  | 047F-... | person     |
| 2003  | B23F-... | person     |
| 2003  | 3E3H-... | address    |
| 2001  | H2E0-... | address    |
| 2001  | 92E4-... | report     |

No PK, it is generated by some triggers

The person table is a straight forward table with a primary key. The reference table are generated via a trigger that stores the id(PK) in sep column of any table and the table name that is store in table_name column (Note: Since no primary key, the reference table stores duplicate values in the sep column but distinct value into guid.)

Requirement

I need to use JPA to get the record from the reference table and map to the person record (person.id and other table.id are stored in reference.sep column) using Jackson as follows

{
 "id": 2001, 
 "name": "Moses", 
 "gender": "M", 
 "reference": {
   "sep": 2001, 
   "guid": "EA48-...",
   "tableName": "person"
 }
}

Entity (Person)

@Entity
@Table(name="person")
public class Person implements Serializable {
  @Id
  private Long id;
  private String name;
  private String gender;

  @OneToOne
  @JoinColumn(name = "id", referencedColumnName = "sep", insertable = false, updatable = false)
  private Reference reference;

 // Getters & Setters
}

Entity (Reference)

@Entity
@Table(name="reference")
public class Reference implements Serializable {
  private Long sep;
  private String guid;
  private String tableName;

  //Getters & Setters
}

Problem 1
JPA throws error of no @Id annotation on Reference table.

Problem 2
If I add the @Id annotation on the sep field, JPA throws error of duplicate values for that column.

Problem 3
If I add the @Id annotation on the guid field (it is unique field), JPA throws error of mapping a Long to a String field (org.hibernate.TypeMismatchException: Provided id of the wrong type for class)

Question
How can I structure the entities (Person.java and Reference.java) in order to come up with the output below:

{
 "id": 2001, 
 "name": "Moses", 
 "gender": "M", 
 "reference": {
   "sep": 2001, 
   "guid": "EA48-...",
   "tableName": "person"
 }
}

Solution

For the benefit of anyone looking to solve this kind of issue, I will be posting the solution that works for me following @XtremeBaumer suggestion in the comment.

Step 1: For the REFERENCE table, I made the JPA entity to have two ids (sep & table_name) by creating an extra composite Id class and using it in the Reference Entity.

public class RefId {
  private Long sep;
  private String tableName;
  
  //All args constructor
  //No args constructor
  //Setters & Getters
  //Override the equals() and hashCode() !very important
}

Step 2: Add the above class as a composite id to the Reference entity by using the @IdClass annotation. We must also declare and annotate the two fields with @Id in the Reference class.

@Entity
@Table(name="reference")
@IdClass(RefId.class) // Important if not using embeddable type
public class Reference implements Serializable {
  @Id
  private Long sep;
  private String guid;
  @Id
  private String tableName;

  //Getters & Setters
}

Step 3: In the Person entity, declare @OneToOne on the Reference entity and annotate it with @JoinColumnsOrFormulas as shown below:

@Entity
@Table(name="person")
public class Person implements Serializable {
  @Id
  private Long id;
  private String name;
  private String gender;

  @OneToOne
  @JoinColumnsOrFormulas(value = {
    @JoinColumnOrFormula(column = @JoinColumn(name = "id", referencedColumnName = "sep", insertable = false, updatable = false)),
    @JoinColumnOrFormula(formula = @JoinFormula(value = "'person'", referencedColumnName = "tableName"))
  })
  private Reference reference;

 // Getters & Setters
}

This works fine in the scenario above. Note in the formula = @JoinFormula, it is like we are declaring the 'WHERE' clause i.e. WHERE table_name = 'person' (Don't miss the single quotes)

Lastly, by using the Jackson object mapper, I was able to get

{
 "id": 2001, 
 "name": "Moses", 
 "gender": "M", 
 "reference": {
   "sep": 2001, 
   "guid": "EA48-...",
   "tableName": "person"
 }
}

Thanks for your insight (@XtremeBaumer)

Answered By - Habeeb Okunade
Answer Checked By - Clifford M. (JavaFixing Volunteer)