How to change the ordering of sql execution in hibernate

Question

I am trying to model bidirectional parent-child design, with ordered children.

When removing child (eg. child #2 of 3 children) from the parent, hibernate's generated sql led to unique constraint violation because "update" (sibling) is being executed before the "delete" (target).

The RDBMS that I use (H2) does not support deferred constraint. What are the options I have besides the following ?

• remove the unique constraint from schema
• explicitly manage the sorting myself, rather than depend on hibernate

Is there any way to make hibernate generate sql with 'delete' precedes 'update' ?

---

Some old discussion found in the forum:

DELETE then INSERT in collection - Order of executed SQL

---

DB Schema:

CREATE TABLE IF NOT EXISTS  Sequences (
ID                      BIGINT NOT NULL AUTO_INCREMENT,
Name                    LONGVARCHAR,
Type                    LONGVARCHAR,
Sequence                LONGVARCHAR,

ParentId                BIGINT DEFAULT NULL,
Index                   INT,

CONSTRAINT pk_SequenceId    PRIMARY KEY     (ID),
CONSTRAINT uc_Sequences     UNIQUE          (ParentId, Index),
CONSTRAINT fk_Sequences
    FOREIGN KEY (ParentId) 
    REFERENCES Sequences(ID) 
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Class:

public class Sequence {
    protected Long ID;
    protected String name;
    protected String type;
    protected String sequence;
    protected Sequence  parentSequence;
    protected List<Sequence> childSequences = new ArrayList<Sequence>();
}

HBM Mapping:

<hibernate-mapping>
<class name="Sequence" table="Sequences">
    <id name="ID" column="ID" type="long">
        <generator class="native"/>
    </id>

    <property name="name" type="string" column="Name"/>
    <property name="type" type="string" column="Type"/>
    <property name="sequence" type="string" column="Sequence"/>

    <many-to-one name="parentSequence" column="parentId" cascade="save-update" insert="false" update="false" class="Sequence" />

    <list name="childSequences" inverse="false" lazy="true" cascade="all-delete-orphan">
        <key column="parentId" not-null="true"/>
        <list-index column="Index" base="0"/>
        <one-to-many class="Sequence"/>
    </list>
</class>

Answer 1

Hibernate does not execute the HQL (or SQL) statements directly but in the moment of a commit() or flush() it re-orders the SQL statement with the goal to do them in the most effective way. But it can happen the re-order from Hibernate is wrong and for example causes constraint violations, as in your case.

The solution is to introduce an intermediate flush(). flush() forces the re-order and send the SQL statements, but it does not commit.

In your case you can modify your code like (as a sketch):

transaction = session.beginTransaction();
session.delete(obj);
session.flush();     /* newly introduced */
session.update(...);
transaction.commit();

If the problem should be in a cascade delete or some delete executed by Hibernate without your control, then you have to take the control over the delete and update process and do the operations explicitely in your code instead of relying on the automatisms of Hibernate.