CODEWITHSUNDEEP
javajpa-2.0eclipselinkh2
Marwen Trabelsi
Marwen Trabelsi

Reputation: 4257

Issue With JPA, When I call : em.persist(obj); `Referential integrity constraint violation Exception`

for 3 days, I'm stuck on this problem, I try to explain as clearly as possible: I'm working on a software inventory management, I use EclipseLink(JPA2.0) for managing the Database. the problem is that when I create a new invoice with related Articles , and try to persist them, then i get a Referential integrity constraint violation Exception...

the real problem is that I generated all entities with netbeans(since I'm not familiar with the annotation) and I can not confirm if they are correctly generated or not(but still I doubt)....

tables SQL:

enter image description here

CREATE TABLE fact_proforma (
  idfact_proforma INT UNSIGNED NOT NULL AUTO_INCREMENT,
  utilisateur_login VARCHAR(25) NOT NULL,
  client_idclient INT UNSIGNED NOT NULL,
  date DATE NOT NULL,
  PRIMARY KEY(idfact_proforma),
  FOREIGN KEY(client_idclient)
    REFERENCES client(idclient)
      ON DELETE NO ACTION
      ON UPDATE CASCADE,
  FOREIGN KEY(utilisateur_login)
    REFERENCES utilisateur(login)
      ON DELETE NO ACTION
      ON UPDATE CASCADE
);



CREATE TABLE fact_proforma_has_article (
  fact_proforma_idfact_proforma INT UNSIGNED NOT NULL,
  article_idarticle VARCHAR(40) NOT NULL,
  prix_ht DOUBLE NOT NULL,
  qte DOUBLE  NOT NULL,
  remise DOUBLE NOT NULL,
  marge_benef DOUBLE NOT NULL,
  PRIMARY KEY(fact_proforma_idfact_proforma, article_idarticle),
  FOREIGN KEY(fact_proforma_idfact_proforma)
    REFERENCES fact_proforma(idfact_proforma)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(article_idarticle)
    REFERENCES article(idarticle)
      ON DELETE NO ACTION
      ON UPDATE CASCADE
);


CREATE TABLE article (
  idarticle VARCHAR(40) NOT NULL,
  libel VARCHAR(100) NOT NULL,
  prix_ht DOUBLE NOT NULL,
  tva_idtva DOUBLE NOT NULL,
  qte DOUBLE NOT NULL,
  min_qte DOUBLE  NOT NULL,
  marge_benef DOUBLE NOT NULL,
  remise DOUBLE NOT NULL,
  unite_idunite VARCHAR(10) NOT NULL,
  famille_idfamille VARCHAR(50) NOT NULL,
  etat CHAR(1) NOT NULL,
  PRIMARY KEY(idarticle),
  FOREIGN KEY(tva_idtva)
    REFERENCES tva(idtva)
      ON DELETE NO ACTION
      ON UPDATE CASCADE,
  FOREIGN KEY(famille_idfamille)
    REFERENCES famille(idfamille)
      ON DELETE NO ACTION
      ON UPDATE CASCADE,
  FOREIGN KEY(unite_idunite)
    REFERENCES unite(idunite)
      ON DELETE NO ACTION
      ON UPDATE CASCADE
);



CREATE TABLE utilisateur (
  login VARCHAR(25) NOT NULL,
  pass VARCHAR(15) NOT NULL,
  class CHAR(1) NOT NULL,
  etat CHAR(1) NOT NULL,
  PRIMARY KEY(login)
);

FactProforma.java: // "Facture Proforma" is "proforma invoice"

public class FactProforma implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "IDFACT_PROFORMA", nullable = false)
    private Integer idfactProforma;
    @Basic(optional = false)
    @Column(name = "DATE", nullable = false)
    @Temporal(TemporalType.DATE)
    private Date date;
    @OneToMany(cascade=CascadeType.ALL , mappedBy = "factProforma")
    private List<FactProformaHasArticle> factProformaHasArticleList;
    @JoinColumn(name = "UTILISATEUR_LOGIN", referencedColumnName = "LOGIN", nullable = false)
    @ManyToOne(optional = false)
    private Utilisateur utilisateurLogin;
    @JoinColumn(name = "CLIENT_IDCLIENT", referencedColumnName = "IDCLIENT", nullable = false)
    @ManyToOne(optional = false)
    private Client clientIdclient;

FactProformaHasArticle.java

public class FactProformaHasArticle implements Serializable {
    private static final long serialVersionUID = 1L;
    @EmbeddedId
    protected FactProformaHasArticlePK factProformaHasArticlePK;
    @Basic(optional = false)
    @Column(name = "PRIX_HT", nullable = false)
    private double prixHt;
    @Basic(optional = false)
    @Column(name = "QTE", nullable = false)
    private double qte;
    @Basic(optional = false)
    @Column(name = "REMISE", nullable = false)
    private double remise;
    @Basic(optional = false)
    @Column(name = "MARGE_BENEF", nullable = false)
    private double margeBenef;
    @JoinColumn(name = "FACT_PROFORMA_IDFACT_PROFORMA", referencedColumnName = "IDFACT_PROFORMA", nullable = false, insertable = false, updatable = false)
    @ManyToOne(optional = false)
    private FactProforma factProforma;
    @JoinColumn(name = "ARTICLE_IDARTICLE", referencedColumnName = "IDARTICLE", nullable = false, insertable = false, updatable = false)
    @ManyToOne(optional = false)
    private Article article;

FactProformaHasArticlePK.java

@Embeddable
public class FactProformaHasArticlePK implements Serializable {
    @Basic(optional = false)
    @Column(name = "FACT_PROFORMA_IDFACT_PROFORMA", nullable = false)
    private int factProformaIdfactProforma;
    @Basic(optional = false)
    @Column(name = "ARTICLE_IDARTICLE", nullable = false, length = 40)
    private String articleIdarticle;

my code:

FactProforma factpro=new FactProforma(null, new Date());
 Utilisateur user=new Utilisateur(loginActuel);
 Client client=new Client(Integer.parseInt(codeClient.getText()));

java.util.List<FactProformaHasArticle> ListOfArticles =this.c.GetPanier(dtm,factpro);

factpro.setClientIdclient(client);
factpro.setFactProformaHasArticleList(ListOfArticles);
factpro.setUtilisateurLogin(user);

 EntityManager em= emf.createEntityManager();
    em.getTransaction().begin();

    em.persist(factpro);

    em.getTransaction().commit();

stack trace:

Exception in thread "AWT-EventQueue-0" javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "CONSTRAINT_9A: PUBLIC.FACT_PROFORMA_HAS_ARTICLE FOREIGN KEY(FACT_PROFORMA_IDFACT_PROFORMA) REFERENCES PUBLIC.FACT_PROFORMA(IDFACT_PROFORMA)"; SQL statement:
Internal Exception: org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "CONSTRAINT_9A: PUBLIC.FACT_PROFORMA_HAS_ARTICLE FOREIGN KEY(FACT_PROFORMA_IDFACT_PROFORMA) REFERENCES PUBLIC.FACT_PROFORMA(IDFACT_PROFORMA)"; SQL statement:
INSERT INTO TEST.PUBLIC.FACT_PROFORMA_HAS_ARTICLE (MARGE_BENEF, PRIX_HT, QTE, REMISE, ARTICLE_IDARTICLE, FACT_PROFORMA_IDFACT_PROFORMA) VALUES (?, ?, ?, ?, ?, ?) [23506-164]
INSERT INTO TEST.PUBLIC.FACT_PROFORMA_HAS_ARTICLE (MARGE_BENEF, PRIX_HT, QTE, REMISE, ARTICLE_IDARTICLE, FACT_PROFORMA_IDFACT_PROFORMA) VALUES (?, ?, ?, ?, ?, ?) [23506-164]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.constraint.ConstraintReferential.checkRowOwnTable(ConstraintReferential.java:345)
    at org.h2.constraint.ConstraintReferential.checkRowOwnTable(ConstraintReferential.java:345)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:287)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:287)
    at org.h2.table.Table.fireConstraints(Table.java:862)
    at org.h2.table.Table.fireConstraints(Table.java:862)
    at org.h2.table.Table.fireAfterRow(Table.java:879)
    at org.h2.table.Table.fireAfterRow(Table.java:879)
    at org.h2.command.dml.Insert.insertRows(Insert.java:126)
    at org.h2.command.dml.Insert.update(Insert.java:84)
    at org.h2.command.dml.Insert.insertRows(Insert.java:126)
    at org.h2.command.CommandContainer.update(CommandContainer.java:73)
    at org.h2.command.dml.Insert.update(Insert.java:84)
    at org.h2.command.Command.executeUpdate(Command.java:226)
    at org.h2.command.CommandContainer.update(CommandContainer.java:73)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:325)
    at org.h2.command.Command.executeUpdate(Command.java:226)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:146)
    at java.lang.Thread.run(Thread.java:722 at org.h2.server.TcpServerThread.process(TcpServerThread.java:325)
)

Error Code: 23506
Call: INSERT INTO TEST.PUBLIC.FACT_PROFORMA_HAS_ARTICLE (MARGE_BENEF, PRIX_HT, QTE, REMISE, ARTICLE_IDARTICLE, FACT_PROFORMA_IDFACT_PROFORMA) VALUES (?, ?, ?, ?, ?, ?)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:146)
    bind => [6 parameters bound]
    at java.lang.Thread.run(Thread.java:722)

tomorrow I will try with another DBMS, to avoid conflict...

PS: Im Sorry for french language..I have no choice.

UPDATE: its worked :

EntityManager em= emf.createEntityManager();
    em.getTransaction().begin();

    em.persist(fact);
    em.flush();

    for(FactProformaHasArticle couple: estComposeFacture)
    {
        couple.getFactProformaHasArticlePK().setFactProformaIdfactProforma(fact.getIdfactProforma());
        em.persist(couple);
    }

    em.getTransaction().commit();

Upvotes: 1

Views: 2499

Answers (2)

Chris
Chris

Reputation: 21145

You already have a solution but I thought I'd mention the reason why it works is because you have the primary key field controled through the EmbeddedId's basic mapping - so even though you may have set the relationship before persisting, the pk field will be null until the embeddedId's articleIdarticle is set with a value manually. This is the problem with having the "ARTICLE_IDARTICLE" mapped twice - both mappings should be maintained by the application, and the pk value needs to be available before you persist FactProformaHasArticle.

JPA 2.0 makes this setup a bit easier, as you can use the @MapsId annotation on the relationship to show that it controls the embeddedId's basic mapping - so you only need to set the relationship and have JPA set the field for you. Or you can remove the embeddedId, use the object as a PKclass and mark the relationship with @Id directly as described here:

http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#JPA_2.0 Using this will allow you to remove the flush call, as JPA will set the pk values for you.

Upvotes: 1

JScoobyCed
JScoobyCed

Reputation: 10413

EDITED POST: You need to make sure you have the relationship between FactProformaHasArticle and FactProforma is fully referenced. I suspect you need to have something like this before you can persist:

List<FactProformaHasArticle> ListOfArticles =this.c.GetPanier(dtm,factpro)
for(FactProformaHasArticle fpha: ListOfArticles) {
  fpha.setFactProforma(factpro);
}

(Note: having lowercase for first letter of member variables would be nicer, i.e. listOfArticles instead of ListOfArticles etc)

INITIAL POST (in case it is also valid for your case): Have you tried to remove the optional=false and nullable=false from the annotations of primary keys? I used to have issues with it: JPA wouldn't allow to do MyEntity me=new MyEntity(null); because of that. Try something like:

public class FactProforma implements Serializable {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  // removed:@Basic(optional = false)
  @Column(name = "IDFACT_PROFORMA") // removed: , nullable = false)
  private Integer idfactProforma;
  // rest should be ok
}

Upvotes: 1

Related Questions