[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-2098) Problem deleting entities in hierarchy
Diego Pires Plentz (JIRA)
noreply at atlassian.com
Sat Oct 13 01:56:39 EDT 2007
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2098?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Diego Pires Plentz resolved HHH-2098.
-------------------------------------
Assignee: Diego Pires Plentz
Resolution: Duplicate
> Problem deleting entities in hierarchy
> --------------------------------------
>
> Key: HHH-2098
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2098
> Project: Hibernate3
> Issue Type: Bug
> Components: query-hql
> Affects Versions: 3.2.0.cr3
> Environment: Hibernate 3.2.0CR3 and Hibernate 3.2.0CR4, on an Oracle DB
> Reporter: Philippe Larouche
> Assignee: Diego Pires Plentz
> Priority: Minor
> Attachments: hhh-2098.zip
>
>
> Hi, I'm using Hibernate 3.2.0 CR3 (also tried it with 3.2.0CR4) and when I try to delete entities in batch (with an HQL query), I got an error in the SQL translation of my query. I try to delete an entity that is the child of another one. Here are my mapping files:
> Scancode (parent entity)......
> <?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="com.cardinal.dal.entity.foundation.ScanCode" table="SCAN_CD_VW" schema="CDM_FND_1_0">
> <composite-id name="id" class="com.cardinal.dal.entity.generated.foundation.ScanCodeId">
> <key-property name="scanCdGuid" type="binary">
> <meta attribute="use-in-equals" inherit="false">true</meta>
> <column name="SCAN_CD_GUID"/>
> </key-property>
> <key-property name="operatingCntxGuid" type="binary">
> <meta attribute="use-in-equals" inherit="false">true</meta>
> <column name="OPERATING_CNTX_GUID"/>
> </key-property>
> </composite-id>
> <version name="versionNum" column="VERSION_NUM" generated="always" unsaved-value="negative" insert="false"/>
> <property name="dataDomainGuid" type="binary">
> <column name="DATA_DOMAIN_GUID" not-null="true"/>
> </property>
> <property name="scanCdCls" type="string">
> <column name="SCAN_CD_CLS" length="1" not-null="true"/>
> </property>
> <property name="scanCdTxt" type="string">
> <column name="SCAN_CD_TXT" not-null="true"/>
> </property>
> <filter name="operatingCntxFilter" condition=":operatingCntxGuid = OPERATING_CNTX_GUID"/>
> </class>
> </hibernate-mapping>
> PatientScancode (child entity).............
> <?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>
> <joined-subclass name="com.cardinal.dal.entity.patient.PatientScanCode" extends="com.cardinal.dal.entity.foundation.ScanCode" table="PATIENT_SCAN_CD_VW" schema="CDM_PAT_1_0">
> <key>
> <column name="PATIENT_SCAN_CD_GUID"/>
> <column name="OPERATING_CNTX_GUID"/>
> </key>
> <property name="patientGuid" type="binary">
> <meta attribute="scope-get" inherit="false">protected</meta>
> <meta attribute="scope-set" inherit="false">protected</meta>
> <column name="PATIENT_GUID"/>
> </property>
> <many-to-one name="patientVw" class="com.cardinal.dal.entity.patient.Patient" update="false" insert="false" fetch="select">
> <column name="PATIENT_GUID" not-null="true"/>
> <column name="OPERATING_CNTX_GUID"/>
> </many-to-one>
> </joined-subclass>
> </hibernate-mapping>
> Here's how I execute my delete statement in my session bean:
> String queryStr = "delete from PatientScanCode as scancode " +
> "where scancode.patientVw.id.partyGuid = ? ";
> Query query = session.createQuery(queryStr);
> query.setParameter(0, patientId.getPartyGuid());
> query.executeUpdate();
> I know that the query syntax is correct, the problem comes from the SQL translation made by Hibernate, here's what it looks like:
> delete
> from
> CDM_PAT_1_0.PATIENT_SCAN_CD_VW
> where
> (
> PATIENT_SCAN_CD_GUID, OPERATING_CNTX_GUID
> ) IN (
> select
> PATIENT_SCAN_CD_GUID,
> OPERATING_CNTX_GUID
> from
> HT_PATIENT_SCAN_CD_VW
> )
> delete
> from
> CDM_FND_1_0.SCAN_CD_VW
> where
> (
> SCAN_CD_GUID, OPERATING_CNTX_GUID
> ) IN (
> select
> PATIENT_SCAN_CD_GUID,
> OPERATING_CNTX_GUID
> from
> HT_PATIENT_SCAN_CD_VW
> )
> There are 2 delete statements since it has to delete both the child and the parent. In the from clause in the select statement, the table called HT_PATIENT_SCAN_CD_VW is used, but it doesn't exist in our model. I suppose it should be a temporary table created by hibernate to keep in memory which rows have to be deleted when it's time to delete the parent table. The problem is that this table doesn't seem to be created properly before running delete statements cause I get the following exception when it is called on the database:
> 19:04:06,001 ERROR [JDBCExceptionReporter] ORA-00918: column ambiguously defined
> 19:04:06,017 WARN [MultiTableDeleteExecutor] unable to cleanup temporary id table after use
> java.sql.SQLException: ORA-00942: table or view does not exist
> at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
> at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
> at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
> at org.hibernate.hql.ast.exec.AbstractStatementExecutor.dropTemporaryTableIfNecessary(AbstractStatementExecutor.java:179)
> at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:136)
> at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:391)
> at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
> at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1134)
> at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
> at com.cardinal.dal.session.patient.PatientScanCodeBean.deleteAllByPatient(PatientScanCodeBean.java:75)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
> at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
> at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.tx.BMTInterceptor.handleStateless(BMTInterceptor.java:71)
> at org.jboss.ejb3.tx.BMTInterceptor.invoke(BMTInterceptor.java:131)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:181)
> at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:79)
> at $Proxy866.deleteAllByPatient(Unknown Source)
> at testng.com.cardinal.dal.session.patient.PatientScanCodeTestBean.deleteAllByPatient(PatientScanCodeTestBean.java:101)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
> at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
> at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.tx.BMTInterceptor.handleStateless(BMTInterceptor.java:71)
> at org.jboss.ejb3.tx.BMTInterceptor.invoke(BMTInterceptor.java:131)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:225)
> at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:106)
> at org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
> at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:828)
> at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:681)
> at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:358)
> at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:412)
> at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:239)
> 19:04:06,095 ERROR [PatientScanCodeBean] Error in com.cardinal.dal.session.patient.PatientScanCodeBean.invoke0(): org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk delete
> org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk delete
> at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
> at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:102)
> at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:391)
> at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
> at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1134)
> at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
> at com.cardinal.dal.session.patient.PatientScanCodeBean.deleteAllByPatient(PatientScanCodeBean.java:75)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
> at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
> at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.tx.BMTInterceptor.handleStateless(BMTInterceptor.java:71)
> at org.jboss.ejb3.tx.BMTInterceptor.invoke(BMTInterceptor.java:131)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:181)
> at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:79)
> at $Proxy866.deleteAllByPatient(Unknown Source)
> at testng.com.cardinal.dal.session.patient.PatientScanCodeTestBean.deleteAllByPatient(PatientScanCodeTestBean.java:101)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
> at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
> at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.tx.BMTInterceptor.handleStateless(BMTInterceptor.java:71)
> at org.jboss.ejb3.tx.BMTInterceptor.invoke(BMTInterceptor.java:131)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
> at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
> at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:225)
> at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:106)
> at org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
> at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:828)
> at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:681)
> at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:358)
> at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:412)
> at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:239)
> Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined
> at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
> at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
> at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
> at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:93)
> ... 67 more
> Thx to keep me posted on this one
> Phil
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list