-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Quick HQL Deletes
PostPosted: Thu Sep 28, 2006 4:57 am 
Beginner
Beginner

Joined: Tue Jul 04, 2006 11:29 am
Posts: 20
Location: Cambs, UK
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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 28, 2006 9:57 am 
Senior
Senior

Joined: Wed Aug 17, 2005 12:56 pm
Posts: 136
Location: Erie, PA (USA)
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 ...

_________________
---- Don't forget to rate! ----


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 28, 2006 10:22 am 
Beginner
Beginner

Joined: Tue Jul 04, 2006 11:29 am
Posts: 20
Location: Cambs, UK
That's what I did in the end. Shame it doesn't cascade but oh well. Credits coming your way :)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.