Here we go again...

Hi,

could it be that Hibernate does not support many-to-many mappings of the following type?

I have a n-to-m relationship of objects of classes A and B. A has the following composite PK: pk1, pk2. B has the composite PK pk1, pk2, pk3. The db table for the n-to-m mapping contains pk1, pk2, pk3. So a specific combination pk1fix, pk2fix (an individual A entity) maps to corresponding combinations pk1fix, pk2fix, pk3var (the corresponding B entities).

WIth the following mapping I always get a Hibernate mapping exception. Am I correct that Hibernate does not support this type of composite PKs? (Though that would be sad, 'cause I would like to switch from JDO to Hibernate, if we don't have to change our whole DB schema.)

Mapping for A:
<set name="bs" table="AB_MAPPING" lazy="true" inverse="true">
<key>
<column name="pk1"/>
<column name="pk2"/>
</key>
<many-to-many class="B">
<column name="pk1"/>
<column name="pk2"/>
<column name="pk3"/>
</many-to-many>
</set>

Mapping for B:
<set name="as" table="AB_MAPPING" lazy="true">
<key>
<column name="pk1"/>
<column name="pk2"/>
<column name="pk3"/>
</key>
<many-to-many class="A">
<column name="pk1"/>
<column name="pk2"/>
</many-to-many>
</set>



Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I don't quite follow. How can this have many-to-many multiplicity? How could you possibly have two As for a particular B???

Please show the full table definitions, including the full PK declarations.

Oooops,

sorry, I was a bit quick with the "pk2"s. Attached you find the corrected version and a bit further down the "real" table definitions.

I have a n-to-m relationship of objects of classes A and B. A has the following composite PK: pk1, pk2. B has the composite PK pk1, pk3. The db table for the n-to-m mapping contains pk1, pk2, pk3. So a specific combination pk1fix, pk2fix (an individual A entity) maps to corresponding combinations pk1fix, pk3var (the corresponding B entities).

WIth the following mapping I always get a Hibernate mapping exception. Am I correct that Hibernate does not support this type of composite PKs? (Though that would be sad, 'cause I would like to switch from JDO to Hibernate, if we don't have to change our whole DB schema.)

Mapping for A:
<set name="bs" table="AB_MAPPING" lazy="true" inverse="true">
<key>
<column name="pk1"/>
<column name="pk2"/>
</key>
<many-to-many class="B">
<column name="pk1"/>
<column name="pk3"/>
</many-to-many>
</set>

Mapping for B:
<set name="as" table="AB_MAPPING" lazy="true">
<key>
<column name="pk1"/>
<column name="pk3"/>
</key>
<many-to-many class="A">
<column name="pk1"/>
<column name="pk2"/>
</many-to-many>
</set>

Here come the "real" definitions...
table for class Cell:
create table funzel
(
arb_lfdnr int not null,
ver_nr smallint not null,
nls_id char(1) not null,
sta_id char(5) not null,
sta_loc char(1) not null,
alt_nr int not null,
fan_lfdnr int not null,
zel_id char(1) not null,

zel_network_type char(1) null,
...
zel_kommentar varchar(255) null
)

alter table funzel
add constraint funzel_pk primary key clustered( arb_lfdnr, ver_nr, nls_id, sta_id, sta_loc, alt_nr, fan_lfdnr, zel_id )

table for class CellGroup:
create table funzgr
(
arb_lfdnr int not null,
ver_nr smallint not null,
zgr_id char(15) not null,

zgr_name varchar(36) null,
...
zgr_kommentar varchar(255) null
)

alter table funzgr
add constraint funzgr_pk primary key clustered( arb_lfdnr, ver_nr, zgr_id )

n-to-m mapping table:
create table funzgm
(
zgr_id char(15) not null,
arb_lfdnr int not null,
ver_nr smallint not null,
nls_id char(1) not null,
sta_id char(5) not null,
sta_loc char(1) not null,
alt_nr int not null,
fan_lfdnr int not null,
zel_id char(1) not null,

zgm_lfdnr int default 0
not null
)

alter table funzgm
add constraint funzgm_pk primary key clustered( zgr_id, arb_lfdnr, ver_nr, nls_id, sta_id, sta_loc, alt_nr, fan_lfdnr, zel_id )

mapping files:
<hibernate-mapping schema="dbo" package="de.vodafone.rnp.core.business">
<class name="Cell" table="funzel" proxy="Cell" lazy="true">
<composite-id>
<key-property name="workspaceId"/>
<key-property name="versionId"/>
<key-property name="regionId"/>
<key-property name="cellCompoundCode"/>
<key-property name="siteId"/>
<key-property name="alternativeId"/>
<key-property name="radioSystemId"/>
<key-property name="id"/>
</composite-id>
<property name="workspaceId" type="integer" column="arb_lfdnr"/>
<property name="versionId" type="integer" column="ver_nr"/>
<property name="regionId" type="character" column="nls_id"/>
<property name="cellCompoundCode" type="string" column="sta_id"/>
<property name="siteId" type="character" column="sta_loc"/>
<property name="alternativeId" type="integer" column="alt_nr"/>
<property name="radioSystemId" type="integer" column="fan_lfdnr"/>
<property name="id" type="character" column="zel_id"/>

<property name="networkSystemLabel" type="character" column="zel_freq_band"/>
...
<property name="pilotPower" type="double" column="zel_power_bcch"/>

<set name="cellgroups" table="funzgm" lazy="true">
<key>
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
<column name="nls_id"/>
<column name="sta_id"/>
<column name="sta_loc"/>
<column name="alt_nr"/>
<column name="fan_lfdnr"/>
<column name="zel_id"/>
</key>
<many-to-many class="CellGroup">
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
<column name="zgr_id"/>
</many-to-many>
</set>
<!--subclass name="de.vodafone.rnp.core.business.persistence.jdo.CellImpl"/-->
</class>
</hibernate-mapping>

<hibernate-mapping schema="dbo" package="de.vodafone.rnp.core.business">
<class name="CellGroup" table="funzgr" proxy="CellGroup" lazy="true">
<composite-id>
<key-property name="workspaceId"/>
<key-property name="versionId"/>
<key-property name="name"/>
</composite-id>
<property name="workspaceId" type="integer" column="arb_lfdnr" insert="false" update="false"/>
<property name="versionId" type="integer" column="ver_nr" insert="false" update="false"/>
<property name="name" type="string" column="zgr_id"/>

<set name="cells" table="funzgm" lazy="true" inverse="true">
<key>
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
<column name="zgr_id"/>
</key>
<many-to-many class="Cell">
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
<column name="nls_id"/>
<column name="sta_id"/>
<column name="sta_loc"/>
<column name="alt_nr"/>
<column name="fan_lfdnr"/>
<column name="zel_id"/>
</many-to-many>
</set>
<many-to-one name="version" class="Version">
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
</many-to-one>
<!--subclass name="de.vodafone.rnp.core.business.persistence.jdo.CellGroupImpl"/-->
</class>
</hibernate-mapping>

The exception I get is:
net.sf.hibernate.MappingException: Repeated column in mapping for collection: de.vodafone.rnp.core.business.Cell.cellgroups column: arb_lfdnr

please use code tag, your topic isn' easy to read ;(

Sure. Here comes the post with <code> tags:

Oh, I did some testing with modifications of the mapping table. It seems as if Hibernate does not support "shared foreign keys" in the n-to-m mapping tables. I changed the mapping table from
<code>
create table AB_MAPPING
(
pk1 char(15) not null,
pk2 int not null,
pk3 smallint not null,
)

alter table AB_MAPPING
add constraint AB_MAPPING_pk primary key clustered( pk1, pk2, pk3 )
</code>
to
<code>
create table AB_MAPPING
(
pk1_A char(15) not null,
pk1_B char(15) not null,
pk2 int not null,
pk3 smallint not null,
)

alter table AB_MAPPING
add constraint AB_MAPPING_pk primary key clustered( pk1_A, pk2_B, pk2, pk3 )
</code>
and now it works.

If I am right regarding this point: Are you planning to support "shared foreign keys" (shared columns in the mapping table for both sides of the many-to-many relationships) in the future releases?

Ok, here comes the original post...

I have a n-to-m relationship of objects of classes A and B. A has the following composite PK: pk1, pk2. B has the composite PK pk1, pk3. The db table for the n-to-m mapping contains pk1, pk2, pk3. So a specific combination pk1fix, pk2fix (an individual A entity) maps to corresponding combinations pk1fix, pk3var (the corresponding B entities).

WIth the following mapping I always get a Hibernate mapping exception. Am I correct that Hibernate does not support this type of composite PKs? (Though that would be sad, 'cause I would like to switch from JDO to Hibernate, if we don't have to change our whole DB schema.)

Mapping for A:

<code>
<set name="bs" table="AB_MAPPING" lazy="true" inverse="true">
<key>
<column name="pk1"/>
<column name="pk2"/>
</key>
<many-to-many class="B">
<column name="pk1"/>
<column name="pk3"/>
</many-to-many>
</set>
</code>

Mapping for B:

<code>
<set name="as" table="AB_MAPPING" lazy="true">
<key>
<column name="pk1"/>
<column name="pk3"/>
</key>
<many-to-many class="A">
<column name="pk1"/>
<column name="pk2"/>
</many-to-many>
</set>
</code>

Here come the "real" definitions...
table for class Cell:

<code>
create table funzel
(
arb_lfdnr int not null,
ver_nr smallint not null,
nls_id char(1) not null,
sta_id char(5) not null,
sta_loc char(1) not null,
alt_nr int not null,
fan_lfdnr int not null,
zel_id char(1) not null,

zel_network_type char(1) null,
...
zel_kommentar varchar(255) null
)

alter table funzel
add constraint funzel_pk primary key clustered( arb_lfdnr, ver_nr, nls_id, sta_id, sta_loc, alt_nr, fan_lfdnr, zel_id )
</code>

table for class CellGroup:

<code>
create table funzgr
(
arb_lfdnr int not null,
ver_nr smallint not null,
zgr_id char(15) not null,

zgr_name varchar(36) null,
...
zgr_kommentar varchar(255) null
)

alter table funzgr
add constraint funzgr_pk primary key clustered( arb_lfdnr, ver_nr, zgr_id )
</code>

n-to-m mapping table:

<code>
create table funzgm
(
zgr_id char(15) not null,
arb_lfdnr int not null,
ver_nr smallint not null,
nls_id char(1) not null,
sta_id char(5) not null,
sta_loc char(1) not null,
alt_nr int not null,
fan_lfdnr int not null,
zel_id char(1) not null,

zgm_lfdnr int default 0
not null
)

alter table funzgm
add constraint funzgm_pk primary key clustered( zgr_id, arb_lfdnr, ver_nr, nls_id, sta_id, sta_loc, alt_nr, fan_lfdnr, zel_id )
</code>

mapping files:

<code>
<hibernate-mapping schema="dbo" package="de.vodafone.rnp.core.business">
<class name="Cell" table="funzel" proxy="Cell" lazy="true">
<composite-id>
<key-property name="workspaceId"/>
<key-property name="versionId"/>
<key-property name="regionId"/>
<key-property name="cellCompoundCode"/>
<key-property name="siteId"/>
<key-property name="alternativeId"/>
<key-property name="radioSystemId"/>
<key-property name="id"/>
</composite-id>
<property name="workspaceId" type="integer" column="arb_lfdnr"/>
<property name="versionId" type="integer" column="ver_nr"/>
<property name="regionId" type="character" column="nls_id"/>
<property name="cellCompoundCode" type="string" column="sta_id"/>
<property name="siteId" type="character" column="sta_loc"/>
<property name="alternativeId" type="integer" column="alt_nr"/>
<property name="radioSystemId" type="integer" column="fan_lfdnr"/>
<property name="id" type="character" column="zel_id"/>

<property name="networkSystemLabel" type="character" column="zel_freq_band"/>
...
<property name="pilotPower" type="double" column="zel_power_bcch"/>

<set name="cellgroups" table="funzgm" lazy="true">
<key>
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
<column name="nls_id"/>
<column name="sta_id"/>
<column name="sta_loc"/>
<column name="alt_nr"/>
<column name="fan_lfdnr"/>
<column name="zel_id"/>
</key>
<many-to-many class="CellGroup">
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
<column name="zgr_id"/>
</many-to-many>
</set>
<!--subclass name="de.vodafone.rnp.core.business.persistence.jdo.CellImpl"/-->
</class>
</hibernate-mapping>

<hibernate-mapping schema="dbo" package="de.vodafone.rnp.core.business">
<class name="CellGroup" table="funzgr" proxy="CellGroup" lazy="true">
<composite-id>
<key-property name="workspaceId"/>
<key-property name="versionId"/>
<key-property name="name"/>
</composite-id>
<property name="workspaceId" type="integer" column="arb_lfdnr" insert="false" update="false"/>
<property name="versionId" type="integer" column="ver_nr" insert="false" update="false"/>
<property name="name" type="string" column="zgr_id"/>

<set name="cells" table="funzgm" lazy="true" inverse="true">
<key>
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
<column name="zgr_id"/>
</key>
<many-to-many class="Cell">
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
<column name="nls_id"/>
<column name="sta_id"/>
<column name="sta_loc"/>
<column name="alt_nr"/>
<column name="fan_lfdnr"/>
<column name="zel_id"/>
</many-to-many>
</set>
<many-to-one name="version" class="Version">
<column name="arb_lfdnr"/>
<column name="ver_nr"/>
</many-to-one>
<!--subclass name="de.vodafone.rnp.core.business.persistence.jdo.CellGroupImpl"/-->
</class>
</hibernate-mapping>
</code>

The exception I get is:
net.sf.hibernate.MappingException: Repeated column in mapping for collection: de.vodafone.rnp.core.business.Cell.cellgroups column: arb_lfdnr