Mapping JPA Entities

Question

I have a number of tables

programme       episode         performer       prog_ep_perf
----------      ---------       ---------       ------------
programmeId     episodeId       performerId     programmeId
progTitle       episodeTitle    performerName   episodeId
                programmeId                     performerId

prog_ep_perf is a join table (programmeId in the episode table might be redundant because of the join table?). I've worked out the following relationships, which I think are correct

programme
  @OneToMany
  episode     --> One programme can have many episodes
  @OneToMany
  performers  --> One programme can have many performers

episode
  @OneToOne
  programme  --> One episode links to one programme
  @OneToMany
  performers --> One episode can have many performers

performer
  @OneToMany
  programme  --> One performer can have many programmes
  @OneToMany
  episode    --> One performer can have many episodes

Here's how I've set up the entities, is this correct?

@Entity
@Table(name = "PROGRAMME")
public class Programme {

  @Id
  @GeneratedValue(generator="increment")
  @GenericGenerator(name="increment", strategy="increment")
  private Long programmeId;

  private String progTitle;

  @OneToMany(      
      targetEntity=Performer.class,
      cascade={CascadeType.PERSIST, CascadeType.MERGE}
  )
  @JoinTable(
      name="PROG_EP_PERF",
      joinColumns=@JoinColumn(name="PROGRAMMEID"),
      inverseJoinColumns={@JoinColumn(name="PERFORMERID")}
  )
  private Set<Performer> performers;

  @OneToMany(      
      targetEntity=Performer.class,
      cascade={CascadeType.PERSIST, CascadeType.MERGE}
  )
  @JoinTable(
      name="PROG_EP_PERF",
      joinColumns=@JoinColumn(name="PROGRAMMEID"),
      inverseJoinColumns={@JoinColumn(name="EPISODEID")}
  )
  private Set<Episode> episodes;
}

@Entity
@Immutable
@Table(name = "EPISODE")
public class Episode {

  @Id
  @GeneratedValue(generator="increment")
  @GenericGenerator(name="increment", strategy="increment")
  private Long episodeId;

  @Type(type="com.springtests.model.Programme")
  @OneToOne( cascade = {CascadeType.PERSIST, CascadeType.MERGE} )
  @JoinColumn(name="PROGRAMMEID")
  private Programme programme;

  private String episodeTitle;

  @OneToMany(      
      targetEntity=Performer.class,
      cascade={CascadeType.PERSIST, CascadeType.MERGE}
  )
  @JoinTable(
      name="PROG_EP_PERF",
      joinColumns=@JoinColumn(name="EPISODEID"),
      inverseJoinColumns={@JoinColumn(name="PERFORMERID")}
  )
  private Set<Performer> performers;

}

@Entity
@Immutable
@Table(name = "PERFORMER")
public class Performer {

  @Id
  @GeneratedValue(generator="increment")
  @GenericGenerator(name="increment", strategy="increment")
  private Long performerId;      

  private String performerName;

  @OneToMany(      
      cascade = {CascadeType.PERSIST, CascadeType.MERGE},
      targetEntity = Programme.class
  )
  @JoinTable(
      name="PROG_EP_PERF",
      joinColumns=@JoinColumn(name="PERFORMERID"),
      inverseJoinColumns={@JoinColumn(name="PROGRAMMEID")}
  )
  public Set<Programme> programmes;

  @OneToMany(      
      cascade = {CascadeType.PERSIST, CascadeType.MERGE},
      targetEntity = Programme.class
  )
  @JoinTable(
      name="PROG_EP_PERF",
      joinColumns=@JoinColumn(name="PERFORMERID"),
      inverseJoinColumns={@JoinColumn(name="EPISODEID")}
  )
  public Set<Episode> episodes;

}

Answer 1

If the table is fixed and you just need Entities to represent what is in the database, then it looks like programme has a OneToMany to episode and episode has a ManyToOne back to programme using the episode.programmeId field as a foreign key:

public class Programme {
  ..
  @OneToMany(mappedby="programme")
  private Set<Episode> episodes;
  ..

public class Episode {
  ..
  @ManyToOne
  @JoinColumn(name = "programmeId")
  private Programme programme;
  ..

As the Episode.programme relationship controls the foreign key in the database, it is up to you if you want to actually add the Programme.episodes collection. If you do, you have to maintain it yourself to keep both sides in sync when you make changes. Ie JPA will not automatically fix the programme reference if you add an episode to a collection, nor will it add the episode to the programme's collection when you point an episode at it.

As for the prog_ep_perf table, this is a 3 way mapping that might be difficult to handle. If all 3 foreign keys are to be populated for each row, then it cannot be mapped the way you laid out, as by breaking it into multiple 1:M mappings, one foreign key will always be null.

Instead, the best solution is to map the prog_ep_perf table as an entity so you can handle it exactly as you want or need in your application. An alternative is to use a Map as the collection type with a ManyToMany relationship. See http://en.wikibooks.org/wiki/Java_Persistence/ManyToMany#Mapping_a_Join_Table_with_Additional_Columns for additional information (JPA 1.0) and for JPA 2.0 see https://wiki.eclipse.org/EclipseLink/Examples/JPA/2.0/MapKeyColumns and http://docs.oracle.com/javaee/6/api/javax/persistence/MapKeyJoinColumn.html, but it might look like:

public class Programme {
  ..
  @ManyToMany
  @JoinTable(name="prog_ep_perf",
                   joinColumns=@JoinColumn(name="episodeId"),
                   inverseJoinColumns=@JoinColumn(name="programmeId"))
  @MapKeyJoinColumn(name="performerId")
  Map<Performer, Episode> prog_ep_perf;
  ..
}