JPA @OneToOne Mapping a relationship with ReadOnly Oracle data without primary key-CodePudding

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"
 }
}

CodePudding user response：

Reference is the owner of the relationship and needs to be specified as such in either a unidirectional or bidirectional relationship

// Unidirection relationship
@Entity
public class Person implements Serializable {
    @Id
    private Long id;
    private String name;
    private String gender;
    // Getters & Setters
}

@Entity
public class Reference implements Serializable {

    @Id
    private String guid;
    private String tableName;

    @OneToOne
    @JoinColumn(name = "sep", insertable = false, updatable = false)
    private Person person;
    //Getters & Setters
}

// Bidirection relationship
@Entity
public class Person implements Serializable {
    @Id
    private Long id;
    private String name;
    private String gender;

    @OneToOne(mappedBy = "person")
    private Reference reference;
    // Getters & Setters
}

@Entity
public class Reference implements Serializable {

    @Id
    private String guid;
    private String tableName;

    @OneToOne
    @JoinColumn(name = "sep", insertable = false, updatable = false)
    private Person person;
    //Getters & Setters
}

CodePudding user response：

Same example for read any kind records from table reference:

@Entity
@Table(name = "reference")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "table_name")
public abstract class AbstractReferenceEntity {
    @Id
    private UUID guid;

    public UUID getGuid() {
        return guid;
    }

    public void setGuid(UUID guid) {
        this.guid = guid;
    }
}

@Entity
@DiscriminatorValue("person")
public class PersonReferenceEntity extends AbstractReferenceEntity {
    @OneToOne
    @JoinColumn(name = "sep")
    private Person person;

    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }
}

// Read all types of records.
AbstractReferenceEntity e = this.em.find(AbstractReferenceEntity.class, sameUuid));

// Read only person type of records.
AbstractReferenceEntity e = this.em.find(PersonReferenceEntity, sameUuid);