Problems with performing cascade deletes

mkarami · **Posted:** Wed May 28, 2008 3:33 am

I am having a problem performing a delete operation using hibernate and was hoping someone could help me out here.
I am using Hibernate and Spring and have a table in the database based on the following configurations:

Spring-based tranaction manager:

Code:

<bean id="myTxManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
      <property name="sessionFactory" ref="sessionFactory"/>
   </bean>

Hibernate definition:

Code:

[b]<hibernate-mapping>
   <class name="com.domain.EmployeeUser" table="employee_domain">
      <id name="userID" column="user_id" length="15"/> 
      <many-to-one name="domain" column="domain_id"/>
   </class>
</hibernate-mapping>
<hibernate-mapping>
   <class name="com.domain.Domain" table="domain">
        <id name="domain_id" type="string" column="domain_id" length="10"/>
        <property name="description" type="string" column="description" not-null="true" length="40"/>
        <set name="domainCountries" inverse="true" fetch="subselect" lazy="false" cascade="all, delete-orphan">
      <key>
                <column name="domain_id" not-null="true" />
            </key>
         <one-to-many class="com.domain.DomainCountry"/>
      </set>
   </class>
</hibernate-mapping>
<hibernate-mapping>
   <class name="com.domain.DomainCountry" table="domain_country">      
          <composite-id>
          <key-many-to-one name="domain" column="domain_id"/>
          <key-property name="country" type="string" column="country"  length="2"/>
         </composite-id>
   </class>
</hibernate-mapping>[/b]

Code that takes care of delete action:

Code:

public void delete(EmployeeUser user) {
        getHibernateTemplate().delete(user);
}

Code that takes care of add/update action:

Code:

public void delete(EmployeeUser user) {
        getHibernateTemplate().saveOrUpdate(user);
}

What basically happens is that users are able to select a domain in user interface and can assign it to themselves. So from a list of valid domain id's users can select one domain, and this domain is assigned to users and stored in the database table "employee_domain". They can also choose to delete the domain id by deselecting the domain for this user. In such case the delete method is called to remove the entry from the table employee_domain.
Following describes the situation I am puzzled with:

1- If user already has a domain assigned to him (there is already an entry in the table employee_domain), removing the domain calls the delete method which results in calling "getHibernateTemplate().delete(user)" and removing the entry form the table. following queries are performed:

Code:

select
        employeeus_.user_id,
        employeeus_.domain_id as domain2_19_ 
    from
        employee_domain employeeus_ 
    where
        employeeus_.user_id=?

delete 
    from
        employee_domain
    where
        user_id=?

Assiging a domain to user calls the "getHibernateTemplate().saveOrUpdate(user)"
Everything is fine, selecting and de-selecting works fines and table is updated properly.

2- If the user has no entry in the table, e.g. this is the first time user is being assigned a domain, selecting a domain calls "getHibernateTemplate().saveOrUpdate(user)". A new entry is added in the table with user id and domain id. If after this action user deselects the domain, e.g. decide to remove the domain for this user, "getHibernateTemplate().delete(user)" is called. But instead of delete being called in hibernate, an update operation is attempted to be executed with the following statements:

Code:

select
        employeeus_.user_id,
        employeeus_.domain_id as domain2_19_ 
    from
        employee_domain employeeus_ 
    where
        employeeus_.user_id=?
2008-05-27 15:36:54,491 [WebContainer : 0] DEBUG SQL - 
    select
        domain_.domain_id,
        domain_.description as descript2_14_ 
    from
        domain domain_ 
    where
        domain_.domain_id=?
2008-05-27 15:36:54,507 [WebContainer : 0] DEBUG SQL - 
    update
        employee_domain 
    set
        domain_id=? 
    where
        user_id=?

It seems like instead of delete, hibernate calls update statement trying to update the table entry with a NULL value, and following exception is thrown:

2008-05-27 15:36:54,585 [WebContainer : 0] DEBUG SdcmPortlet - Caught exception during action phase - forwarding to render phase
org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
Caused by:
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:562)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:654)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:624)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:307)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:117)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
at $Proxy83.delete(Unknown Source)
at com.mvc.employeesettings.EmployeeSettingsController.onSubmitAction(EmployeeSettingsController.java:77)
at org.springframework.web.portlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:322)
at org.springframework.web.portlet.mvc.AbstractFormController.handleActionRequestInternal(AbstractFormController.java:389)
at com.mvc.BaseFormController.handleActionRequestInternal(BaseFormController.java:79)
at org.springframework.web.portlet.mvc.AbstractController.handleActionRequest(AbstractController.java:197)
at org.springframework.web.portlet.mvc.SimpleControllerHandlerAdapter.handleAction(SimpleControllerHandlerAdapter.java:46)
at org.springframework.web.portlet.DispatcherPortlet.doActionService(DispatcherPortlet.java:638)
at com.portlet.SdcmPortlet.doActionService(SdcmPortlet.java:62)
........
Caused by:
java.sql.BatchUpdateException: ORA-01407: cannot update ("SDCM"."EMPLOYEE_DOMAIN"."DOMAIN_ID") to NULL

at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:459)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:4373)
at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecuteBatch(WSJdbcStatement.java:1173
........

Strange thing is that the same code is called in both cases. It seems like hibernate decide to do an update instead of delete even though I am calling the delete method.
Does anyone know how hibernate figures out to call update instead of delete in the second scenario? and how can I make sure delete is called and not update?

Cheers,
devx

farzad · **Posted:** Wed May 28, 2008 1:25 pm

Try evicting it before running a delete. I suspect you have a modified version of user object in session memory which is flushed before a delete command is called. Otherwise, you are probably calling the wrong method. Did you actually debug the code to make sure HibernateTemplate.delete is being called?

Farzad-

mkarami · **Posted:** Thu May 29, 2008 2:58 am

Hi,
I debugged the code and HibernateTemplate.delete is actually called. By evict do you mean saving the object in session? I tried to save the object before doing the delete operation and still no results.

devx

mkarami · **Posted:** Thu May 29, 2008 2:59 am

Hi,
I debugged the code and HibernateTemplate.delete is actually called. By evict do you mean saving the object in session? I tried to save the object before doing the delete operation and still no results.
gr, devx

Yoann56 · **Joined:** Thu Nov 15, 2007 11:27 am **Posts:** 34

Have you check the values of 'EmployeeUser user'? Check if user_id and domain_id got the expected values. If not the problem may comes from a unexpecting modification of persistent objects.

Regards,
Y.

kukudas · **Joined:** Wed Apr 09, 2008 10:28 am **Posts:** 52

I think he means with evicting - detaching the Object from the Hibernate Session.

mkarami · **Posted:** Thu May 29, 2008 4:16 am

I have already checked the values of user and the values passed for user_id and domain_id are the expected values.
My guess is that the user object being modified from the last operation (assigning a domain to user) is still in the session memory, even though the changes from the add operation are already committed into the database table.
My assumption was that once the modifications are committed into the database table the user object will be flushed and the nest operation takes a new transaction to perform.
I tried to create a custom HibernateCallback and use hibernateTamplate.execute(callbackinstance) instead of using the HibernateTemplate.delete() operation, but got an DML operation not supported error.

Code:

public Object doInHibernate(Session session) {

            StringBuffer whereClause = new StringBuffer();

            // Perfrom the following query : "delete from employee_domain
            // where user_id=?"
            if (userID != null) {

                whereClause.append(" where ");
                whereClause.append(" e.userID =" + USER_ID + " ");

                String hsql = "delete from EmployeeUser e " + whereClause;
                log.debug("hsql query: " + hsql);
                Query query = session.createQuery(hsql);
                String[] namedParameters = query.getNamedParameters();
                for (int i = 0; i < namedParameters.length; i++) {
                    String namedParameter = namedParameters[i];
                    if (namedParameter.equals(USER_ID)) {
                        query.setString(USER_ID, this.userID);
                    }
                }
                log.debug("query string: " + query.getQueryString());
                return query.list();
            } else {
                throw new ApplicationException("No user id has been specified");
            }
        }

What seems strange to me is that I do not encounter this problem if there is already an entry in the table for the user when I start the application (see first posting), but only when in the start state, no entires are in the table.[/code]

Cameron McKenzie · **Posted:** Mon Jun 09, 2008 9:42 am

Well, it looks like you might be trying to delete a record that does not exist. I wonder what would happen if you committed after the save you said you call, started a new transaction, and then deleted. If that works, you might want to rework the way your interacting with your database. You may actually be deleting a record that doesn't exist yet.