Need help with setting up table relationships. So, there are 2 entities - Section and Period. 1 Section has many Period, but 1 Period do not belongs to any specific Section. I implemented this relationship as follows: created an additional table SectionCodes with an external key on Section (more in diagram)

Section class:

@Entity
@Table(name = "section")
@AttributeOverride(name = "id", column = @Column(name = "id", nullable = false))
public class Section extends BaseEntity<Integer> {
    private String form;
    private Integer version;
    private List<SectionPeriod> periodCodes;

    @Column(name = "form")
    public String getForm() { return form; }
    public void setForm(String form) { this.form = form;}

    @Column(name = "version")
    public Integer version() { return version; }
    public void setVersion(Integer version) { this.version = version; }

    @OneToMany(mappedBy = "section", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    public List<SectionPeriod> getPeriodCodes() {
        return periodCodes;
    }
    public void setPeriodCodes(List<SectionPeriod> periodCodes) {
        this.periodCodes = periodCodes;
    }
}

SectionPeriod:

@Entity
@Table(name="section_period")
@AttributeOverride(name = "id", column = @Column(name = "id", nullable = false))
public class SectionPeriod extends BaseEntity<Integer> {
    private Integer periodCode;
    private Section section;

    @Column(name = "period_code")
    public Integer getPeriodCode() {
        return periodCode;
    }
    public void setPeriodCode(Integer periodCode) {
        this.periodCode = periodCode;
    }

    @ManyToOne()
    @JoinColumn(name = "section_id", referencedColumnName = "id", nullable=false)
    public Section getSection () {
        return section;
    }
    public void setSection(Section section) {
        this.section = section;
    }
}

This works fine, but there is a problem - it turns out that every Section entity has a SectionPeriod list, and SectionPerid has a Section - so there is loop. Is there an easy way to perform this? Ideally, it should be just List of Period in Section class or at least Integer[] of periodCodes.

CodePudding user response：

To model the Many To Many relationship between Period and Section, you have 2 choices:

1. Use 2 Entities (Period, Section), and use the @ManyToMany annotation in one of these entities. This will autogenerate the Join Table which will have a composite primary key. This is not recommended.
2. Use 3 Entities, and this way you can think the "ManyToMany" as having two OneToMany relationships (1 Period - Many "PeriodSection" AND 1 Section - Many "PeriodSection"). This is recommended because this way you will have a PK and you can easily add more columns to the PeriodSection (** Note: instead of PeriodSection, the Entity and Table should have a meaninful name, but i'll leave that to you)

Using the second choice:

Period:

@Entity
@Table(name = "periods")
public class Period {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private Integer code;

private String name;


@OneToMany(mappedBy = "period", cascade = CascadeType.MERGE, fetch = FetchType.LAZY)
private List<PeriodSection> periodSections;  // bad naming.

}

PeriodSection:

@Entity
@Table(name = "period_section")  // use a more meaningful name for the table and the entity
public class PeriodSection {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@ManyToOne(cascade = CascadeType.MERGE)
@JoinColumn(name = "period_id", referencedColumnName = "id")
private Period period;

@ManyToOne(cascade = CascadeType.MERGE)
@JoinColumn(name = "section_id", referencedColumnName = "id")
private Section section;

}

Section:

@Entity
@Table(name = "sections")
public class Section {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

private Integer version;

private String form;


@OneToMany(mappedBy = "section", cascade = CascadeType.MERGE, fetch = FetchType.LAZY)
private List<PeriodSection> periodSections;  // bad naming.

}

You can play around with the fetch and cascade strategies, to check which is best for your app. Hope this helped