Deleting Parent and Children--muliple queries executed

prashu50 · **Joined:** Sun Sep 07, 2008 11:07 pm **Posts:** 17

Hibernate version: 3.2.2

Name and version of the database you are using:Oracle 10G

I have searched through the forum but could not find a solution to my problem

I have a Parent and multiple associated collections to it as shown below

Parent
---childx1
---childx2
---childy1
---childy2
---childz1
---childz2

For all the childs I have lazy=true
Also in the parent hbm file I have the cascade ="all"
I also do not want to set the ON CASCADE option at the Database level

I am trying to do the following

Code between sessionFactory.openSession() and session.close():
//hibernate loads the parent by executing a select statement
Parent parent = session.load(Parent.class,parentId);
session.delete(parent)

Hibernate tries to execute multiple SQL statements when it calls session.delete(parent)
1) It executes "Select" statement for each collection associated with it (In this case it executes 3 selects)
2) It executes "Delete" statement for each child in the collection. (In this case it executes 6 delete statements)

So a total of 9 statements are executed for this example. If we were to use plain JDBC it can be achieved by just 3 deletes

Why is Hibernate trying to execute the extra select statements and delete statements for each child in the collection?

I am sure Hibernate is smart.,but may it is ignorance on my part.
It will be great if somebody can explain me

Thanks
Prashant

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Set the cascade to "save-update" instead of "all".
Using "all", Hibernate goes one by one because it also checks for children on each of the child collection's elements.
But if you are sure the child collection does not have children in turn, you can use save-update, and it should perform the deletion in less SQL statements.

prashu50 · **Joined:** Sun Sep 07, 2008 11:07 pm **Posts:** 17

Just putting "save-update" will not delete the children as cascade will not be propagated.Hence it will throw ConstraintViolation exception.

Can somebody please help me

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

When a parent has many children, and the parent is deleted, Hibernate is not as intelligent as to issue a "delete from B where B.a=?"

However, Hibernate can be told that the database has a CASCADE DELETE, so that it doesn't try to execute extra statements.

The property to set for this is on-delete="cascade". Unfortunately, the Hibernate creators only made it available on an "inverse" kind of association.
This means that your association has to be bidirectional, and the code that adds children has to be intelligent enough to set the parent.

As an example of all this, create the following 2 tables.

Quote:

create table A (idA varchar(255) not null, primary key (idA))
create table B (idB varchar(255) not null, idA varchar(255) not null, primary key (idB))
alter table B add constraint FK42AB37F9DB foreign key (idA) references A on delete cascade

Use the following mapping

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="test4" >
  <class name="A" table="A">
    <id name="idA" type="string" column="idA" >
      <generator class="uuid"/>
    </id>
    <set name="bs" cascade="all-delete-orphan" inverse="true">
      <key column="idA" not-null="true" on-delete="cascade"/>
      <one-to-many class="B"/>
    </set>
  </class>
  
  <class name="B" table="B">
    <id name="idB" type="string" column="idB">
      <generator class="uuid"/>
    </id>
    <many-to-one name="a" column="idA" class="A" not-null="true" />
  </class>
</hibernate-mapping>

This is the code of the 2 classes

Code:

package test4;

import java.util.HashSet;
import java.util.Set;

public class A {
   private String idA;

   private Set<B> bs=new HashSet<B>();
   
   
   
   //setters-getters
   public String getIdA() {
      return idA;
   }

   public void setIdA(String id) {
      this.idA = id;
   }

   /**
    * @return the bs
    */
   public Set getBs() {
      return bs;
   }

   /**
    * @param bs the bs to set
    */
   public void setBs(Set bs) {
      this.bs = bs;
   }
   
   public void addB(B b){
      this.bs.add(b);
      b.setA(this);
   }

}

Code:

package test4;

import java.io.Serializable;
import java.util.Set;

public class B {
   private String idB;

   private A a;
   
   
   /**
    * @return the id
    */
   public String getIdB() {
      return idB;
   }

   /**
    * @param id the id to set
    */
   public void setIdB(String id) {
      this.idB = id;
   }

   /**
    * @return the a
    */
   public A getA() {
      return a;
   }

   /**
    * @param a the a to set
    */
   public void setA(A a) {
      this.a = a;
   }



}

And this is some simple client code, that adds children and then deletes the parent.

Code:

package test4;

import org.apache.log4j.Logger;
import org.apache.log4j.xml.DOMConfigurator;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class Main {
   
   static Logger logger=Logger.getLogger(Main.class.getName());
   public static void main(String[] args){
      
      Configuration cfg=new Configuration();
      cfg.addResource("test4/mapping.hbm.xml");
      cfg.setProperty("hibernate.dialect", "org.hibernate.dialect.MckoiDialect");
      cfg.setProperty("hibernate.connection.driver_class", "com.mckoi.JDBCDriver");
      cfg.setProperty("hibernate.connection.url", "jdbc:mckoi://localhost/app");
      cfg.setProperty("hibernate.connection.username", "myuser");
      cfg.setProperty("hibernate.connection.password", "mypass");
      cfg.setProperty("hibernate.connection.autocommit", "true");
      cfg.setProperty("hibernate.show_sql", "true");
      
      SessionFactory sef=cfg.buildSessionFactory();
      Session session=sef.openSession();
      
      //data
      A a=new A();

      B b1=new B();
      B b2=new B();
      B b3=new B();

      a.addB(b1);
      a.addB(b2);
      a.addB(b3);

      session.save(a);
      session.delete(a);
      session.flush();
   }
   
}

Once Main is executed, this is the SQL produced

Quote:

insert into A (idA) values (?)
insert into B (idA, idB) values (?, ?)
insert into B (idA, idB) values (?, ?)
insert into B (idA, idB) values (?, ?)
delete from A where idA=?

Notice how only one SQL is issued for deleting, because the database has cascade delete.

prashu50 · **Joined:** Sun Sep 07, 2008 11:07 pm **Posts:** 17

Thanks a lot for the detailed explanation.

I had put in my initial post that I do not want to use the Database cascade by specifiying ON-CASCADE in hbm file.

The reason being in that case you are not using Hibernate delete but relying on database. If we were to use the database cascade then we dont have to use Hibernate at all. When deleting a parent automatically Database would delete the child records.

Is there not a better way in Hibernate to do this. It has all the information it needs for deleting the child records.

Hoping to hear from experts soon

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Good luck waiting for the experts.

prashu50 · **Joined:** Sun Sep 07, 2008 11:07 pm **Posts:** 17

Can please anybody give explanation to this

prashu50 · **Joined:** Sun Sep 07, 2008 11:07 pm **Posts:** 17

Please help me

nasedo47 · **Joined:** Tue Nov 04, 2008 11:32 am **Posts:** 2

Hi, I have the same problem and I am using a SQLite DB.