codesmell.org

As you might know already, Hibernate adds an important non-Spec Feature to the JPA:

ordered List Mappings

. It is used as easy as:

A.java

                    @OneToMany(fetch = FetchType.LAZY, cascade = { CascadeType.ALL })
                    @IndexColumn(name = "sort" , base = 1) private List<B> b = new LinkedList<B>();
                

The good news is: Hibernate preserves the order of the List by adding a column “sort” to the JoinTable.
The bad one is: there seems to be a bug (or at least some inconvenient behaviour) in it:

Suppose this code run against a MySQL/InnoDB Database:

                    A a = new A(); List<B> b = a.getB(); // empty list B b1 = new B(); B b2 = new B(); b.add(b1); b.add(b2); em.persist(a); em.persist(b1); em.persist(b2); // all fine. now swap them: b.remove(b2); // remove from the end (1) b.add(0,b2); // and add as first (2)
                

I felt a little dizzy watching the console when flushing the state to the DB:

javax.persistence.RollbackException: Error while commiting the transaction
Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
Caused by: java.sql.BatchUpdateException: Duplicate entry ’2′ for key 2
… 33 more

The point is: hibernate tries to use two update commands in order to update the JoinTable which seems to break the constraints (Should’t they be checked at the Transaction’s end anyway?)

You can work around this problem by flushing after removal in order to cause hibernate to delete/insert/update instead of update/update:

                    b.remove(b2); em.flush(); // makes the delete command execute when tx is comitted b.add(0,b2); // there will be a new row inserted for this one
                

Is it just me or is Hibernate too clever here? Or is InnoDB just too picky with the constraints being checked too early? Must check this against another DB…