Exception in Deleting Parent and Child entity

[tarun adepu]

Hi All,
I am not able to delete the parent and child entity. The details are given below.

User.hbm.file
<hibernate-mapping>
<class name="com.hibernate.pojo.User_Bank" table="USER_BANK">
<id name="id" column="ID" type="string">
<generator class="assigned"></generator>
</id>
<property name="name" column="name" type="string" />
<component name="address" class="com.hibernate.pojo.Address">
<property name="city" type="string" column="CITY" />
<property name="State" type="string" column="STATE" />
<property name="zip" type="int" column="ZIP" />
</component>

<set name="bank_details" inverse="true" cascade="all" lazy="true">
<key column="user_id" on-delete="cascade" not-null="true"/>
<one-to-many class="com.hibernate.pojo.Bank_Details"/>
</set>

</class>
</hibernate-mapping>

Bank_Details.hbm.xml

<hibernate-mapping package="com.hibernate.pojo">

<class name="com.hibernate.pojo.Bank_Details" table="BANK_DETAILS">
<id name="id">
<generator class="assigned" />
</id>
<property name="bank_name" column="BANK_NAME" type="string" />
<many-to-one name="user" column="USER_ID" class="User_Bank" cascade="all"/>
<joined-subclass table="BANK_ACCOUNT" name="com.hibernate.pojo.Bank_account">
<key column="ID" />
<property name="account_type" column="AC_TYPE" />
</joined-subclass>
<joined-subclass name="com.hibernate.pojo.Creditcard_Account" table="CREDITCARD_ACCOUNT">
<key column="ID"/>
<property name="valid_from" column="VALID_FROM" type="date"/>
<property name="valid_thru" column="VALID_THRU"/>
</joined-subclass>


</class>
</hibernate-mapping>

Deleting operation
User_Bank user = (User_Bank)session.load(User_Bank.class, string);
session.delete(user);

The excepton I got:
Hibernate: delete from USER_BANK where ID=?
Got Excep...Could not execute JDBC batch update
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:161)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:669)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:293)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
at com.hibernate.util.Hibernate_Main.main(Hibernate_Main.java:53)
Caused by: java.sql.BatchUpdateException: integrity constraint violation: foreign key no action; USER_ID_FK table: BANK_DETAILS
at org.hsqldb.jdbc.JDBCPreparedStatement.executeBatch(JDBCPreparedStatement.java:1924)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:154)
... 8 more

Please help me in solving this

[Ramon Anger]

Hi Tarun,

what happens if you try to execute the delete operation using a native sql command?

Regards,
Ramon

[tarun adepu]

hi Ramon,
thanks for the reply. I tried the sql query on db. I got the same exception. Problem is we can not delete parent record first. So the code which I wrote is not able to delete the child records first. I dont understand where it is going wrong.

[Ramon Anger]

Hi Tarun,

I've found a thread in another forum. http://stackoverflow.com/questions/3627674/deleting-jpa-object-fails-due-to-foreign-key-constraints seems to be connected to the problem you actually face.

May be the only way is to iterate over each record of your parent table you want to delete and than delete each corresponding child record before.

BTW, what database do you use?

Cheers,
Ramon

[tarun adepu]

Hi Ramon,

I am using hsqldb. So you mean to say hibernate can not delete collection entities before deleting parent. What does cascade="delete" means?


Thanks
Tarun

[Ramon Anger]

Hi Tarun,

you are right. on-detete=cascade sould delete dependent child records automatically.
There is an interesting link that describes how on-delete=cascade should work: http://eddii.wordpress.com/2006/11/16/hibernate-on-deletecascade-performance/
Nevertheless there are sometimes problems using on-delete=cascade when deleting more than one record.

Let's go the other way around:
-Can you ensure that your ID parameter is set properly when Hibernate is running the delete statement?
delete from USER_BANK where ID=?

-Can you ansure that BANK_DETAILS has no other relations to other tables that could prevent deletion of records?

-Is there a way to increase the logging level to have more information about what' going wrong?

-Is there a constraint definition in your USER_BANK table that handles cascade delete?

Regards,
Ramon

[tarun adepu]

Hi Ramon,

Sorry I forgot to tell BANK_DETAILS has relations to other tables.I implemented table per subclass relation for this table. Please find the db scripts below.

CREATE MEMORY TABLE PUBLIC.USER_BANK(ID VARCHAR(10),NAME VARCHAR(10),CITY VARCHAR(10),STATE VARCHAR(10),ZIP NUMERIC(10),CONSTRAINT ID_PK PRIMARY KEY(ID))

CREATE MEMORY TABLE PUBLIC.BANK_DETAILS(ID VARCHAR(10),BANK_NAME VARCHAR(10),USER_ID VARCHAR(10),CONSTRAINT ACCOUNT_ID_PK PRIMARY KEY(ID),CONSTRAINT USER_ID_FK FOREIGN KEY(USER_ID) REFERENCES PUBLIC.USER_BANK(ID))

CREATE MEMORY TABLE PUBLIC.CREDITCARD_ACCOUNT(ID VARCHAR(10),VALID_FROM DATE,VALID_THRU DATE,CONSTRAINT CREDIT_ID_PK PRIMARY KEY(ID),CONSTRAINT CREDIT_ID_FK FOREIGN KEY(ID) REFERENCES PUBLIC.BANK_DETAILS(ID))

CREATE MEMORY TABLE PUBLIC.BANK_ACCOUNT(ID VARCHAR(10) PRIMARY KEY,AC_TYPE VARCHAR(10),CONSTRAINT BANK_ID_FK FOREIGN KEY(ID) REFERENCES PUBLIC.BANK_DETAILS(ID))

CREDITCARD_ACCOUNT and BANK_ACCOUNT represent sub entities.

I paased the corect ID to the hibernate query.
I didnt specify constraint definition while creating the tables. Is this the reason why hibernate is not able to cascade delete