JPA error: Generated Insert statement has more columns than existing in DB [Column not unique]

[former_member280281]

Hello Everyone,

I have the following database tables:

create table "SAPN73DB"."OEE_KPI"(
"CLIENT" VARCHAR (3) UNICODE not null,
"KPI" VARCHAR (20) UNICODE not null,
"KPI_TYPE" VARCHAR (50) UNICODE,
"UOM_OF_TARGETS" VARCHAR (50) UNICODE,
constraint SYSPRIMARYKEY primary key ("CLIENT","KPI"))

create table "SAPN73DB"."OEE_KPIT"(
"CLIENT" VARCHAR (3) UNICODE not null,
"KPI" VARCHAR (20) UNICODE not null,
"LANG" VARCHAR (2) UNICODE not null,
"DESCRIPTION" VARCHAR (50) UNICODE,
constraint SYSPRIMARYKEY primary key ("CLIENT","KPI","LANG"))



The "OEE_KPI" is the parent table and "OEE_KPIT" is the child table, so there is a one to many relationship. The Join columns are "CLIENT" and "KPI".

Hence I created the corresponding JPA classes:

1. @Entity 
2. @Table(name = "OEE_KPI")  
3. public class KPI implements Serializable {  
4.     private static final long serialVersionUID = 1L;  
5.  
6.     @EmbeddedId 
7.     private KPIPK id;  
8.     @Column(name = "KPI_TYPE")  
9.     private String kpiType;  
10.     @Column(name = "UOM_OF_TARGETS")  
11.     private String uomOfTargets;  
12.  
13.     // bi-directional many-to-one association to KPIText  
14.     @OneToMany(mappedBy = "oeeKpi", cascade = CascadeType.ALL)  
15.     private List<KPIText> KPIText;  
16.  
17.     public KPI() {  
18.     }  
19.  
20.     public String getKpiType() {  
21.         return this.kpiType;  
22.     }  
23.     public void setKpiType(String kpiType) {  
24.         this.kpiType = kpiType;  
25.     }  
26.     public String getUomOfTargets() {  
27.         return this.uomOfTargets;  
28.     }  
29.     public void setUomOfTargets(String uomOfTargets) {  
30.         this.uomOfTargets = uomOfTargets;  
31.     }  
32.     public KPIPK getId() {  
33.         return id;  
34.     }  
35.     public void setId(KPIPK id) {  
36.         this.id = id;  
37.     }  
38.     public List<KPIText> getKPIText() {  
39.         return KPIText;  
40.     }  
41.     public void setKPIText(List<KPIText> kPIText) {  
42.         KPIText = kPIText;  
43.     }  
44. } 

1. @Entity 
2. @Table(name = "OEE_KPIT")  
3. public class KPIText implements Serializable{  
4.  
5.     private static final long serialVersionUID = 1L;  
6.  
7.     @EmbeddedId 
8.     private KPITextPK id;  
9.     @Column(name="DESCRIPTION")  
10.     private String description;  
11.       
12.     //bi-directional many-to-one association to KPI  
13.     @ManyToOne(optional=false,cascade = CascadeType.ALL)  
14.     @JoinColumns({  
15.         @JoinColumn(name="CLIENT", referencedColumnName="CLIENT"),  
16.         @JoinColumn(name="KPI", referencedColumnName="KPI")  
17.     })  
18.     private KPI oeeKpi;  
19.  
20.     public KPIText() {  
21.     }  
22.     public String getDescription() {  
23.         return this.description;  
24.     }  
25.     public void setDescription(String description) {  
26.         this.description = description;  
27.     }  
28.  
29.     public KPI getOeeKpi() {  
30.         return this.oeeKpi;  
31.     }  
32.     public void setOeeKpi(KPI oeeKpi) {  
33.         this.oeeKpi = oeeKpi;  
34.     }  
35.     public KPITextPK getId() {  
36.         return id;  
37.     }  
38.     public void setId(KPITextPK id) {  
39.         this.id = id;  
40.     }  
41. } 

================================================================

1. @Embeddable 
2. public class KPIPK implements Serializable {  
3.     //default serial version id, required for serializable classes.  
4.     private static final long serialVersionUID = 1L;  
5.     private String client;  
6.     private String kpi;  
7.  
8.                 public KPIPK() {  
9.                 }  
10.     public String getClient() {  
11.         return this.client;  
12.     }  
13.     public void setClient(String client) {  
14.         this.client = client;  
15.     }  
16.     public String getKpi() {  
17.         return this.kpi;  
18.     }  
19.     public void setKpi(String kpi) {  
20.         this.kpi = kpi;  
21.     }  
22.  
23.     public boolean equals(Object other) {  
24.         if (this == other) {  
25.             return true;  
26.         }  
27.         if (!(other instanceof KPIPK)) {  
28.             return false;  
29.         }  
30.         KPIPK castOther = (KPIPK)other;  
31.         return   
32.             this.client.equals(castOther.client)  
33.             && this.kpi.equals(castOther.kpi);  
34.  
35.     }  
36.       
37.     public int hashCode() {  
38.         final int prime = 31;  
39.         int hash = 17;  
40.         hash = hash * prime + this.client.hashCode();  
41.         hash = hash * prime + this.kpi.hashCode();  
42.           
43.         return hash;  
44.     }  
45. } 

================================================================

1. @Embeddable 
2. public class KPITextPK implements Serializable {  
3.     //default serial version id, required for serializable classes.  
4.     private static final long serialVersionUID = 1L;  
5.     private String client;  
6.     private String kpi;  
7.  
8.     @Column(name="LANG")  
9.     private String language;  
10.  
11.     public KPITextPK() {  
12.     }  
13.     public String getClient() {  
14.         return this.client;  
15.     }  
16.     public void setClient(String client) {  
17.         this.client = client;  
18.     }  
19.     public String getKpi() {  
20.         return this.kpi;  
21.     }  
22.     public void setKpi(String kpi) {  
23.         this.kpi = kpi;  
24.     }  
25.     public String getLanguage() {  
26.         return this.language;  
27.     }  
28.     public void setLanguage(String language) {  
29.         this.language = language;  
30.     }  
31.  
32.     public boolean equals(Object other) {  
33.         if (this == other) {  
34.             return true;  
35.         }  
36.         if (!(other instanceof KPITextPK)) {  
37.             return false;  
38.         }  
39.         KPITextPK castOther = (KPITextPK)other;  
40.         return   
41.             this.client.equals(castOther.client)  
42.             && this.kpi.equals(castOther.kpi)  
43.             && this.language.equals(castOther.language);  
44.  
45.     }  
46.       
47.     public int hashCode() {  
48.         final int prime = 31;  
49.         int hash = 17;  
50.         hash = hash * prime + this.client.hashCode();  
51.         hash = hash * prime + this.kpi.hashCode();  
52.         hash = hash * prime + this.language.hashCode();  
53.           
54.         return hash;  
55.     }  
56. } 

================================================================

Now when I execute the following code:

1.             ArrayList<KPIText> kpiTextList = new ArrayList<KPIText>();  
2.             KPIText kpiText1 = new KPIText();  
3.             KPITextPK textId = new KPITextPK();  
4.             kpiText1.setId(textId);  
5.             kpiText1.getId().setClient("006");  
6.             kpiText1.setDescription("Availability Description English");  
7.             kpiText1.getId().setKpi("Availability");  
8.             kpiText1.getId().setLanguage("EN");  
9.             kpiTextList.add(kpiText1);  
10.                       
11.             KPIText kpiText2 = new KPIText();  
12.             KPITextPK textId2 = new KPITextPK();  
13.             kpiText2.setId(textId2);  
14.             kpiText2.getId().setClient("006");  
15.             kpiText2.setDescription("Availability Description Deutsche");  
16.             kpiText2.getId().setKpi("Availability");  
17.             kpiText2.getId().setLanguage("DE");  
18.             kpiTextList.add(kpiText2);  
19.  
20.             KPI kpiHeader = new KPI();  
21.             KPIPK kpiId = new KPIPK();  
22.             kpiHeader.setId(kpiId);  
23.             kpiHeader.getId().setClient("006");  
24.             kpiHeader.getId().setKpi("Availability");  
25.             kpiHeader.setKpiType("Positive");  
26.             kpiHeader.setUomOfTargets("Percentage");  
27.             kpiHeader.setKPIText(kpiTextList);  
28.  
29.             em.persist(kpiHeader); 

I am getting an error saying:

Caused by: com.sap.sql.log.OpenSQLException: The SQL statement "INSERT INTO "OEE_KPIT" ("CLIENT","KPI","LANG","DESCRIPTION","CLIENT","KPI") VALUES (?,?,?,?,?,?)" contains the semantics error[s]: - 1:61 - column ""CLIENT"" not unique

- 1:70 - column ""KPI"" not unique

Can anyone help me why the "CLIENT" and "KPI" field is added again in the insert statement? Kindly help me in fixing this error.

Regards,

Anil.

[Former Member · Accepted solution]

Assume there is a problem with the join annotations, but am not familiar enough with JPA to pinpoint what.

Since it is a JPA question, suggest that you post it to stackoverflow as well

http://stackoverflow.com/questions/tagged/jpa

(the only SAP specific component is OpenSQL)

Regards

Dagfinn

[former_member280281 · Accepted solution]

Thanks a lot for you inputs Dagfinn. This issue is resolved by adding

"insertable=false, updateable=false" properties.

@ManyToOne(optional=false,cascade = CascadeType.ALL)

@JoinColumns({

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

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

})

Regards,

Anil.