3

I have an entity called 'Instructions'. Sometimes each Instructions has to keep track Instructions before and after it. For Example I have new instruction B which is continuing from existing instruction A, Instruction B has to aware that Instruction A is the previous instruction, while Instruction A also has to know that Instruction B is the next after it. Not every Instruction will have before and after Instruction.

How to implement this in JPA(EclipseLink): [one-to-one + self referential + bidirectional] relation?

So far (not working yet) i came up with this:

mysql db:

CREATE TABLE instructions (
instruction_id int(11) NOT NULL AUTO_INCREMENT,
instruction_title varchar(100) NOT NULL,
instruction_text varchar(999) NOT NULL,
instruction_previous_id int(11) DEFAULT NULL,
PRIMARY KEY (instruction_id),
CONSTRAINT instructions_ibfk_3 
FOREIGN KEY (instruction_previous_id) 
REFERENCES instructions (instruction_id));

entity:

@Entity
@Table(name = "instructions")
public class Instructions implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "instruction_id")
private Integer instructionId;
@Basic(optional = false)
@Column(name = "instruction_title")
private String instructionTitle;
@Basic(optional = false)
@Column(name = "instruction_text")
private String instructionText;

@JoinColumn(name="instruction_previous_id", referencedColumnName = "instruction_id", nullable = true)
@OneToOne(optional = true)
private Instructions instructionPrevious;
@OneToOne(cascade = CascadeType.ALL, mappedBy = "instructionPrevious")
private Collection<Instructions> instructionNextCollection;
// other properties, setter & getter
}

Currently no problem at create new Instruction, had error at reading

Instructions instruction = em.find(Instructions.class, instructionId);
instruction.getInstructionNextCollection().size(); //error this line

Local Exception Stack: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'atiits.instructions_instructions' doesn't exist Error Code: 1146 Call: SELECT t1.instruction_id, t1.instruction_urgent, t1.instruction_uploaded_by, t1.instruction_translate, t1.instruction_title, t1.instruction_type, t1.instruction_translate_received, t1.instruction_is_cancelled, t1.instruction_translate_sent, t1.instruction_had_workorder, t1.instruction_text, t1.instruction_update_date, t1.instruction_update_by, t1.instruction_create_by, t1.instruction_translator, t1.instruction_create_date, t1.instruction_company_id, t1.instruction_previous_id, t1.instruction_status_id FROM instructions_instructions t0, instructions t1 WHERE ((t0.Instructions_instruction_id = ?) AND (t1.instruction_id = t0.instructionNextCollection_instruction_id)) bind => [874] Query: ReadAllQuery(name="instructionNextCollection" referenceClass=Instructions sql="SELECT t1.instruction_id, t1.instruction_urgent, t1.instruction_uploaded_by, t1.instruction_translate, t1.instruction_title, t1.instruction_type, t1.instruction_translate_received, t1.instruction_is_cancelled, t1.instruction_translate_sent, t1.instruction_had_workorder, t1.instruction_text, t1.instruction_update_date, t1.instruction_update_by, t1.instruction_create_by, t1.instruction_translator, t1.instruction_create_date, t1.instruction_company_id, t1.instruction_previous_id, t1.instruction_status_id FROM instructions_instructions t0, instructions t1 WHERE ((t0.Instructions_instruction_id = ?) AND (t1.instruction_id = t0.instructionNextCollection_instruction_id))") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:687)

Perception
  • 79,279
  • 19
  • 185
  • 195
blukit
  • 113
  • 2
  • 5
  • 12
  • Possible duplicate of http://stackoverflow.com/questions/3393515/jpa-how-to-have-one-to-many-relation-of-the-same-entity-type – perissf Feb 16 '12 at 08:20

1 Answers1

4

There's some confusion in your example whether each Instruction can be followed by a single Instruction or by many.

If it's a single, then don't use a collection for instructionNext.

If it's many, then the example code in JPA: How to have one-to-many relation of the same Entity type should help. You need @ManyToOne for the preceding instruction and @OneToMany for the following, rather than @OneToOne.

Community
  • 1
  • 1
Andrew Spencer
  • 15,164
  • 4
  • 29
  • 48
  • thx for your response :D, each Instruction can only be followed by a single Instruction (one-to-one) , so what should i use for declaring instructionNext? – blukit Feb 17 '12 at 14:17
  • 1
    Just replace `Collection` by `Instructions` - does that work? – Andrew Spencer Feb 21 '12 at 14:04