Hello,
I have a Person and the Person has a current status. The status can change, but I want to keep track of the status. That means that the person entity has two links two the status table. A one-to-one for the current status and one-to-many for the status history.
Code:
CREATE TABLE "Person" (
"ID" NUMBER(19,0) NOT NULL,
"NAME" VARCHAR2(256) NOT NULL,
CONSTRAINT "PERSON_PK" PRIMARY KEY ("ID")
);
CREATE TABLE "Status" (
"ID" NUMBER(19,0) NOT NULL,
"NAME" VARCHAR2(256) NOT NULL,
"STATUS_DATE" TIMESTAMP NOT NULL,
"PERSON_ID" NUMBER(19,0) NOT NULL,
CONSTRAINT "PERSON_PK" PRIMARY KEY ("ID"),
CONSTRAINT "STATUS_PERSON_FK" FOREIGN KEY ("PERSON_ID") REFERENCES "PERSON" ("ID")
);
Here are the mappings as I have them right now:
Code:
@Entity(name = "PERSON")
public class Person {
private Set<Status> statusHistory;
private Status currentStatus;
@OneToMany(mappedBy = "person", fetch = FetchType.LAZY)
public List<Status> getStatusHistory() {
return statusHistory;
}
public void setStatusHistory(List<Status> statusHistory) {
this.statusHistory = statusHistory;
}
@OneToOne(mappedBy = "person",cascade = CascadeType.ALL)
@Where(clause = "CREATE_DATE = (SELECT MAX(s.CREATE_DATE) FROM STATUS s WHERE s.PERSON_ID = PERSON_ID)")
public Status getCurrentStatus() {
return this.currentStage;
}
public void setCurrentStatus(Status status) {
this.currentStatus = status;
}
}
@Entity(name="STATUS")
public class Status {
private Person person;
@ManyToOne()
@JoinColumn(name = "PERSON_ID")
public Person getPerson() {
return this.person;
}
public void setPerson(Person person) {
this.person = person;
}
}
This model breaks on the one-to-one from Person to Status. It tells me that there are multiple rows in the result set. Looking at the SQL output I also see that the Where clause is ignored.
Do you have any suggestions on how to solve this problem in a good way? The person table could be rather big (over a million) and the status table could be much bigger. Therefore I am looking for a solution that would perform will with bigger datasets.
Thanks,
Johannes
Login
FAQ
Search