JPA Join Tables with two fields, one of them a primary key

Question

the problems is when @ManyToOne make a @Joincolumn ID_REPORT (it´s a primary key ) and @Joincolumn ID_TEMPLATE_DEFAULT

Repeated column in mapping for entity: CurReport column: id_report (should be mapped with insert="false" update="false")

Code

First table CUR_TEMPLATE

CREATE TABLE CUR_TEMPLATE 
   (    
    ID_REPORT NUMBER(5,0) NOT NULL, 
    ID_TEMPLATE NUMBER(5,0) NOT NULL, 
    -- Other fields
   );
 
ALTER TABLE CUR_TEMPLATE ADD CONSTRAINT PK_CUR_TEMPLATE PRIMARY KEY (ID_REPORT, ID_TEMPLATE)

-- CUR_TEMPLATE foreign keys
ALTER TABLE CUR_TEMPLATE ADD CONSTRAINT FK_CUR_PLAN_REFERENCE_CUR_REPO FOREIGN KEY (ID_REPORT)
      REFERENCES CUR_REPORTS (ID_REPORT);

Second table CUR_REPORTS

-- CUR_REPORTS definition
CREATE TABLE CUR_REPORTS 
   (    
    ID_REPORT NUMBER(3,0) NOT NULL, 
    NAME_REPORT VARCHAR2(100) NOT NULL, 
    -- other fields 
    ID_TEMPLATE_DEFAULT NUMBER(5,0), 
    -- other fields
   ) ;

ALTER TABLE CUR_REPORTS ADD CONSTRAINT PK_CUR_REPORTS PRIMARY KEY (ID_REPORT)

ALTER TABLE CUR_REPORTS CONSTRAINT FK_CUR_REPO_REFERENCE_CUR_PLAN FOREIGN KEY (ID_REPORT, ID_TEMPLATE_DEFAULT)
      REFERENCES CUR_TEMPLATE (ID_REPORT, ID_TEMPLATE) 

First table CUR_REPORTS Entity CurReport

@Entity
@Table(name = "CUR_REPORTS")
@IdClass(CurPlantillaPK.class)
@Getter
@Setter
public class CurReport {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID_REPORT", nullable = false)
    private Long id;

    @Column(name = "NAME_REPORT", nullable = false, length = 100)
    private String nombreReporte;

    @ManyToOne(fetch = FetchType.LAZY) <---WHERE IS THE PROBLEM 
    @JoinColumn(name = "ID_REPORT", referencedColumnName = "ID_REPORTE")
    @JoinColumn(name = "ID_TEMPLATE_DEFAULT", referencedColumnName = "ID_TEMPLATE")
    private CurTemplate curTemplate;

    @OneToMany(mappedBy = "curReport")
    private Set<CurTemplate> curTemplates= new LinkedHashSet<>();
}

Second table CUR_TEMPLATE Entity CurReport

@Entity
@Table(name = "CUR_TEMPLATE")
@IdClass(CurPlantillaPK.class)
@Getter
@Setter
public class CurTemplate {
    
    @Id
    @Column(name = "ID_REPORT", nullable = false)
    private Long idReport;

    @Id
    @Column(name = "ID_TEMPLATE", nullable = false)
    private Long idTemplate;

    
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_REPORT", foreignKey = @ForeignKey(name = "FK_CUR_PLAN_REFERENCE_CUR_REPO"), referencedColumnName = "ID_REPORT", insertable = false, updatable = false)
    private CurReport curReport;

}

When i add insertable=false, updatable=false

@JoinColumn(name = "ID_REPORT", referencedColumnName = "ID_REPORT", insertable=false, updatable=false)

said

Mixing insertable and non insertable columns in a property is not allowed: CurTemplate

How could i map those relationships? How resolve the @JoinColumn when one field of the FK are column PK?

Answer 1

You can use a derived identity and map CurTemplate like this:

@Entity
@Table(name = "CUR_TEMPLATE")
@IdClass(CurTemplatePK.class)
@Getter
@Setter
public class CurTemplate {

    @Id
    @Column(name = "ID_TEMPLATE", nullable = false)
    private Long idTemplate;

    @Id
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_REPORT", foreignKey = @ForeignKey(name = "FK_CUR_PLAN_REFERENCE_CUR_REPO"), referencedColumnName = "ID_REPORT", insertable = false, updatable = false)
    private CurReport curReport;

}

Then you will need an @IdClass like this:

public class CurTemplatePK {
    Long idTemplate; // matches name of @Id attribute
    Long curReport; // matches name of @Id attribute and type of CurReport PK
}

Then you should use a basic mapping for the default template key and provide a getter for the default template object:

@Entity
@Table(name = "CUR_REPORTS")
@IdClass(CurPlantillaPK.class)
@Getter
@Setter
public class CurReport {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID_REPORT", nullable = false)
    private Long id;

    @Column(name = "NAME_REPORT", nullable = false, length = 100)
    private String nombreReporte;

    @Column(name = "ID_TEMPLATE_DEFAULT")
    private Long idDefaultTemplate;

    @OneToMany(mappedBy = "curReport")
    private Set<CurTemplate> curTemplates= new LinkedHashSet<>();

    public CurTemplate getDefaultTemplate() {
        return this.curTemplates.stream()
                .filter(template -> template.getIdTemplate().equals(idDefaultTemplate))
                .findFirst()
                .orElse(null);
    {
}

If you want to allow clients to set the default template, you will need to implement a setter that first verifies that the new default template is already in the set curTemplates.