Hi Kevin, > Q1: Where is the duplicate key exception coming from? Which > of the SQL statements is producing this exception? Just after the set of SQL statements below (generated by commit()) the exception is thrown On the first Axle update.
> Q2: The reason for the question is that Delete seems to be > processing on uniqueid of 413966, but I don't see any Updates > being processed on that key... Axle 413966 is not updated (see below) > Q3: So, are you expecting Axle with uniqueid of 413966 to be > deleted before the updates? Or, not at all? Or, is this > uniqueid key in error and you are expecting some other Axle > to be deleted? Here is the Scenario: Start with Equipment #413908 Axle 1 #413966 Axle 2 #498801 Axle 3 #413965 Axle 4 #504805 User removes Axle 1 from the Collection (on screen) What "should" happen: Since the reference bewteen Axle 1 and equipment is set to null - Axel 1(413966) should be Removed from the Database. Axle 2 (498801) should become Axle 1 Axle 3 (431965) should become Axle 2 Axle 4 (504805) should become Axle 3 IOW - I think the delete should be done before the updates - and I assumed that JPA would Organise the SQLs based on the Unique Key Constraint accordingly, or at least that is what I thought this passage meant: 13.2. Unique Constraints ... "The unique constraints you define are used during table creation to generate the proper database constraints, and may also be used at runtime to order INSERT, UPDATE , and DELETE statements. For example, suppose there is a unique constraint on the columns of field F. In the same transaction, you remove an object A and persist a new object B, both with the same F value. The JPA runtime must ensure that the SQL deleting A is sent to the database before the SQL inserting B to avoid a unique constraint violation." Most likely, I am just missing some link somewhere as I can't imagine that this is a bug that went unnoticed .... Thanks a lot for taking the time to look into this! John > -----Ursprüngliche Nachricht----- > Von: Kevin Sutter [mailto:kwsut...@gmail.com] > Gesendet: Montag, 11. Juni 2012 22:04 > An: users@openjpa.apache.org > Betreff: Re: Order of SQL Statements > > Hi John, > Q1: Where is the duplicate key exception coming from? Which > of the SQL statements is producing this exception? > > Q2: The reason for the question is that Delete seems to be > processing on uniqueid of 413966, but I don't see any Updates > being processed on that key... > > Q3: So, are you expecting Axle with uniqueid of 413966 to be > deleted before the updates? Or, not at all? Or, is this > uniqueid key in error and you are expecting some other Axle > to be deleted? > > Just trying to understand the scenario and expected results. Thanks. > > Kevin > > > On Mon, Jun 11, 2012 at 10:29 AM, Boblitz John > <john.bobl...@bertschi.com>wrote: > > > Hello, > > > > after correcting the issues arrising from the InverseManager, and > > adding annotations to remove unlinked Entities > (@ElementDependent) I > > get an error due to duplicate key. > > > > The goal is: > > > > I have up to 5 Axles and the User can delete any one of > them and the > > others should move up (Axle 3 becomes Axle 2 ...) When I > delete the > > first Axel, I get an exception. > > > > I get all the axles back from the GUI and proceed to move them from > > the Data Object to my Entity: > > > > TreeSet<AxleDTO> axleDtos = new TreeSet<AxleDTO>(new > > Comparator<AxleDTO>() { > > > > @Override > > public int compare(AxleDTO pO1, AxleDTO pO2) { if > (pO1.getAxleNumber() > > < pO2.getAxleNumber()) { return -1; } else if > (pO1.getAxleNumber() == > > pO2.getAxleNumber()) { return 0; } else { return 1; } } }); > > > > axleDtos.addAll(pData.getAxles()); > > if (axleDtos != null && !axleDtos.isEmpty()) { short i = 1; > Set<Axle> > > axles = new HashSet<Axle>(); AxleMgr axleMgr = new > AxleMgr(mPm); for > > (AxleDTO axleDto : axleDtos) { Axle axle = new Axle(); > > axleMgr.toEntity(axleDto, axle); if (!axle.isActive()) { > > axle.setEquipmentId(null); } else { axle.setAxleNumber(i++); > > axle.setEquipmentId(pEntity); axles.add(axle); } } > > pEntity.setAxles(axles); } > > > > > > @Entity > > @Table(schema = "galaxy11", name = "Equipment") public > class Equipment > > extends BaseEntity { > > > > @JsonManagedReference > > @OneToMany(cascade = CascadeType.ALL, mappedBy = > "equipmentId", fetch > > = > > FetchType.EAGER) > > @ElementDependent > > private Set<Axle> axles = new HashSet<Axle>(); > > > > @Entity > > @Table(schema = "galaxy11", name = "Axle", uniqueConstraints= > > @UniqueConstraint(name="uq_Axle_equipmentId_axleNumber", > columnNames = > > { "equipmentId", "axleNumber" }) > > ) > > public class Axle extends BaseEntity { > > > > @Basic > > @Column(columnDefinition = "int2") > > private short axleNumber; > > > > @JsonBackReference > > @ManyToOne(fetch = FetchType.EAGER) > > @JoinColumn(name = "equipmentId", columnDefinition = > "int8", nullable > > = > > false) > > private Equipment equipmentId; > > > > > > > > It all seems to work as I expect, except for the order of > the statements! > > The trace shows that the delete of the Axle is done AFTER > the updates: > > > > > > > > UPDATE galaxy11.Equipment > > SET modified = ?, versionid = ?, length = ?, height = ?, > wheelbase = > > ?, tareweight = ?, grossweight = ? > > WHERE uniqueid = ? AND versionid = ? > > [params=(Timestamp) 2012-06-11 13:47:18.21, (int) 33, (BigDecimal) > > 6.2, > > (BigDecimal) 3.1, (BigDecimal) 3.8, (BigDecimal) 6.7, > (BigDecimal) 18, > > (long) 413908, (int) 32] > > - <t 656806424, conn 972792495> [27 ms] spent > > > > - <t 656806424, conn 972792495> executing prepstmnt > 684003742 UPDATE > > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, > > axlenumber = ? > > WHERE uniqueid = ? AND versionid = ? > > [params=(Timestamp) 2012-06-11 13:47:18.23, (int) 15, (long) 413908, > > (short) 2, (long) 413965, (int) 14] > > - <t 656806424, conn 972792495> [23 ms] spent > > > > - <t 656806424, conn 972792495> executing prepstmnt 10578812 UPDATE > > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, > > axlenumber = ? > > WHERE uniqueid = ? AND versionid = ? > > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 2, (long) 413908, > > (short) 3, (long) 504805, (int) 1] > > - <t 656806424, conn 972792495> [21 ms] spent > > > > - <t 656806424, conn 972792495> executing prepstmnt > 1704434230 UPDATE > > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, > > axlenumber = ? > > WHERE uniqueid = ? AND versionid = ? > > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 13, (long) 413908, > > (short) 1, (long) 498801, (int) 12] > > - <t 656806424, conn 972792495> [22 ms] spent > > > > - <t 656806424, conn 972792495> executing prepstmnt > 1141736277 UPDATE > > galaxy11.Motor SET modified = ?, versionid = ? > > WHERE uniqueid = ? AND versionid = ? > > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 28, > (long) 414058, > > (int) 27] > > - <t 656806424, conn 972792495> [21 ms] spent > > > > - <t 656806424, conn 972792495> executing prepstmnt > 1395221594 DELETE > > FROM galaxy11.Axle WHERE uniqueid = ? AND versionid = ? > > [params=(long) 413966, (int) 14] > > > > > > I am certain I am missing something quite minor, but after running > > interactive debugs between The GUI and the persistence for > the last 48 > > hrs, I think I am missing the obvious. > > > > Anyone out there see where I am going astray? > > > > > > Thanks, > > > > John > > > > > > >
