Unable to delete a parent in many-to-one

schof · **Joined:** Sat Jul 08, 2006 8:44 am **Posts:** 6

I have a many-to-one relationship where I would like to delete the parent object and leave the children as orphans. My test case is failing, however, and complaining about batch updates.

WARNING: SQL Error: 0, SQLState: null
Jul 10, 2006 10:37:29 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: failed batch
Jul 10, 2006 10:37:29 AM org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update

...

Here's my mapping:

Code:

    
    <class name="Category">
      <id name="id"
          column="CATEGORY_ID">
        <generator class="native"/>
      </id>
      <property name="description"/>
      <property name="parentCategory"/>
      <set name="pets"
           inverse="true"
           cascade="save-update">
        <key column="category_id"/>
        <one-to-many class="Pet"/>
      </set>
    </class>

    <class name="Pet">
      <id name="id"
          column="pet_id">
        <generator class="native"/>
      </id>
      <property name="productId"/>
      <property name="description"/>
      <property name="price"/>
      <property name="quantity"/>
      <many-to-one
          name="category"
          column="category_id"
          class="Category"/>
    </class>

And here's my test case:

Code:

    public void testRemoveCategory() throws Exception {
        // create a couple of pets
        Pet pet1 = new Pet("12345", "Pet1");
        Pet pet2 = new Pet("78901", "Pet2");
        category.addPet(pet1);
        category.addPet(pet2);

        // persist the category
        long id = categoryDao.store(category);

        categoryDao.getHibernateTemplate().flush();
        categoryDao.getHibernateTemplate().clear();

        Category retrievedCategory = categoryDao.get(id);
        categoryDao.remove(retrievedCategory);

        categoryDao.getHibernateTemplate().flush();
        categoryDao.getHibernateTemplate().clear();

        retrievedCategory = categoryDao.get(id);
        assertNull("retrievedCategory", retrievedCategory);
    }

TIA,

Sean

schof · **Joined:** Sat Jul 08, 2006 8:44 am **Posts:** 6

I am able to delete when I change the one-to-many configuration cacade to "delete-all-orphan"

Code:

      <set name="pets"
           inverse="true"
           cascade="all-delete-orphan">
        <key column="category_id"/>
        <one-to-many class="Pet"/>
      </set>

But this is not what I really wanted. I want the category to be deleted and then have the pets unassigned to a category (and potentially assigned to a new category later.)

How do I set this type of relationship up?

Sean

batmat · **Posted:** Mon Jul 10, 2006 4:53 pm

all-delete-orphan is the contrary of what you want to do. The use case of this conf option is : delete a parent only, cascades it deleting automatically the children of this relation...

schof · **Joined:** Sat Jul 08, 2006 8:44 am **Posts:** 6

I know its the all-delete-orphan is the opposite of what I want to do. Any idea on how to solve the original problem?

dsm08873 · **Posted:** Tue Jul 11, 2006 1:42 pm

I think you have a couple of options:

1. Explicitly define a join table. So instead of having a column in the pets table that indicates the category of the pet, you'll have a separate table that maps category ids to pet ids. Then when you cascade the delete it will delete the entry from the join table. I believe it won't cascase down to the pets table thus leaving your pet uncategorized. You can define a unique contraint on the pet id column of the join table to make sure a pet is only categorized once.

2. I'm not sure how to do it, but you may be able to define the delete action so that it does a "on delete set null" constraint. Oracle supports this type of thing and I think many other databases do as well. I'm not sure how to do this in Hibernate's mapping files though. So if you are generating your schema from Hibernate you may have to code this by hand. Hopefully someone else may know how to set this up.

Good luck.

kochcp · **Posted:** Tue Jul 11, 2006 2:29 pm

createQuery("delete from XXXX where pk=:getPk()").executeUpdate();

for (getChildren)
child.setParent(null)

or, the quicker way through hql executing just one statement to the db
update child set parent='null' where fk=:parent.getPk()