JPA, Composite Key consisted of foreign key and table column members

Question

Greetings to the community,

I am struggling all day to find a solution to the issue below.

The scenario is the following, I have a table

---TABLE_ONE--- INT ID VARCHAR NAME PRIMARY_KEY (ID)

and my other table consisted of three columns which consist together a composite key

---TABLE_TWO--- INT TABLE_ONE_ID (FK -> TABLE_ONE.ID) VARCHAR NAME VARCHAR EMAIL PRIMARY_KEY(TABLE_ONE_ID, NAME, EMAIL)

The relationship I want to achieve is that the TABLE_ONE entity will have a list of objects from the TABLE_TWO (one-to-many relationship).

I tried to do this with as shown below.

@Entity
@Table(name = "TABLE_ONE")
public class TableOne {

  @Column(name="id")
  private int id;
  @Column(name="name")
  private String name
  @OneToMany(fetch = FetchType.EAGER, mappedBy = "tableOne")
  private List<TableTwo> tableTwoList;
  //getters, setters, constructors        
}

@Entity
@Table(name = "TABLE_TWO")
public class TableTwo {

  @EmbeddedId
  private TableTwoCompositeId tableTwoCompositeId;
  @ManyToOne
  @JoinColumn(name = "TABLE_ONE_ID", referencedColumnName = "ID", insertable = false, updatable = false)
  private TableOne tableOne;
  //getters, setters, constructors        
}


@Embeddable
public class TableTwoCompositeId {
    @Column(name = "TABLE_ONE_ID")
    public Integer provider;
    @Column(name = "NAME")
    public String name;
    @Column(name = "EMAIL")
    public String email;
    //getters, setters, constructors
}

However, I'm getting javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet and Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist when a TableOne object is retrieved from the database.

Thanks in advance for any help!

Answer 1

I think you need several minor changes:

• TableOne.id needs an @Id annotation
• The type of TableTwoCompositeId.provider should match the type of TableOne.id
• TableTwo.tableOne needs a @MapsId annotation to indicate it maps TableTwoCompositeId.provider

Here is how the code should look:

@Entity
@Table(name = "TABLE_ONE")
public class TableOne {

  @Id
  @Column(name="id")
  private int id;
  @Column(name="name")
  private String name
  @OneToMany(fetch = FetchType.EAGER, mappedBy = "tableOne")
  private List<TableTwo> tableTwoList;
  //getters, setters, constructors        
}

@Entity
@Table(name = "TABLE_TWO")
public class TableTwo {

  @EmbeddedId
  private TableTwoCompositeId tableTwoCompositeId;
  @MapsId("provider") // maps provider attribute of embedded id
  @ManyToOne
  @JoinColumn(name = "TABLE_ONE_ID", referencedColumnName = "ID", insertable = false, updatable = false)
  private TableOne tableOne;
  //getters, setters, constructors        
}


@Embeddable
public class TableTwoCompositeId {
    @Column(name = "TABLE_ONE_ID")
    public int provider;
    @Column(name = "NAME")
    public String name;
    @Column(name = "EMAIL")
    public String email;
    //getters, setters, constructors
}