Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (nike.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type;
  b=gwf84o0HPiwHJ5vdidMW7gdW+zZWqv3CaIHtiDiQOJb35tTrkLfjhtezGioJVAATk/KLCoyddleUd0FvCTFKL3MXX+vDwFPlqpReh+pfGs9hh/kAqhDrosE1iFTXiv0//FM50B0nfhH2Orl2oPy0Z5H9aU4MR0MtakevUK+7EPk=;
Message-ID: <549783.55837.qm@web55908.mail.re3.yahoo.com>
References: <4AE97A2F.7040109@mail.by>
Date: Thu, 29 Oct 2009 09:38:25 -0700 (PDT)
From: Fay Wang <fyw300@yahoo.com>
Subject: Re: Insert is called instead of Update when merge() with complex IDs
To: users@openjpa.apache.org
In-Reply-To: <4AE97A2F.7040109@mail.by>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

The em.merge will return the merged entity. 

    EntityA mergedEntity = em.merge(newEntity);

During your second merge call, did you call 
   em.merge(mergedEntity)
or 
  em.merge(newEntity)?

Fay


----- Original Message ----
From: Constantine Kulak <code@mail.by>
To: users@openjpa.apache.org
Sent: Thu, October 29, 2009 4:19:11 AM
Subject: Insert is called instead of Update when merge() with complex IDs

Hello!

I need some help in the following situation - completely stuck with it :(
Can you please point me in some direction? Anyway, thanks in advance!
OpenJPA version is 2.0.0-M3.

So, I have three entities:

Prognosis contains many PrognosisEntry, and PrognosisEntry references some Stock. So, it's a many-to-many for Prognosis and Stock, having some additional data at PrognosisEntry join table. Fields:

Stock:
   index (PK)
   length

Prognosis:
   station (PK)
   type (PK)

PrognosisEntry:
   prognosis.station (FK, PK)
   prognosis.type (FK, PK)
   stock.index (FK, PK)
   localState
   timestamp

Relationship between Prognosis and PrognosisEntry is bidirectional (I really need Prognosis.getEntries() method). All IDs are taken from the real world and not generated because this data comes from the backend in XML and then deserialized using JAXB (annotations are removed from the sources below to simplify it). Then I do merge() and expect the data in the DB to be updated with the fresh one. When I call merge() for the 1st time (against the empty DB), all the necessary INSERTs are done. But when called for the 2nd time with the same data (I expect no changes to the DB), the exception is thrown because of the duplicate primary keys. Unexpectedly, it does INSERT instead of UPDATE in this case. The problem is seems to be with the PrognosisEntry-Stock relationship, because when I remove it (replacing reference to Stock in the PrognosisEntry with some dummy ID field) it starts working as expected (calls UPDATE). Exception and logs are given after the
 sources below.

***** Source for Prognosis.java:

@Entity(name = "Prognosis")
@Table(name = "PROGNOSIS")
@IdClass(Prognosis.PrognosisId.class)
@Inheritance(strategy = InheritanceType.JOINED)
public class Prognosis {

   protected List<PrognosisEntry> entries;
   protected String station;
   protected String type;

   @OneToMany(targetEntity = PrognosisEntry.class, cascade = {CascadeType.MERGE}, mappedBy="prognosis", fetch=FetchType.EAGER)
   public List<PrognosisEntry> getEntries() {
       if (entries == null) {
           entries = new ArrayList<PrognosisEntry>();
       }
       return this.entries;
   }

   public void setEntries(List<PrognosisEntry> entries) {
       this.entries = entries;
   }

   @Id
   @Column(name = "STATION")
   public String getStation() {
       return station;
   }

   public void setStation(String value) {
       this.station = value;
   }

   @Id
   @Column(name = "TYPE_")
   public String getType() {
       return type;
   }

   public void setType(String value) {
       this.type = value;
   }

   public boolean equals(Object object) { ... }
   public int hashCode() { ... }

   public static class PrognosisId {
       protected String station;
       protected String type;

       public String getStation() {
           return station;
       }

       public void setStation(String value) {
           this.station = value;
       }

       public String getType() {
           return type;
       }

       public void setType(String value) {
           this.type = value;
       }

         public boolean equals(Object object) { ... }
       public int hashCode() { ... }
   }
}


***** Source for PrognosisEntry.java:

@Entity(name = "PrognosisEntry")
@Table(name = "PROGNOSISENTRY")
@Inheritance(strategy = InheritanceType.JOINED)
@IdClass(PrognosisEntry.PrognosisEntryId.class)
public class PrognosisEntry {

   protected String timestamp;
   protected String localState;
   protected Prognosis prognosis;

   protected Stock stock;

   @Id
   @ManyToOne(targetEntity = Stock.class, cascade = { CascadeType.MERGE }, fetch = FetchType.EAGER)
   public Stock getStock() {
       return stock;
   }

   public void setStock(Stock stock) {
       this.stock = stock;
   }

   @Id
   @ManyToOne(targetEntity = Prognosis.class, cascade = { CascadeType.MERGE }, fetch = FetchType.EAGER)
   public Prognosis getPrognosis() {
       return prognosis;
   }

   public void setPrognosis(Prognosis prognosis) {
       this.prognosis = prognosis;
   }

   @Column(name = "TIMESTAMP_", length = 255)
   public String getTimestamp() {
       return timestamp;
   }

   public void setTimestamp(String value) {
       this.timestamp = value;
   }

   @Basic
   @Column(name = "LOCALSTATE", length = 255)
   public String getLocalState() {
       return localState;
   }

   public void setLocalState(String value) {
       this.localState = value;
   }

   public boolean equals(Object object) { ... }
   public int hashCode() { ... }

   public static class PrognosisEntryId {

       protected Prognosis.PrognosisId prognosis;
       protected String stock;

       public String getStock() {
           return stock;
       }

       public void setStock(String stock) {
           this.stock = stock;
       }

       public Prognosis.PrognosisId getPrognosis() {
           return prognosis;
       }

       public void setPrognosis(Prognosis.PrognosisId prognosis) {
           this.prognosis = prognosis;
       }

       public boolean equals(Object object) { ... }
       public int hashCode() { ... }
   }
}


***** Source for Stock.java:

@Entity(name = "Stock")
@Table(name = "STOCK")
@Inheritance(strategy = InheritanceType.JOINED)
public class Stock {
   protected String index;
   protected String length;

   @Id
   @Column(name = "INDEX_")
   public String getIndex() {
       return index;
   }

   public void setIndex(String value) {
       this.index = value;
   }
     @Basic
   @Column(name = "LENGTH_", length = 255)
   public String getLength() {
       return length;
   }

   public void setLength(String value) {
       this.length = value;
   }

   public boolean equals(Object object) { ... }
   public int hashCode() { ... }
}


***** Generated DDL:

CREATE TABLE PROGNOSIS (STATION VARCHAR(254) NOT NULL, TYPE_ VARCHAR(254) NOT NULL, PRIMARY KEY (STATION, TYPE_));
CREATE TABLE PROGNOSISENTRY (PROGNOSIS_STATION VARCHAR(254) NOT NULL, PROGNOSIS_TYPE_ VARCHAR(254) NOT NULL, STOCK_INDEX_ VARCHAR(254) NOT NULL, LOCALSTATE VARCHAR(254), TIMESTAMP_ VARCHAR(254), PRIMARY KEY (PROGNOSIS_STATION, PROGNOSIS_TYPE_, STOCK_INDEX_));
CREATE TABLE STOCK (INDEX_ VARCHAR(254) NOT NULL, LENGTH_ VARCHAR(254), WEIGHT VARCHAR(254), PRIMARY KEY (INDEX_));
ALTER TABLE PROGNOSISENTRY ADD FOREIGN KEY (PROGNOSIS_STATION, PROGNOSIS_TYPE_) REFERENCES PROGNOSIS (STATION, TYPE_);
ALTER TABLE PROGNOSISENTRY ADD FOREIGN KEY (STOCK_INDEX_) REFERENCES STOCK (INDEX_);


***** Executed SQL and exception:

3063  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 1004551136> executing prepstmnt 379983526 SELECT t0.STATION, t0.TYPE_ FROM PROGNOSIS t0 WHERE t0.STATION = ? AND t0.TYPE_ = ?  [params=(String) 1400, (String) IN]
3063  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 1004551136> [0 ms] spent
3078  TRACE  [main] openjpa.jdbc.JDBC - <t 1183336072, conn 1004551136> [0 ms] close
3125  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 1901752666> executing prepstmnt 1663984430 SELECT t0.PROGNOSIS_STATION, t0.PROGNOSIS_TYPE_, t0.STOCK_INDEX_, t0.LOCALSTATE, t1.INDEX_, t1.LENGTH_, t1.WEIGHT, t0.TIMESTAMP_ FROM PROGNOSISENTRY t0 LEFT OUTER JOIN STOCK t1 ON t0.STOCK_INDEX_ = t1.INDEX_ WHERE t0.PROGNOSIS_STATION = ? AND t0.PROGNOSIS_TYPE_ = ?  [params=(String) 1400, (String) IN]
3156  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 1901752666> [31 ms] spent
3156  TRACE  [main] openjpa.jdbc.JDBC - <t 1183336072, conn 1901752666> [0 ms] close
3203  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 1242843668> executing prepstmnt 140118106 SELECT t0.LOCALSTATE, t1.STATION, t1.TYPE_, t2.INDEX_, t2.LENGTH_, t2.WEIGHT, t0.TIMESTAMP_ FROM PROGNOSISENTRY t0 LEFT OUTER JOIN PROGNOSIS t1 ON t0.PROGNOSIS_STATION = t1.STATION AND t0.PROGNOSIS_TYPE_ = t1.TYPE_ LEFT OUTER JOIN STOCK t2 ON t0.STOCK_INDEX_ = t2.INDEX_ WHERE t0.PROGNOSIS_STATION = ? AND t0.PROGNOSIS_TYPE_ = ? AND t0.STOCK_INDEX_ IS NULL  optimize for 1 row [params=(String) 1400, (String) IN]
3203  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 1242843668> [0 ms] spent
3219  WARN   [main] openjpa.Runtime - Finder for "PrognosisEntry" is not cachable.
3219  TRACE  [main] openjpa.Runtime - finder-invalidate
3219  TRACE  [main] openjpa.jdbc.JDBC - <t 1183336072, conn 1242843668> [0 ms] close
3281  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 391649112> executing prepstmnt 317330154 SELECT t0.LENGTH_, t0.WEIGHT FROM STOCK t0 WHERE t0.INDEX_ = ?  [params=(String) 3]
3281  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 391649112> [0 ms] spent
3281  TRACE  [main] openjpa.jdbc.JDBC - <t 1183336072, conn 391649112> [0 ms] close
3344  TRACE  [main] openjpa.jdbc.JDBC - The batch limit is set to 100.
3344  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 1494898970> executing prepstmnt 686827760 INSERT INTO PROGNOSISENTRY (PROGNOSIS_STATION, PROGNOSIS_TYPE_, STOCK_INDEX_, LOCALSTATE, TIMESTAMP_) VALUES (?, ?, ?, ?, ?) [params=(String) 1400, (String) IN, (String) 3, (null) null, (String) 2009-12-09-17.34]
3547  TRACE  [main] openjpa.jdbc.SQL - <t 1183336072, conn 1494898970> [171 ms] spent

<openjpa-2.0.0-M3-r422266:822833 fatal store error> org.apache.openjpa.util.StoreException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
   at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2249)
   at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2096)
   at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:1994)
   at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1912)
   at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
   at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1436)
   at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:895)
   at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:557)
   at test.persistParent(test.java:34)
   at test.main(test.java:16)
Caused by: <openjpa-2.0.0-M3-r422266:822833 fatal store error> org.apache.openjpa.util.ObjectExistsException: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "PROGNOSISENTRY" from having duplicate values for the index key.
FailedObject: prepstmnt 686827760 INSERT INTO PROGNOSISENTRY (PROGNOSIS_STATION, PROGNOSIS_TYPE_, STOCK_INDEX_, LOCALSTATE, TIMESTAMP_) VALUES (?, ?, ?, ?, ?) [org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement_]
   at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4575)
   at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4543)
   at org.apache.openjpa.jdbc.sql.DB2Dictionary.newStoreException(DB2Dictionary.java:541)
...

--
Constantine Kulak
wintermuteblog.blogspot.com