Quick HQL Deletes

Leesy · **Posted:** Thu Sep 28, 2006 4:57 am

The usual information about my setup including mapping docs are at the bottom of this post.

My problem is this: I have a table with a relationship to another table. I would like to delete all the rows in those two tables with are related to eachother and one more table.

Using Criteria's I can quite easily get the Entry objects with a certain foreign key. I can then just loop through these Entry objects and then just use session.delete() and as I have my cascades set up properly, it will handle deleting all the related rows from EntryValues too.

The trouble with this approach is that is that there are a lot of queries being called and a lot of unneccesiary objects being created. As each entry can contain something like 40/50 EntryValues, doing it this way takes a long time.

So what I want is a simple one or two line delete. Here's what I have tried:

HQL Statement
Can only have one table in a DELETE FROM clause so using something like "DELETE FROM Entry e, EntryValue ev WHERE ..." won't work. I could list over the entries and then use a one line HQL to delete all the related EntryValues before moving onto the next Entry. I would like something more simplier than this.
SQL Statement
I guess I could have a long SQL statement but this would end up being the only SQL statement in all my code (all the rest is Criteria code with the odd HQL statement) and that is something I would also like to avoid.

So does anyone have any ideas on the quickest and easiest way to delete all the rows in Entry and the relating ones in EntryValue?

Cheers,
Lee

Hibernate version: 3.1

Mapping documents:
Entry:

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>
   <class name="Entry" table="entry">
      <cache usage="read-write" />
      <id name="id" column="entry_id">
         <generator class="native" />
      </id>
      <!-- Properties here -->

      <many-to-one name="index"
         class="Index" column="index_id"
         not-null="true" />

      <set cascade="all-delete-orphan" name="entryValues">
         <key column="entry_id" />
         <one-to-many
            class="com.oobjects.datastore.tables.EntryValue" />
      </set>
   </class>
</hibernate-mapping>

EntryValue:

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>
   <class name="EntryValue"
      table="entry_value">
      <cache usage="read-write" />
      <id name="id" column="entry_value_id">
         <generator class="native" />
      </id>
      <property name="value" length="1024" />

      <many-to-one name="entry"
         class="Entry" column="entry_id" />
   </class>
</hibernate-mapping>

Name and version of the database you are using: MySQL 5.0.24-standard

CWitherow · **Posted:** Thu Sep 28, 2006 9:57 am

How about using HQL bulk delete like the following:

Code:

Query qry = session.createQuery("DELETE FROM Child c WHERE c.parent.id IN (SELECT p.id FROM Parent p WHERE <<whatever condition you need>>)");
qry.executeUpdate();

qry = session.createQuery("DELETE FROM Parent p WHERE <<whatever condition you need>>");
qry.executeUpdate();

This should end up executing just 2 SQL statements.

Good luck,
Curtis ...

Leesy · **Posted:** Thu Sep 28, 2006 10:22 am

That's what I did in the end. Shame it doesn't cascade but oh well. Credits coming your way :)