Hibernate version: 2.1.4

A question related to updating a collection:

According to the manual, this is the order of how SQL is executed:



My setting (the following are pseudocode):
I have a persister Object A that have a collection c

In my code I do the following in order:

1. A.c.clear()
2. A.c.add(object)
3. update(A)

That is, I clear the collection and then adds an object to the collection. I have set the necessary cascading attributes on A.c (I've tried both "all-delete-orpans" and "all").

The order of SQL execution that I intuitively expected was that the DELETE statements to be executed first, then the INSERTs. Instead, as the aforementioned specifies, the INSERTs are being executed _before_ the DELETEs.

The problem with this is that I'm trying to insert objects that already existed before the DELETE, and when the INSERT is executed before the DELETE, this causes a PK constraint violation.

A second point, the database triggers some functions when an INSERT is done, so I really want to insert elements instead of updating these.

I have also tested dereferencing the collection, initialising a new collection and setting this, as described in chapter 14.1.4. However, this doesn't work with "cascade=all-delete-orphan", and the elements in the collection thus does not get deleted, causing a constraint violation when I try to insert elements with the same PK that already resides in the "old" collection.

I guess my question boils down to:

Is there a way to alter the order of how the SQLs are executed (in the same session)?

As of now, my options, as far as i can see, are:

- Forcing the DELETEs and INSERT in different sessions.
- Redesign the database triggers, and check the collection for object existence before updating the collection. (Not preferrable)

Thanks for any comments, views, etc...


Regards, Jen

You can call flush() explicitly after delete(). But usually wanting to delete then reinsert is a sign that you are trying to use Hibernate in the wrong way.

I have a problem where the order of inserts/deletes on a collection are throwing UNIQUE KEY VIOLATONS. This is the SAME problem as posted earlier. I don't understand the original reply stating usage of hibernate the "wrong way". What does that mean? An example of correct/incorrect usage of lists would be helpful.

I have a parent child relationship where I am adding/removing children in a list. When I update the parent it attempts to FIRST insert ALL CHILDREN then DELETE all the de-referenced children. If I drop the unique key constraint it works, but I WANT the unique key constraint.

My problem is with the order Hibernate issues the sql. It doesn't issue them in the order that it should. It should delete anything I took out first and then insert anything new. It does the inserts first however.

Can someone help me?

I have exactly the same problem. Because it does updates before deletes, it breaks a unique key constraint.

I also tried re-creating the collection, per the reference guide 14.1.4, "One-shot Delete"



...where the referred-to behavior is:



But that does not appear to work. I expected a single DELETE followed by some INSERTs, but I got the same as before -- UPDATES, followed by a DELETE for the row I deleted.

I'm using 2.1.7.

Hi everybody

I'm facing a very similar problem:

I have a list mapped as a <bag> and I use to add and remove several beans from it. Then when I save the bean with the nested collection and flush the session, Hibernate performs all the inserts needed to the database table before the deletes.

The problem is that I have some unique keys in the table that are violated because the inserted objects have the same values for the uk fields as the objects that will be deleted latter.

The solutions described in this thread suggest to manually delete the collection elements, flush the session and then perform the inserts, but I don't know which objects should be deleted because they are inside some sort of deletion log within the bag implementation.

Is there a way to make Hibernate perform such deletions before the insertions?

Thanks in advance.

I am running into this same problem, where I'm trying to reorder the elements in a hibernate-managed list. But due to unique constraint violations, this is failing. One issue I have is that the size of my list is unchanged; only the order is different. Since Hibernate doesn't know about this unique constraint (and I don't know how to inform Hibernate of its existence), it would seem reasonable for Hibernate to not issue any DELETEs in this scenario, and just UPDATE the current items with their new order.

In order to workaround this, I tried the delete-flush-insert approach, which works, but is messy. I settled on the "one-shot-delete" approach (unlike gshannon, i found this approach to work as expected), where I instantiate a new List as a copy of the previous one, reorder in the new list, and reassign back to the parent. This isn't as efficient (as I end up with n+1 DB calls in all cases, rather than 2 in the best case), but we're expecting very small numbers of items (no more than 10) in these lists, so I'm deeming this 'workaround' sufficient.

If someone knows of a better way to reorder items in a list with Hibernate, without forcing a flush in the middle and without creating a brand new list, I'd love to hear it.

As a sidenote, I lurk in these forums a lot, and I always find it amusing when Gavin et al declare someone as using Hibernate in the "wrong way," with no indicator of why it's wrong or how to find out what the "right way" is. I know these guys are really busy, and there's copious amounts of documentation in this site. But hibernate is a complex beast, with lots of subleties and trapfalls. Declaring seemingly legitimate usage as being the "wrong way", with no further explanation, is less than helpful. It makes me wonder why they even bother to take the time to post if that's the kind of answer they're going to give.

What would be your solution? I don't think quoting 50 pages of documentation is going to help the user if he uses "Hibernate the wrong way". But believe me, its the only thing that helps someone who didn't read the 50 pages.

Gavin, since you took the time to reply to the previous post on this topic, I wonder if you could also comment on the question at hand. Is it unreasonable to expect Hibernate to perform deletions first, then inserts? Is there some reason it should not? Or is this a legitimate issue?

I've got 3 or 4 unique indexes I have disabled because of this. Thanks.

Hi, Christian! Thanks for adding your thoughts to the thread!

Do you have any insight into the problem? Is there a way to get Hibernate to adjust a list or collection by performing the DELETEs first, then the INSERTs, so as to avoid uniqueness constraint violations? Or is this, as gshannon asks, unreasonable? If so, why? Is this what is meant by the "wrong way"? Is there a "right way" to approach this problem?

Many thanks for your continued service to the Hibernate community.

I seem to have the same or very similar problem but it's not in a collection.

I issue some deletes then attempt to save a new object and get a UNIQUE Constraint violation because the deletes hadn't occured yet.

I was able to work around this by issuing a flush after the deletes.

But what I really would like is to understand why Hibernate is doing what it's doing? And if I'm doing things the "wrong way" then why is it wrong and what would be the correct way? I'm more then willing to read the "50 pages of docs" if someone would point out which 50 pages explain the issue.

I understand you get lots of questions from people who haven't read the documentation, I know it's frustrating, I deal with these sort of people at work all the time. But there are many of us who have read the docs and are willing to make the effort but still have problems and are trying to understand.

So back to the question...
It seems to me that the transparent write-behind logic would need to be aware of the unique contraint on the table and thus do the deletes ahead of the inserts. Am I on the right track or am I missing the point? Or is this just a limitation I need to be aware of?

Hi

There's also another situation directly related with this issue which is affecting me and probably others:

When you have a collection ordered by an attribute edited by the user and constrained by a Unique Key constraint in the database and you add a new bean to the beginning of the collection and increment such attribute in the other elements (to avoid a duplicated unique key), the INSERT is performed before the updates of the other elements causing unique key violations.


I would like to ask the very same questions to the Hibernate Team:

* What is the right way to keep a collection in order when the attribute that gives such order is edited by the user?
* What is the good reason to Hibernate perform all the INSERTs first?

[quote="gavin"]You can call flush() explicitly after delete(). But usually wanting to delete then reinsert is a sign that you are trying to use Hibernate in the wrong way.[/quote]
So if I'm using Hibernate in wrong way (I don't think so) is there any way how to implement my own SessionImpl, where I reorder the delete, insert and updates and use it? This will solve my problem.
Thanks

Can we please let this thread die, it has several different users posting on about 3 completely different issues. The first user is actually plain wrong (no, Hibernate never issues the deletes before the inserts).

The problem with UK violations is because the users have defined a different PK to the one Hibernate expects: (fk, indexCol).

I'm sorry to put the life back in this thread but I want to point out a legitimate reason why someone might have a defined a different PK instead of [FK + index column].

I my situation, the entities in my List have their own List of entities. In other words, the relationship is A->B->C (A has many B, B has many C). Rather than defining B's PK as [FK referencing A + B's List index column] I defined a surrogate PK on B.

Why did I use a surrogate key for B? Using a surrogate key for B means that B's foreign key in C is just one column instead of two. I prefer that to using the two part composite key. Indeed, page 91 of Hibernate in Action recommends using surrogate keys for entities. Furthermore, page 92 says that composite keys can be more difficult to work with.

I can definitely attest to composite keys being a pain in the butt. I had a situation once where I had A->B->C->D->E and I initially tried to NOT use composite keys but the problem was that the number of columns in the key kept growing the further down the graph you go! One problem with this is that it makes queries longer and harder to write.

Anyway, for the above reasons, I used a surrogate key in B. However, I still wanted to ensure that I wouldn't have a situation where I had duplicate list index values for the same A FK in B. For example (B_ID is PK of B, A_ID is FK referencing A, LIST_INDEX is the index column for the List mapping):

B_ID, A_ID, LIST_INDEX
1, 1, 0
2, 1, 1
3, 1, 2
4, 1, 2 (2 is a duplicate index value!)

Normally, this wouldn't occur if all users used our Hibernate-powered application to populate the database. However, what if someone used another application or a SQL script and accidentally duplicated the list index values? If I am reading PersistentList.readFrom() correctly, I think record B3 will be overwritten by record B4 (or vice-versa depending on the order of the records in the result set) when the List is rehydrated.

To prevent that, I added a unique constraint to B on A_ID + LIST_INDEX. But, as pointed out in the previous messages in this thread, this causes unique constraint violations when you remove an item from anywhere but the last position in the list because the updates (to update the list index value of the repositioned list elements) seem to be submitted before the deletes (I verified this by setting show_sql to true).

For now, my workaround is to remove the unique constraint. I prefer that to going back to using composite keys.

Perhaps Hibernate can deal with the situation in a future version?

This problem has cost me several days of my life, and I don't think I'm using Hibernate "the wrong way" either.

Consider this, if you will:

@Entity
@org.hibernate.annotations.Entity(dynamicInsert=true, dynamicUpdate=true)
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(discriminatorType=DiscriminatorType.INTEGER, name="ChildType")

public abstract class Child {

@Id @GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="ChildId")
protected Long id ;

@ManyToOne
@JoinColumn(name="ParentId", referencedColumnName="ParentId", nullable=false)
protected Parent parent ;

...

}

[ Derived Entities Omitted ]


@Entity
@org.hibernate.annotations.Entity(dynamicInsert=true, dynamicUpdate=true)
public class Parent {

@Id @GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="ParentId")
protected Long id ;

@OneToMany(cascade={CascadeType.ALL}, mappedBy="parent")
@org.hibernate.annotations.Cascade({
org.hibernate.annotations.CascadeType.DELETE_ORPHAN
})
protected Set<Child> children = new HashSet<Child>() ;

public void addChild(Child child) {
child.setParent(this) ;
children.add(child) ;
}

public void removeChild(Child child) {
child.setParent(null) ;
children.remove(child) ;
}

// ...
}


Where there is a db uniqueness constraint on the natural composite key: Child.(ParentId, ChildType).

Then, I do:

parent.remove(someChild) ;
parent.add(newChild) ;

where 'someChild' and 'newChild' are both of the same type.

Then I will get Constraint Violation, since Hibernate does the Insert before the Delete. This would appear to render delete-orphan about as much use as a chocolate teapot.

Further, if I try to solve the problem by dropping the surrogate key on Child, and use the natural composite (ParentId, ChildType), then I have a new problem (duplicate column), since ChildType is also the Discriminator column. AFAIK, it is not possible to map the DiscriminatorColumn with insertable=false, since this is not available via the JPA annotations, and there is no Hibernate extension annotation to achieve this.

With some Hibernate hacking, I am able to force insertable=false on the Discriminator column by modifying the hibernate Configuration that is generated from the annotations. However, I then just run into new problems; I either get a NonUniqueObjectException, (if I do not null the parentId in the child on remove), or a Property Getter exception if I do. Either way, it doesn't work. I've tried using @Embedded with @Id, @EmbeddedId, and @IdClass implementations of the composite PK, and nothing seems to work.

BTW, in all cases, my equals() and hashCode() methods are implemented on the natural keys.

It's all very frustrating. It would be great if Hibernate would just do the operations in an order that won't trip the constraint (delete first), or at least provide the author with some mechanism to force this ordering.