| [Teneo/Hibernate] Delete many objects from database at once [message #121625] |
Thu, 01 May 2008 09:02  |
Eclipse User |
|
|
|
Originally posted by: schlamp.medihack.org
Hy. This is more of a hibernate HQL problem, than a Teneo problem. But I
hope that someone perhaps has an answer here, as the Hibernate forum
wasn't too informative.
I have an EMF class named "Document", that has an attribute "properties"
of type "StringToStringMap". This model is mapped with hbm2ddl to my
database, so that there is a table for "Document" and one for
"properties". The last one has a "Document" id as foreign id. (For more
details please see Hibernate mapping file below.)
Now I want to delete a large number of objects at once. I have their id's
for that task. With normal SQL this would be no problem at all. With HQL
it isn't that easy (but I like to use that cause of database independency).
Deleting each object one by one (with session.delete()) is not an option,
as it takes far too long (I have to sequentially fetch each object by id
and then delete it).
My other thought was to use bulk delete (see
http://www.hibernate.org/hib_docs/v3/reference/en/html/batch .html#batch-direct
... delete Document as doc where doc.id = 33, or something like that), but
it fails cause of a foreign key exception as it doesn't handle the
dependent properties table.
So it seems that bulk delete doesn't use the 'cascade="all,delete-orphan"'
and so doesn't delete cascading.
Is there another option do delete many objects at once?
Kai
extract from the hibernate mapping file:
<class name="org.pubcurator.documentor.documentmodel.impl.DocumentImpl "
entity-name="Document" abstract="false" lazy="false"
discriminator-value="Document" table="`document`">
<meta attribute="eclassName">Document</meta>
<meta
attribute="epackage">http://www.pubcurator.org/documentor/documentmodel</meta>
<id name="id" type="long" unsaved-value="0">
<column not-null="true" unique="false" name="`id`"/>
<generator class="native"/>
</id>
<discriminator column="`dtype`" type="string"/>
<version name="e_version" column="e_version"
access=" org.eclipse.emf.teneo.hibernate.mapping.property.VersionProp ertyHandler ">
<meta attribute="syntheticVersion">true</meta>
</version>
<map name="properties" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="`document_properties_id`" not-null="false"
unique="false"/>
</key>
<map-key type="java.lang.String"/>
<element type="java.lang.String" not-null="false" unique="false">
<column not-null="false" unique="false" name="`value`" length="10000"/>
</element>
</map>
</class>
|
|
|
| Re: [Teneo/Hibernate] Delete many objects from database at once [message #121707 is a reply to message #121625] |
Fri, 02 May 2008 01:28  |
| Eclipse User |
|
|
|
Hi Kai,
I can't think of anything else then first bulk-delete the properties elements and then the documents
themselves. But I am not sure if you can delete the map entries using a hql statement...
Regarding direct sql, if the sql statement is simple enough then it should be portable across
different databases (ofcourse assuming that table/column names are the same...).
gr. Martin
Kai Schlamp wrote:
> Hy. This is more of a hibernate HQL problem, than a Teneo problem. But I
> hope that someone perhaps has an answer here, as the Hibernate forum
> wasn't too informative.
>
> I have an EMF class named "Document", that has an attribute "properties"
> of type "StringToStringMap". This model is mapped with hbm2ddl to my
> database, so that there is a table for "Document" and one for
> "properties". The last one has a "Document" id as foreign id. (For more
> details please see Hibernate mapping file below.)
> Now I want to delete a large number of objects at once. I have their
> id's for that task. With normal SQL this would be no problem at all.
> With HQL it isn't that easy (but I like to use that cause of database
> independency).
> Deleting each object one by one (with session.delete()) is not an
> option, as it takes far too long (I have to sequentially fetch each
> object by id and then delete it).
> My other thought was to use bulk delete (see
> http://www.hibernate.org/hib_docs/v3/reference/en/html/batch .html#batch-direct
> ... delete Document as doc where doc.id = 33, or something like that),
> but it fails cause of a foreign key exception as it doesn't handle the
> dependent properties table.
> So it seems that bulk delete doesn't use the
> 'cascade="all,delete-orphan"' and so doesn't delete cascading.
>
> Is there another option do delete many objects at once?
>
> Kai
>
> extract from the hibernate mapping file:
>
> <class name="org.pubcurator.documentor.documentmodel.impl.DocumentImpl "
> entity-name="Document" abstract="false" lazy="false"
> discriminator-value="Document" table="`document`">
> <meta attribute="eclassName">Document</meta>
> <meta
> attribute="epackage">http://www.pubcurator.org/documentor/documentmodel</meta>
>
> <id name="id" type="long" unsaved-value="0">
> <column not-null="true" unique="false" name="`id`"/>
> <generator class="native"/>
> </id>
> <discriminator column="`dtype`" type="string"/>
> <version name="e_version" column="e_version"
> access=" org.eclipse.emf.teneo.hibernate.mapping.property.VersionProp ertyHandler ">
>
> <meta attribute="syntheticVersion">true</meta>
> </version>
> <map name="properties" lazy="true" cascade="all,delete-orphan">
> <key update="true">
> <column name="`document_properties_id`" not-null="false"
> unique="false"/>
> </key>
> <map-key type="java.lang.String"/>
> <element type="java.lang.String" not-null="false"
> unique="false">
> <column not-null="false" unique="false" name="`value`"
> length="10000"/>
> </element>
> </map>
> </class>
>
--
With Regards, Martin Taal
Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org
|
|
|
| Re: [Teneo/Hibernate] Delete many objects from database at once [message #617981 is a reply to message #121625] |
Fri, 02 May 2008 01:28 |
| Eclipse User |
|
|
|
Hi Kai,
I can't think of anything else then first bulk-delete the properties elements and then the documents
themselves. But I am not sure if you can delete the map entries using a hql statement...
Regarding direct sql, if the sql statement is simple enough then it should be portable across
different databases (ofcourse assuming that table/column names are the same...).
gr. Martin
Kai Schlamp wrote:
> Hy. This is more of a hibernate HQL problem, than a Teneo problem. But I
> hope that someone perhaps has an answer here, as the Hibernate forum
> wasn't too informative.
>
> I have an EMF class named "Document", that has an attribute "properties"
> of type "StringToStringMap". This model is mapped with hbm2ddl to my
> database, so that there is a table for "Document" and one for
> "properties". The last one has a "Document" id as foreign id. (For more
> details please see Hibernate mapping file below.)
> Now I want to delete a large number of objects at once. I have their
> id's for that task. With normal SQL this would be no problem at all.
> With HQL it isn't that easy (but I like to use that cause of database
> independency).
> Deleting each object one by one (with session.delete()) is not an
> option, as it takes far too long (I have to sequentially fetch each
> object by id and then delete it).
> My other thought was to use bulk delete (see
> http://www.hibernate.org/hib_docs/v3/reference/en/html/batch .html#batch-direct
> ... delete Document as doc where doc.id = 33, or something like that),
> but it fails cause of a foreign key exception as it doesn't handle the
> dependent properties table.
> So it seems that bulk delete doesn't use the
> 'cascade="all,delete-orphan"' and so doesn't delete cascading.
>
> Is there another option do delete many objects at once?
>
> Kai
>
> extract from the hibernate mapping file:
>
> <class name="org.pubcurator.documentor.documentmodel.impl.DocumentImpl "
> entity-name="Document" abstract="false" lazy="false"
> discriminator-value="Document" table="`document`">
> <meta attribute="eclassName">Document</meta>
> <meta
> attribute="epackage">http://www.pubcurator.org/documentor/documentmodel</meta>
>
> <id name="id" type="long" unsaved-value="0">
> <column not-null="true" unique="false" name="`id`"/>
> <generator class="native"/>
> </id>
> <discriminator column="`dtype`" type="string"/>
> <version name="e_version" column="e_version"
> access=" org.eclipse.emf.teneo.hibernate.mapping.property.VersionProp ertyHandler ">
>
> <meta attribute="syntheticVersion">true</meta>
> </version>
> <map name="properties" lazy="true" cascade="all,delete-orphan">
> <key update="true">
> <column name="`document_properties_id`" not-null="false"
> unique="false"/>
> </key>
> <map-key type="java.lang.String"/>
> <element type="java.lang.String" not-null="false"
> unique="false">
> <column not-null="false" unique="false" name="`value`"
> length="10000"/>
> </element>
> </map>
> </class>
>
--
With Regards, Martin Taal
Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@springsite.com - mtaal@elver.org
Web: www.springsite.com - www.elver.org
|
|
|
Powered by
FUDForum. Page generated in 0.15960 seconds
Home