hibernar.org

A friendly forum for Hibernate users

Reducing the number of SQL DELETE queries

When a parent object has several children, and the parent is deleted, Hibernate first issues one delete statement for each child. ¿How can we reduce the number of SQL queries generated?

Let's suppose we have a parent object of class "A", and several children object of class "B" attached to it in a set.
Let's also suppose that object's of class B cannot exist without a parent; in other words, B objects should not exist as independent entities.
A common way to represent this kind of relationship in the database is to have 2 tables, A and B, each one with its own id, idA and idB, and in addition table B also having a foreign key to table A, not null.
This would be the DDL:

	create table A (id varchar(255) not null, primary key (id))
	create table B (id varchar(255) not null, idA varchar(255), primary key (id))
	alter table B add constraint FKBtoA  foreign key (idA) references A

(There is also another way to represent the relationship between A and B: a third "relationship table". But I prefer to use this 3rd table approach for when A and B are independent entities).
The classes corresponding to these 2 tables would be:

	package test8;

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

	public class A {
		private String id;
		private Set<B> bs=new HashSet<B>();

		/**
		 * @return the id
		 */
		public String getId() {
			return id;
		}

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

		public Set<B> getBs() {
			return this.bs;
		}

		public void setBs(Set<B> bs) {
			this.bs = bs;
		}

	}

    package test8;

	public class B {
		private String id;

		/**
		 * @return the id
		 */
		public String getId() {
			return id;
		}

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

	}

And a typical mapping configuration file for both classes would be:

	<?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="test8" >
	  <class name="A" table="A">
		<id name="id" type="string" column="id">
		  <generator class="uuid"/>
		</id>
		<set name="bs" table="B" cascade="all">
		  <key column="idA" not-null="true"/>
		  <one-to-many class="B"/>
		</set>
	  </class>

	  <class name="B" table="B">
		<id name="id" type="string" column="id">
		  <generator class="uuid"/>
		</id>
	  </class>
	</hibernate-mapping>

Notice the not-null="true" property used when indicating the foreign key idA. Notice also that, although this foreign key "idA" belongs to table B, it is indicated in the mapping of class A, not class B. Now, let's run some simple client code that creates one parent instance of A and adds several child instances of B to it, and then deletes the parent.

	package test8;

	import org.hibernate.Session;
	import org.hibernate.SessionFactory;
	import org.hibernate.cfg.Configuration;
	import org.hibernate.tool.hbm2ddl.SchemaExport;

	public class Main {

		public static void main(String[] args){
			Configuration cfg=new Configuration();
			cfg.addResource("test8/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", "mypassword");
			cfg.setProperty("hibernate.connection.autocommit", "true");
	//		cfg.setProperty("hibernate.show_sql", "true");

			SessionFactory sef=cfg.buildSessionFactory();
			SchemaExport se=new SchemaExport(cfg);
			se.create(true, true);

			Session session=sef.openSession();

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

			a.getBs().add(b1);
			a.getBs().add(b2);
			a.getBs().add(b3);

			session.persist(a);
			session.delete(a);

			session.flush();


		}
	}

This code works as expected, but when the parent is deleted, it generates one SQL DELETE statement per each of the children, before deleting the parent:

	DEBUG - insert into A (id) values (?)
	DEBUG - insert into B (idA, id) values (?, ?)
	DEBUG - insert into B (idA, id) values (?, ?)
	DEBUG - insert into B (idA, id) values (?, ?)	
	DEBUG - delete from B where id=?
	DEBUG - delete from B where id=?
	DEBUG - delete from B where id=?	
	DEBUG - delete from A where id=?

This happens because the "cascade" attribute in the mapping configuration file was set to "all". "All" means that all operations performed on the parent are propagated to the children. In our example, we needed to propagate the operation persist() and delete() to the children.
Hibernate checks for all children of the a objects, and deletes them one by one. If I tried to put a value other than "all" or "delete" in that property, Hibernate would not have deleted the children, and by the time I wanted to delete the parent, a database error would have occurred.
Hibernate, at least at the time of writing this (version 3.3.2), is not intelligent enough to issue a single SQL statement deleting all the children at once.

There are basically 2 solutions to reduce the number of generated SQL DELETE statements. One, is to execute native SQL queries. The other, is to let the database cascade delete, and configure Hibernate accordingly. The second is, I think, a superior solution.
I demonstrate both solutions in separate posts, following this one.

Solution Nº1: write native queries

A tentative solution would be trying to manually issue a native SQL query.
Hibernate allows you to write native SQL queries this way:

	SQLQuery  sqlQuery=session.createSQLQuery("DELETE FROM B where idA='" + a.getId() + "'");
	sqlQuery.executeUpdate();[/quote]

But, in general, one should try to avid using native SQL queries in Hibernate.
They produce database modifications of which Hibernate is not aware, so you have to laboriously write code to bring the entities stored in your session back in synchronization with the database.
Besides, native SQL queries are contrary to the general philosophy of Hibernate, which is, to achieve all your database modifications via object manipulations.
The following client code achieves the deletion of all children in one single step, using a native SQL query:

	package test8;

	import org.hibernate.SQLQuery;
	import org.hibernate.Session;
	import org.hibernate.SessionFactory;
	import org.hibernate.cfg.Configuration;
	import org.hibernate.tool.hbm2ddl.SchemaExport;


	public class Main {

		public static void main(String[] args){
			Configuration cfg=new Configuration();
			cfg.addResource("test8/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", "mypassword");
			cfg.setProperty("hibernate.connection.autocommit", "true");
	//		cfg.setProperty("hibernate.show_sql", "true");

			SessionFactory sef=cfg.buildSessionFactory();
			SchemaExport se=new SchemaExport(cfg);
			se.create(true, true);

			Session session=sef.openSession();

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

			a.getBs().add(b1);
			a.getBs().add(b2);
			a.getBs().add(b3);

			session.persist(a);
			session.flush();

			session.evict(b1);
			session.evict(b2);
			session.evict(b3);
			a.getBs().clear();

			SQLQuery  sqlQuery=session.createSQLQuery("DELETE FROM B where idA='" + a.getId() + "'");
			sqlQuery.executeUpdate();

			session.delete(a);

			session.flush();


		}


	}

Notice, however, the clumsy series of sessin.evict() calls ad the a.getBs().clear() that had to be made so that the session doesn't store children that don't exist anymore in the database. Also, I had to insert several session.flush() calls, so that Hibernate executed the SQL statements at the right time. This should also be avoided if possible, letting Hibernate decide when to write SQL to the database.
An alternative to native SQL would be to use Hibernate HQL queries, which do keep the session in synchronicity with the database. We could write something like this:

	Query query=session.createQuery("delete B b where b.idA='" + a.getId() + "'");
	query.executeUpdate();

The problem is, however, that there is no b.idA property in our project!
There is no such property in the mapping file for B, and there is no B.getIdA() property in the B class.
This makes sense, because idA is not really a "property" of B, just a means to establish a connection to its parent. If we tried to add such a property to the mapping file for B ...

	<class name="B" table="B">
	  <id name="id" type="string" column="id">
	    <generator class="uuid"/>
	  </id>
	  <property name="idA" type="string"/>
	</class>

... Hibernate would complain, saying that the mapping of idA is "repeated".

  Exception in thread "main" org.hibernate.MappingException: Repeated column in mapping for entity: test8.B column: idA (should be mapped with insert="false" update="false")
	at org.hibernate.mapping.PersistentClass.checkColumnDuplication(PersistentClass.java:652)
	at org.hibernate.mapping.PersistentClass.checkPropertyColumnDuplication(PersistentClass.java:674)
	...

Indeed, we are already using it for something else: as a foreign key from B to A.

Second solution: Let the database do the CASCADE DELETE

If the foreign key relationship in the database is configured as CASCADE DELETE, that means that every time a parent row is deleted, all its dependent children rows have to be automatically deleted first. Then, the deletion of both parent and children can occur in one single SQL statement.
On the Hibernate side, we have to notify Hibernate that there is a "cascade delete" setting between parent and son. Otherwise, Hibernate would fail to remove the children from the session when the parent is removed, and problems would occur.
Unfortunately, things are a little more complicated when using "cascade delete" in Hibernate.
We can only use the on-delete="cascade" setting that does what we want, on the "inverse" side of a bidirectional association.
This means that our children will have to offer a child-parent relationship (eve if we don't plan to use it), and that is reflected in changes on the mapping file and the child B class.

Let's start by creating our B table with "cascade delete"

	create table A (id varchar(255) not null, primary key (id))
	create table B (id varchar(255) not null, idA varchar(255), primary key (id))
	alter table B add constraint FKBtoA foreign key (idA) references A on delete cascade[/i]

Then, we create our classes. Notice that now, the B class also contains a reference to the parent, because the association has to be bi-directional. Class A remains the same.

	package test8;

	public class B {
		private String id;
		private A a;

		/**
		 * @return the id
		 */
		public String getId() {
			return id;
		}

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

		public A getA() {
			return a;
		}

		public void setA(A a) {
			this.a = a;
		}

	}

In our mapping file, we change the following:

The key one-to-many relationship in the Set of A contains the property on-delete="cascade", in order to indicate that the database performs cascade deletions.
The property "inverse" in the set is given the value "true"
A many-to-one mapping is added on the child side (class B) in order to make the parent-child relationship bidirectional.

mapping.hbm.xml

	<?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="test8" >
	  <class name="A" table="A">
		<id name="id" type="string" column="id">
		  <generator class="uuid"/>
		</id>
		<set name="bs" table="B" cascade="all" 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="id" type="string" column="id">
		  <generator class="uuid"/>
		</id>
		<many-to-one name="a" class="A" column="idA"/>
	  </class>
	</hibernate-mapping>

Now, a the client code is simplified back to its original form, where we only have to call one session.delete(parent) Main.java

	package test8;

	import org.hibernate.Query;
	import org.hibernate.Session;
	import org.hibernate.SessionFactory;
	import org.hibernate.cfg.Configuration;


	public class Main {

		public static void main(String[] args){
			Configuration cfg=new Configuration();
			cfg.addResource("test8/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", "mypassword");
			cfg.setProperty("hibernate.connection.autocommit", "true");
			SessionFactory sef=cfg.buildSessionFactory();

			Session session=sef.openSession();

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

			a.getBs().add(b1);
			a.getBs().add(b2);
			a.getBs().add(b3);

			session.persist(a);

			session.delete(a);

			session.flush();
		}


	}

And only one delete statement is generated.

	DEBUG - insert into A (id) values (?)
	DEBUG - insert into B (idA, id) values (?, ?)
	DEBUG - insert into B (idA, id) values (?, ?)
	DEBUG - insert into B (idA, id) values (?, ?)
	DEBUG - delete from A where id=?