Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1.1

Mapping documents:
I have persistent class Job

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class table="MOP_Job" name="se.powerforms.mop.core.customer.Job">
<id name="key" type="java.lang.Long" column="jobSN">
<generator class="native"/>
</id>
<set inverse="true" cascade="save-update" order-by="inputFileSN" name="inputfiles">
<key column="jobSN" on-delete="cascade"/>
<one-to-many class="se.powerforms.mop.core.customer.InputFile"/>
</set>
</class>
</hibernate-mapping>

I have persistent class InputFile, which connected with Job as many-to-one

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class table="MOP_InputFile" name="se.powerforms.mop.core.customer.InputFile">
<id name="key" type="java.lang.Long" column="inputFileSN">
<generator class="native"/>
</id>
<many-to-one column="jobSN" lazy="false" not-null="true" name="job"/>
<set inverse="true" cascade="save-update" order-by="itemSN" name="items">
<key column="inputFileSN" on-delete="cascade"/>
<one-to-many class="se.powerforms.mop.core.item.Item"/>
</set>
</class>
</hibernate-mapping>

I have persistent class Item, which connected with InputFile as many-to-one

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class dynamic-update="true" table="MOP_Item" batch-size="500" dynamic-insert="true" name="se.powerforms.mop.core.item.Item">
<id name="key" type="java.lang.Long" column="itemSN">
<generator class="se.powerforms.hibernate.MopGenerator"/>
</id>
<many-to-one column="itemStateSN" lazy="false" not-null="true" name="itemState"/>
<many-to-one column="inputFileSN" lazy="false" not-null="true" name="inputFile"/>
<many-to-one column="outputFileSN" lazy="false" not-null="false" name="outputFile"/>
<property name="pageNumber" length="11" column="pageNumber" not-null="true" type="long"/>
<property name="pageCount" length="11" column="pageCount" not-null="true" type="long"/>
<map inverse="true" cascade="persist,merge" name="attributeValues">
<key column="itemSN" on-delete="cascade"/>
<index-many-to-many column="attributeSN" class="se.powerforms.mop.core.customer.Attribute"/>
<one-to-many class="se.powerforms.mop.core.item.AttributeValue"/>
</map>
<set inverse="true" name="deliveryPartnerConfiguration2Item">
<key column="itemSN" on-delete="cascade"/>
<one-to-many class="se.powerforms.mop.core.mapping.DeliveryPartnerConfiguration2Item"/>
</set>
<set inverse="true" name="separator2Item">
<key column="itemSN" on-delete="cascade"/>
<one-to-many class="se.powerforms.mop.core.mapping.Separator2Item"/>
</set>
<set inverse="true" name="overlay2Item">
<key column="itemSN" on-delete="cascade"/>
<one-to-many class="se.powerforms.mop.core.mapping.Overlay2Item"/>
</set>
<property name="sequenceNumber" length="11" column="sequenceNumber" not-null="false" type="integer"/>
<property name="streamName" length="255" column="streamName" not-null="false" type="string"/>
<property name="jobSN" length="11" column="jobSN" not-null="false" type="long"/>
<property name="deliveryPartnerConfigurationSN" length="11" column="deliveryPartnerConfigurationSN" not-null="false" type="long"/>
<set inverse="true" name="addresses">
<key column="itemSN" on-delete="cascade"/>
<one-to-many class="se.powerforms.mop.core.item.Address"/>
</set>
</class>
<sql-query name="getItems"><![CDATA[select item from Item item where item.itemState.key = :key and item.deliveryPartnerConfiguration.key = :dpc and item.job.key = :job]]></sql-query>
</hibernate-mapping>

I have persistent class LightItem, which I use for batch insert and update(LightItem versus Item have a few count of objects compared to Item, which needed to create. In our system we need to create and update a huge amount of items so we using LightItem)

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class dynamic-update="true" table="MOP_Item" batch-size="500" dynamic-insert="true" name="se.powerforms.mop.core.item.LightItem">
<id name="key" type="java.lang.Long" column="itemSN">
<generator class="se.powerforms.hibernate.MopGenerator"/>
</id>
<many-to-one column="itemStateSN" lazy="false" not-null="true" name="itemState"/>
<property name="jobSN" length="11" column="jobSN" not-null="false" type="long"/>
<set inverse="true" name="lightAttributeValues">
<key column="itemSN" on-delete="cascade"/>
<one-to-many class="se.powerforms.mop.core.item.LightAttributeValue"/>
</set>
<set inverse="true" name="lightDeliveryPartnerConfiguration2Items">
<key column="itemSN" on-delete="cascade"/>
<one-to-many class="se.powerforms.mop.core.mapping.LightDeliveryPartnerConfiguration2Item"/>
</set>
</class>
</hibernate-mapping>

I have persistent class AttributeValue. This class is needed for work system with Item.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class mutable="false" table="MOP_AttributeValue" batch-size="1000" dynamic-insert="true" name="se.powerforms.mop.core.item.AttributeValue">
<id name="key" type="java.lang.Long" column="attributeValueSN">
<generator class="se.powerforms.hibernate.MopGenerator"/>
</id>
<many-to-one lazy="false" name="item">
<column name="itemSN" unique-key="UK_AttributeValue"/>
</many-to-one>
<many-to-one lazy="false" name="attribute">
<column name="attributeSN" unique-key="UK_AttributeValue" not-null="false"/>
</many-to-one>
<property name="value" length="255" column="value" not-null="false" type="string"/>
</class>
</hibernate-mapping>

Besides, I have persistent class LightAttributeValue :).This class is needed for insert a huge count of records in database.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class mutable="false" table="MOP_AttributeValue" batch-size="1000" dynamic-insert="true" name="se.powerforms.mop.core.item.LightAttributeValue">
<id name="key" type="java.lang.Long" column="attributeValueSN">
<generator class="se.powerforms.hibernate.MopGenerator"/>
</id>
<many-to-one lazy="false" name="item">
<column name="itemSN" unique-key="UK_AttributeValue"/>
</many-to-one>
<many-to-one lazy="false" name="attribute">
<column name="attributeSN" unique-key="UK_AttributeValue" not-null="false"/>
</many-to-one>
<property name="value" length="255" column="value" not-null="false" type="string"/>
</class>
</hibernate-mapping>

Both classes (Item and LightItem have 2 constraint on delete AttributeValue and LightAttributeValue, which related on one! table)


Name and version of the database you are using: MSSQL 2000, MySQL 5.0, HSQLDB 1.7.3

Code between sessionFactory.openSession() and session.close():
I need to delete job
Job job;
hibernateTemplate.delete(job)

Process deleting of Job working correctly, but we have one problem.
Script, which hibernate generated for MSSQl contains 2 constraint for deleting one table. if I removed cascade for AttributeValue in LightItem, for example, deleting of job didn't work.

alter table MOP_AttributeValue add constraint FK29254DC675DC1D83 foreign key (itemSN) references MOP_Item on delete cascade;
alter table MOP_AttributeValue add constraint FK29254DC6AEA820B5 foreign key (itemSN) references MOP_Item on delete cascade;

MSSQL don't want to work with this script, but scripts which were generated for MSSQL and HSQLDB work correctly.

Error on MSSQL:
Server: Msg 1785, Level 16, State 1, Line 71
Introducing FOREIGN KEY constraint 'FK29254DC6AEA820B5' on table 'MOP_AttributeValue' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 71
Could not create constraint. See previous errors.

Question: Why did I need to use cascade for delete in Light Item if I wanted to delete Item but not LightItem from InputFile?

Do you know some solving of this problem?
[/code]