Delete from uni-directional List

andrej · **Joined:** Fri Aug 31, 2007 12:29 am **Posts:** 8

We have spent two days reading documentation, consulting experts, searching the web, etc. on a Hibernate issue that seems very, very obvious, to no avial. Any help will be appreciated.

We are using Hibernate (3.2.1.ga) with annotations (also 3.2.1.ga). We have two persistent classes, called "Survey" and "Page". Each survey consists of a list of pages, and each page consists of a list of questions (but only the fact that Surveys contain lists of Questions is relevant here). So we write:

Code:

@Entity
public class Page
{
    @Id @GeneratedValue
    Long id;

    private String title;
    
    @OneToMany(cascade = {CascadeType.ALL})
    @IndexColumn(name = "position")
    private List<Question> questions;     
...
}

Code:

@Entity
public class Survey
{
    @Id @GeneratedValue
    Long id;

    private String title;

    @OneToMany(cascade = {CascadeType.ALL})
    @IndexColumn(name = "position")
    private List<Page> pages;
   
    ...
}

This seems like a pretty basic example of a uni-directional @OneToMany. Now the trouble is that, as far as we can tell, delete from a persistent List does not work because it generates a uniqueness constraint violation. If we delete the last element of the pages list, everything is ok (a single delete sql statement is issued). But if we delete any other element, then sometimes we get:

Quote:

Hibernate: select survey0_.id as id4_0_, survey0_.created as created4_0_, survey0_.title as title4_0_ from Survey survey0_ where survey0_.id=?
Hibernate: select pages0_.Survey_id as Survey1_1_, pages0_.pages_id as pages2_1_, pages0_.position as position1_, page1_.id as id1_0_, page1_.title as title1_0_, page1_.introText as introText1_0_ from Survey_Page pages0_ left outer join Page page1_ on pages0_.pages_id=page1_.id where pages0_.Survey_id=?
Hibernate: delete from Survey_Page where Survey_id=? and position=?
Hibernate: update Survey_Page set pages_id=? where Survey_id=? and position=?

with things like

Quote:

[WARN] SQL Error: 0, SQLState: null
[WARN] Batch entry 0 update Survey_Page set pages_id=52 where Survey_id=41 and position=0 was aborted. Call getNextException to see the cause.
[WARN] SQL Error: 0, SQLState: 23505
[WARN] ERROR: duplicate key violates unique constraint "survey_page_pages_id_key"

So what is going on here? Since this is a very basic example in which we let Hibernate use whatever defaults it wants, it seems odd that this should not work. I can provide more info, if you think that will help, but I am afraid we are hitting against something fairly obvious.

By the way, list insertion and addition works. It looks like whether deletion works depends on how the order of deletions relates to the order of insertions. We are removing elements with the List.remove method. We have not defined equal and hashCode for Page. Note that DELETE_ORPHAN is irrelevant (it does not help), as it only causes the deleted Page to be remove from page table, it has nothing to do with the survey_page table, which is where the problem lies.

Thank you,

Andrej

hutorny · **Joined:** Mon Aug 27, 2007 8:10 am **Posts:** 37

andrej wrote:

We have spent two days reading documentation, consulting experts, searching the web, etc. on a Hibernate issue that seems very, very obvious, to no avial. Any help will be appreciated.

We had similar issue, so we looked at hibernate sources to understand what is going on.

There are to collection persisters - Basic and OneToMany. The latter one always generates update unless you provide cusom SQL

@org.hibernate.annotations.SQLDelete

alesj · **Posted:** Fri Aug 31, 2007 8:50 am

An actual simple example from Andrej:

Code:

// Please set up hibernate.cfg.xml to use a database of your choice.
// This example dies on a Postgres database. You will also need to include
// a standard batch of jars to make hibernate happy.

public class Main
{
    public static void main(String[] args) {
        // First we create some useful data.
        Session session1 = HibernateUtil.getSession();
        Child ann = new Child("Ann");
        Child bob = new Child("Bob");
        Child chris = new Child("Chris");
        Parent john = new Parent();
        john.getChildren().add(ann);
        john.getChildren().add(bob);
        john.getChildren().add(chris);
        session1.persist(john);
        Long id = john.id;
        HibernateUtil.commitSession();

        Session session2 = HibernateUtil.getSession();
        Parent james = (Parent)session2.load(Parent.class, id);
        // This causes a constraint violation
        james.getChildren().remove(0);
        // This would not cause a constraint violation
        // james.getChildren().remove(1);
        HibernateUtil.commitSession();
    }
}

@Entity
public class Child
{
    @Id @GeneratedValue
    public Long id;
    
    public String name;

    public Child() { }
    
    public Child(String name) {
        this.name = name;
    }
}

@Entity
public class Parent
{
    @Id @GeneratedValue
    public Long id;
    
    @OneToMany(cascade = {CascadeType.ALL})
    @IndexColumn(name = "position")
    private List<Child> children = new ArrayList<Child>();

    public Parent() { }

    public void setChildren(List<Child> children)
    {
        this.children = children;
    }

    public List<Child> getChildren()
    {
        return children;
    }
}

alesj · **Posted:** Sat Sep 01, 2007 4:07 am

Andrej discovered that having your own linked list does the job:

Code:

Element firstElement; // null if list empty
@OneToMany Map<Element,Element> nextElementMap;

andrej · **Joined:** Fri Aug 31, 2007 12:29 am **Posts:** 8

alesj wrote:

Andrej discovered that having your own linked list does the job:

Code:

Element firstElement; // null if list empty
@OneToMany Map<Element,Element> nextElementMap;

This is hardly descriptive enough to be of any help, I hope people will be able to decipher alesj's message. If one wants to have a true linked list (i.e., the two basic operations are "first element" and "next") then the suggestion above kind of works, but there are a couple of problems I want to warn you about:

even though it makes sense to use @OneToMany, Hibernate seems unable to perform updates in the correct order (typically if several deletions and changes to the Map are performed) and hits against uniqueness constraints that it generates. The only fix I know is to use @ManyToMany instead. This of course is bad, as an element of a list cannot have more than one successor.
It seems difficult to get DELETE_ORPHAN behavior because nextElementMap interacts with firstElement, i.e., Hibernate will attempt to delete an entity when it gets unlinked from the map, even though it might still be pointed to by firstElement. One possible fix would be to have a special "before-first-element" that points to the first element. Then we can just keep the nextElementMap and get rid of firstElement. I did not try this out.

All in all, I think Hibernate has problems with generating correct updates that avoid uniqueness constraints (generated by Hibernate itself) on (indexed) List and @OneToMany Maps. Please correct me if I am wrong.

steve · **Posted:** Wed Aug 11, 2010 12:29 pm

Couple of things:
1) You are using a version of Hibernate that is almost 4 years old! http://sourceforge.net/projects/hiberna ... /3.2.1.ga/ If you want help from the community your best bet is to first update to a "community supported" version, currently 3.5.x or 3.6 (still beta).
2) You might want to consider adding a @JoinColumn annotation to your list definition. Why? Notice how even though you specify @OneToMany we are really creating a many-to-many structure in the database? Thats because it is a JPA requirement that a unidirectional collection marked as @OneToMany be interpreted as such. Yes even though they seem to blatantly miss the semantic of the term "one to many". We are constrained by that. However, if you give an @JoinColumn here we are free to interpret that as a real one-to-many with the POSITION column on the PAGE table and doing away with the SURVEY_PAGE table. That will stop the updates you see against the SURVEY_PAGE. To be honest I am not sure why you see a constraint violation there. I would need to see more data. But see (1) above first.

andrej · **Joined:** Fri Aug 31, 2007 12:29 am **Posts:** 8

Dear Steve, I was not using an old version of Hibernate since I posted my question three years ago (check the post date)! I have no idea why you answered it now, I suspect it has something to do with alesj. Anyhow, in the meantime we abandoned trying to use Hibernate + GWT and are very happy with Django. As my honorable partner likes to say, we're just too dumb to use Hibernate. Thanks for answering though.

Actually, I can tell you what was really wrong with Hibernate. Everyone was super-friendly and supportive, that was very cool and that was not a problem at all. We could have even accepted the fact that Hibernate was not bug-free. But we really didn't like losing a lot of time just because all the documentation, demos, and people we spoke to kept saying "it just works, it's great, it's the best", and when we encountered a basic problem and posed a very specific and well defined question, we couldn't get a straight answer.

Django is much more honest: they tell you right from the beginning that its ORM is very simple and it can't do much. So we knew from the start we would have to implement our own persistent collections. That's a better choice than spending a week trying to figure out whether Hibernate is supposed to support some feature just because documentation is dishonest (read that as you wish).

Even now, on this forum nobody has told me whether Lists are supposed to work, or whether there is a bug, or what. It's a simple enough question, but I keep getting non-answers like "install a newer version" and "there is some obscure reason why you should try something highly non-obvious (without any guarantee it will actually fix the problem...)". I am not asking a complicated question.

steve · **Posted:** Wed Aug 11, 2010 3:48 pm

No worries. I was asked to look at something. I did and gave my thoughts. If someone does not want to use that info, thats there choice and that obviously ok :)

andrej · **Joined:** Fri Aug 31, 2007 12:29 am **Posts:** 8

I think I came off a bit ungrateful. I apologize. I think my answer is a reflection of the frustration that was caused by what seemed to be a very basic non-working usage pattern, and it was impossible to find out whether we were doing something wrong, or whether we were hitting a bug. As I see things now it was a design issue (if not a design mistake).

There is a general principle of design which comes into play: if a programmer must do something in order for things to work correctly, then he should be told so very clearly (for example by an explicit warning in the documentation or FAQ, a compiler error, a comprehensible runtime warning, whatever). Otherwise the learning curve is more like a non-learning wall. I ask honestly: how am I supposed to find out as a newbie the correct way to use persistent Lists (with insert, update and remove operations)? Where would I look to discover the JPA requirement that you reference and the fact that we need a @JoinColumn to get things working?

I'd be very happy to hear that new versions of Hibernate don't have this particular problem, for the sake of all its users.

steve · **Posted:** Wed Aug 11, 2010 5:19 pm

I approach things perhaps a bit differently. I look at what you show and ask myself "is that reasonable?". Coming from a database background, I look at the snippets you show and ask myself "does the correspodning SQL make sense?".

Here it clearly does not line up with my expectation. So I dig a little to see why. Well the thing that does not match up for me is why there is an association table in between your Survey and your Page tables. As you even annotate, I think this should be a one-to-many (yes I understand the many-to-many can be morphed to be treated as a one-to-many based on strategically placed UNIQUE constraints). I just prefer to call an apple an apple, and when I see the word apple I expect to see an apple, not a pear. This is a rant aimed at the JPA spec group, not necessarily you.

Anyway, so I next ask myself "does that effect the outcome?". Well it certain *appears* to here does it not? I mean simply reordering pages within a survey has no need to update the link (FK value) between those things. TBH, I am not sure why that happens at all based on the values you provided; but again I am simply trying to remove what I see as inconsistencies.

I general yes there are some issues with reordering list elements that sometimes lead to constraint violations. But I dont think you are even getting to that part. "reordering" would be updateing POSITION, not PAGES_ID...

Long story short, I do not think accepting JPA defaults is an example of best practices. You can term that a "learning wall" if you want, but like all specs JPA is at its heart a production of "least common denominator".

andrej · **Joined:** Fri Aug 31, 2007 12:29 am **Posts:** 8

So if I understand you correctly, I should learn Hibernate by analyzing the SQL queries it generates and reverse-engineer correct annotations that will make it work?

By the way, does my example still not work in the new version of Hibernate? I don't have one installed anymore, so I can't try easily.

steve · **Posted:** Wed Aug 11, 2010 7:58 pm

I get the feeling that you are just jilted from the previous experience you described and are simply not going to be happy with anything I say or even interpret what I say correctly. If that's the case, fine that's you perogative; just say it and we can both not waste our time.

I explicitly said that's how I looked at it and why I said to try what I told you to try. But again I get the feeling you don't really care so..

shynate26 · **Posted:** Thu Aug 12, 2010 1:07 am

It seems to be a problem with coding and not with version of hibernate. If we remove elements , child from parent elements in this case its list, it has to get resolved. Andrej please correct your coding .

andrej · **Joined:** Fri Aug 31, 2007 12:29 am **Posts:** 8

@shynate26: can you be a bit more specific, what do you mean? Also, you know, if someone's asking "what's the problem here" and you answer "you have to correct your code", that's not really useful. The rest of your comment I don't parse.

shynate26 · **Posted:** Thu Aug 12, 2010 5:23 am

@andrej : If the child elemetns are unique then you can have Set, if you dont want any ordering of the elements. When deleting the child elements, ex:

survey.getPages().remove(page);

this will remove the page from the survey. Pleas tell me if this helps.