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