title: JPA: one-to-one + self referential + bidirectional
body: <p>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.</p>
  
  <p><strong>How to implement this in JPA(EclipseLink): [one-to-one + self referential + bidirectional] relation?</strong></p>
  
  <p>So far (not working yet) i came up with this:</p>
  
  <p>mysql db:</p>
  
  <pre><code>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));
  </code></pre>
  
  <p>entity:</p>
  
  <pre><code>@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&lt;Instructions&gt; instructionNextCollection;
  // other properties, setter &amp; getter
  }
  </code></pre>
  
  <p>Currently no problem at create new Instruction, had error at reading</p>
  
  <pre><code>Instructions instruction = em.find(Instructions.class, instructionId);
  instruction.getInstructionNextCollection().size(); //error this line
  </code></pre>
  
  <p><code>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 =&gt; [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)</code></p>