Delete an object which has a <many-to-many> set.

piloupy · **Joined:** Wed May 02, 2007 7:24 am **Posts:** 4

Hi,

I encounter a problem when deleting an object which has a <many-to-many> set.

Let me introduce my problem through a cute example with turtles and salads they can eat :)

Code:

TURTLE TABLE
+-----------+-------------+
| TURTLE_ID | TURTLE_NAME |
+-----------+-------------+
| 1         | john        |
+-----------+-------------+
| 2         | smith       |
+-----------+-------------+
|           |             |

SALAD TABLE
+----------+------------+
| SALAD_ID | SALAD_NAME |
+----------+------------+
| 1        | endive     |
+----------+------------+
| 2        | lettuce    |
+----------+------------+
| 3        | iceberg    |
+----------+------------+
|          |            |

TURTLE_SALAD TABLE
+-----------+----------+
| TURTLE_ID | SALAD_ID |
+-----------+----------+
| 1         | 1        |
+-----------+----------+
| 1         | 2        |
+-----------+----------+
| 2         | 1        |
+-----------+----------+
| 2         | 3        |
+-----------+----------+
|           |          |

I have 2 turtles :
- John which can eat endive and lettuce.
- Smith which can eat endive and iceberg.

When I try to delete a Turtle, I have no error.

Code:

Hibernate: delete from TURTLE_SALAD where TURTLE_ID=?
Hibernate: delete from TURTLE where TURTLE_ID=?

But it's not the case when I try to delete a Salad.

Code:

Hibernate: delete from SALAD where SALAD_ID=?
[WARN] (2007-05-09 14:03:30,312) org.hibernate.util.JDBCExceptionReporter - SQL Error: 1217, SQLState: 23000
[WARN] (2007-05-09 14:03:30,312) org.hibernate.util.JDBCExceptionReporter - SQL Error: 1217, SQLState: 23000
[ERROR] (2007-05-09 14:03:30,312) org.hibernate.util.JDBCExceptionReporter - Cannot delete or update a parent row: a foreign key constraint fails
[ERROR] (2007-05-09 14:03:30,312) org.hibernate.util.JDBCExceptionReporter - Cannot delete or update a parent row: a foreign key constraint fails
[ERROR] (2007-05-09 14:03:30,312) org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:144)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at beans.SaladeMgr.deleteSalade(SaladeMgr.java:45)
   at Main2.main(Main2.java:28)
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails
   at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:665)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
   ... 9 more
[ERROR] (2007-05-09 14:03:30,312) org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:144)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at beans.SaladeMgr.deleteSalade(SaladeMgr.java:45)
   at Main2.main(Main2.java:28)
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails
   at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:665)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
   ... 9 more

Normally, Hibernate should delete rows from TURTLE_SALAD table before deleting the salad's row from SALAD table.

Can anybody explain me my mistake et how to repair it ?

Thanks in advance,

piloupy

Hibernate version: 3.2.2

Mapping documents:

Turtle.hbm.xml

Code:

<hibernate-mapping>

  <class name="beans.Turtle" table="TURTLE">

    <id name="id" column="TURTLE_ID">
      <generator class="native" />
    </id>

    <property name="name" column="TURTLE_NAME" unique="true" />

    <set name="salads" table="TURTLE_SALAD" lazy="false">
      <key column="TURTLE_ID" />
      <many-to-many column="SALAD_ID" class="beans.Salad" />
    </set>

  </class>

</hibernate-mapping>

Salad.hbm.xml

Code:

<hibernate-mapping>

  <class name="beans.Salad" table="SALAD">

    <id name="id" column="SALAD_ID">
      <generator class="native" />
    </id>

    <property name="name" column="SALAD_NAME" unique="true" />

    <set name="turtles" table="TURTLE_SALAD" lazy="false" inverse="true">
      <key column="SALAD_ID" />
      <many-to-many column="TURTLE_ID" class="beans.Turtle" />
    </set>

  </class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

deleteTurtle

Code:

  public static void deleteTurtle(long id) throws Exception {
    Session session = factory.getCurrentSession();
    Transaction tx = null;
    try {
      tx = session.beginTransaction();

      Turtle turtle = (Turtle) session.get(Turtle.class, id);

      if (turtle != null)
        session.delete(turtle);
      else
        throw new Exception("TurtleId not found");

      tx.commit();
    } catch (Exception e) {
      if (tx != null)
        tx.rollback();
      throw e;
    }

  }

deleteSalad

Code:

  public static void deleteSalad(long id) throws Exception {
    Session session = factory.getCurrentSession();
    Transaction tx = null;
    try {
      tx = session.beginTransaction();

      Salad salad = (Salad) session.get(Salad.class, id);

      if (salad != null)
        session.delete(salad);
      else
        throw new Exception("SaladId not found");

      tx.commit();
    } catch (Exception e) {
      if (tx != null)
        tx.rollback();
      throw e;
    }

  }

Name and version of the database you are using: MySQL 4.1.22

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

I know this is slightly outside of the scope of your problem but if you made your DAO objects extend Spring's HibernateDAOSupport you woulnd't need all that nasty boilerplate code...