• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Deleting records from MySql/Hibernate (Challenging)

I have a strange problem and I can't neither a rhyme or reason for the behavior...I'm hoping someone can though.  I'll post the xml representations of the same table in it's original state (first) and then in the final version (second).  Lastly, I'll post the section of code I'm using in my swing app that performs the actual delete on the object itself.

THE PROBLEM:
When I'm looking at my JTable (which has a custom table model) and I choose records to delete and then pass them (as objects) to session.delete() and call tx.commit() it will work about 80% of the time.  The other times I get the following message returned to me from a try/catch block: "deleted objects would be re-saved by cascade. remove deleted object from associations"  When I get this error, the object disappears from the JTable, however the actual delete is not reflected to the database and the object will reload in the JTable if I refresh the data.

I'm not understanding this because any objects these objects may be related to are not loaded at the time of the delete.  In fact, I always close sessions so any objects that are available are so only as transient objects anyways meaning that they would not automatically cascade back into the db, correct?  Anyways, here comes the code...thanks for any help.

I'd give this 1000 points if I could.  :)

ORIGINAL TABLE
<?xml version="1.0"?>
<!DOCTYPE ROOT SYSTEM ".dtd">
<ROOT>
      <row>
            <id>1</id>
            <StartTime>2004-10-29 21:36:15</StartTime>
            <EndTime>2004-10-29 21:36:15</EndTime>
            <Comments>(Enter Description)</Comments>
            <Total>0</Total>
            <machine_id>1</machine_id>
            <dtcode_id>1</dtcode_id>
      </row>
      <row>
            <id>22</id>
            <StartTime>2004-10-29 21:50:42</StartTime>
            <EndTime>2004-10-29 21:50:42</EndTime>
            <Comments>(Enter Description)</Comments>
            <Total>3</Total>
            <machine_id>1</machine_id>
            <dtcode_id/>
      </row>
      <row>
            <id>23</id>
            <StartTime>2004-10-29 21:50:42</StartTime>
            <EndTime>2004-10-29 21:50:42</EndTime>
            <Comments>(Enter Description)</Comments>
            <Total>6</Total>
            <machine_id>1</machine_id>
            <dtcode_id>1</dtcode_id>
      </row>
      <row>
            <id>24</id>
            <StartTime>2004-10-29 21:50:42</StartTime>
            <EndTime>2004-10-29 21:50:42</EndTime>
            <Comments>(Enter Description)</Comments>
            <Total>6</Total>
            <machine_id>1</machine_id>
            <dtcode_id>30</dtcode_id>
      </row>
      <row>
            <id>6</id>
            <StartTime>2004-10-29 21:38:09</StartTime>
            <EndTime>2004-10-29 21:38:09</EndTime>
            <Comments>(Enter Description)</Comments>
            <Total>0</Total>
            <machine_id>1</machine_id>
            <dtcode_id/>
      </row>
      <row>
            <id>25</id>
            <StartTime>2004-10-29 21:50:42</StartTime>
            <EndTime>2004-10-29 21:50:42</EndTime>
            <Comments>(Enter Description)</Comments>
            <Total>4</Total>
            <machine_id>1</machine_id>
            <dtcode_id/>
      </row>
</ROOT>


ALTERED TABLE (only objects remaining are objects that it would not let me delete)
<?xml version="1.0"?>
<!DOCTYPE ROOT SYSTEM ".dtd">
<ROOT>
      <row>
            <id>1</id>
            <StartTime>2004-10-29 21:36:15</StartTime>
            <EndTime>2004-10-29 21:36:15</EndTime>
            <Comments>(Enter Description)</Comments>
            <Total>0</Total>
            <machine_id>1</machine_id>
            <dtcode_id>1</dtcode_id>
      </row>
      <row>
            <id>6</id>
            <StartTime>2004-10-29 21:38:09</StartTime>
            <EndTime>2004-10-29 21:38:09</EndTime>
            <Comments>(Enter Description)</Comments>
            <Total>0</Total>
            <machine_id>1</machine_id>
            <dtcode_id/>
      </row>
</ROOT>

CODE EXCERPTS (Pertinent functions only)

//function called to instantiate all Hibernate-specific variables and establish the initial connection
public void InitHibernate(){
        try{
            // Create a configuration based on the properties file we've put
            // in the standard place.
            config = new Configuration();

            // Tell it about the classes we want mapped, taking advantage of
            // the way we've named their mapping documents.
            config.addClass(Machine.class);
            config.addClass(DTEvent.class);
            config.addClass(DTCategory.class);
            config.addClass(DTCode.class);

            // Get the session factory we can use for persistence
            sessionFactory = config.buildSessionFactory();

            // Ask for a session using the JDBC information we've configured
            session = sessionFactory.openSession();

            // Create some data and persist it
            tx = session.beginTransaction();
        }
        catch(Exception ex){JOptionPane.showMessageDialog(null, ex.getMessage());}
    }

//function called to do the initial load on my JTable
public void LoadEventList(){
       
        //set table model for machine list
        SetTable();
       
        //declare list to hold machines
        List events = null;
       
        try {
                // Ask for a session using the JDBC information we've configured
                session = sessionFactory.openSession();
                try{
                    Date today = new Date();
                    long todayTime = today.getTime();
                    long adjustedTime = (1000*3600*36);
                    long calculatedTime = todayTime - adjustedTime;
                    today.setTime(calculatedTime);
                   
                    Query query = session.getNamedQuery("com.bsuguy.FMS.DTDB.CurrentEvents");
                    query.setDate("yesterday", today);
                    events = query.list();

//                    JOptionPane.showMessageDialog(null, String.valueOf(events.size()));
                }
                catch(Exception ex){}
      }
        catch(Exception e){JOptionPane.showMessageDialog(null, e.getMessage());}
        finally {
            try{
                // No matter what, close the session
                session.close();
            }
            catch(Exception e){JOptionPane.showMessageDialog(null, e.getMessage());}
        }
        if(events!=null)
            eventModel.addEventList(events);
    }
0
lomidien
Asked:
lomidien
  • 2
  • 2
  • 2
1 Solution
 
CEHJCommented:
What is the cascade behaviour between dependent entities that you've defined in your mapping?
0
 
CEHJCommented:
The solution, i think, is to ensure you have the above defined correctly and also that you have no references to any entities (or dependents) elsewhere in your code when you attempt to delete
0
 
lomidienAuthor Commented:
CEHJ,

Thanks for taking a stab at this one.  I was really hoping to wake up and see that someone had posted something.  Let me post some further info below:

Below, I'll post the mapping doc excerpts from the pertinent classes (Machine, DTCode, DTCategory, DTEvent).  I'll provide a explanation of their relationship here and maybe that will make it easier to understand.  When a machine goes down for whatever reason, an operator logs this time in the database as a DTEvent.  Each DTEvent is catagorized by a DTCode which explains the reason for the downtime and each of these DTCodes belongs to a DTCategory such as Unscheduled, No-Sales, Etc.

Mapping Doc Excerpts

DTEvent

    <id name="id" type="int" column="id">
      <meta attribute="scope-set">protected</meta>
      <generator class="native"/>
    </id>

    <property name="StartTime" type="timestamp" not-null="true"/>

    <property name="EndTime" type="timestamp" not-null="true"/>

    <property name="Comments" type="string" not-null="true"/>

    <property name="Total" type="float" not-null="true"/>

    <many-to-one name="machine" class="com.bsuguy.FMS.DTDB.Machine" column="machine_id"/>

    <many-to-one name="dtcode" class="com.bsuguy.FMS.DTDB.DTCode" column="dtcode_id"/>


Machine

    <id name="id" type="int" column="id">
      <meta attribute="scope-set">protected</meta>
      <generator class="native"/>
    </id>

    <property name="name" type="string" not-null="true">
      <meta attribute="use-in-tostring">true</meta>
    </property>

    <property name="description" type="string" not-null="true">
    </property>

    <property name="jobnumber" type="string" not-null="true"/>

    <property name="department" type="string" not-null="true"/>

    <set name="dtevents" table="dtevents" inverse="true" cascade="all">
        <key column="id"/>
        <one-to-many class="com.bsuguy.FMS.DTDB.DTEvent"/>
    </set>

DTCode

    <id name="id" type="int" column="id">
      <meta attribute="scope-set">protected</meta>
      <generator class="native"/>
    </id>

    <property name="Description" type="string" not-null="true"/>

    <many-to-one name="category" class="com.bsuguy.FMS.DTDB.DTCategory" column="dtcategory_id"/>

    <property name="Department" type="string" not-null="true"/>

    <set name="dtevents" table="dtevents" inverse="true" cascade="all">
        <key column="id"/>
        <one-to-many class="com.bsuguy.FMS.DTDB.DTEvent"/>
    </set>

DTCategory

    <id name="id" type="int" column="id">
      <meta attribute="scope-set">protected</meta>
      <generator class="native"/>
    </id>

    <property name="category" type="string" not-null="true"/>

    <property name="description" type="string" not-null="true"/>

    <set name="dtcodes" table="dtcodes" inverse="true" cascade="all">
        <key column="id"/>
        <one-to-many class="com.bsuguy.FMS.DTDB.DTCode"/>
    </set>

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
GuntCommented:
When you load DTEvent, it loads dependent objects also  (mapped with many-to-one).
So, you have DTEvent, and Machine that references this DTEvent (with it's set of DTEvents), and also DTCode that references this DTEvent (with it's set of DTEvents).

Probably, when you call session.delete(), Hibernate is re-attaching your objects to the session, so cascades will count.
The easy way to do this, is mantaining consistency in bidireccional associations.

If you have a Machine, which has a set of DTEvents, in your Machine you probably have:
public void addDTEvent(DTEvent e) {
   this.dtEvents.add(e);
   e.setMachine(this);
}

In bidireccional relationships, YOU have to mantain consitency, if you add the DTEvent to a Machine, you have to set that Machine to the DTEvent (always set both ends of the association).

Now, what happens with deletion?: Also mantain consitency. If you delete a DTEvent, also delete the reference to it from the collection. Some code (I assume a method that deletes a given event):
public void deleteEvent(DTEvent e) {
  // Remove the reference
  e.getMachine().getDTEvents().remove(e);
  e.setMachine(null); // Not really needed
  e.getCode().getDTEvents().remove(e);
  e.setCode(null);
  //Now delete it
  // Obtain session
  session.delete(e);
}

This code is ugly, actually you should provide methods to remove events from machines and codes, which mantain consistency:
public void removeEvent(DTEvent e) {
  this.dtEvents.remove(e);
  e.setMachine(null);
}

Hope this helps!
 
0
 
lomidienAuthor Commented:
Very nice explanation.  Thanks a lot!!!!

David
0
 
GuntCommented:
You're welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now