reveng many-to-many with composite key

midnightraver · **Joined:** Tue Apr 01, 2008 3:34 pm **Posts:** 10

Hi All,

My question is, are composite keys for many-to-many tables supported by hibernate-tools' reverse engineering or does reveng only support single field primary keys in the many-to-many relationship? I have been unable to generate hbm.xml files from a many-to-many table that will support the many-to-many relationship with a mix of a composite key and a single field key. The reverse engineering process run under eclipse generates only one element of the multi-part key in the relationship for one of the objects, but is fine for the other.

I have searched high and low for an answer and the closest I came was a similar thread or two that was over a year old on reverse engineering many-to-many relationships...

http://forum.hibernate.org/viewtopic.php?t=965391&postdays=0&postorder=asc&start=30
http://forum.hibernate.org/viewtopic.php?t=965391&postdays=0&postorder=asc&start=0

...and the release notes (url found in the second post)...

http://opensource.atlassian.com/projects/hibernate/secure/ReleaseNote.jspa?version=10600&styleName=Html&projectId=10030&Create=Create

...which say that reverse engineering for many to many is now supported for "pure" many to many tables. I've seen pure many-to-many described in several places as a table with only the primary keys of 2 other tables which, combined, are the primary key for the many-to-many table. What is less clear is whether such a table may be composed of composite keys.

The tables involved are:

Code:

AIRCRAFT
pk AF_SERIAL_NUMBER

TEMPLATE
pk AM_MODEL, TMP_NAME

(many-to-many table)

Code:

AIRCRAFT_TEMPLATES 
pk AM_MODEL, TMP_NAME, AF_SERIAL_NUMBER
fk1 AM_MODEL, TMP_NAME references TEMPLATE
fk2 AF_SERIAL_NUMBER references AIRCRAFT

I've checked the "Detect many-to-many tables" checkbox in the code generation window and the mapping for Templates to Aircraft works great - XML for creating a set that maps to AF_SERIAL_NUMBER in the AIRCRAFT_TEMPLATES table is generated. It's the mapping from AIRCRAFT to AIRCRAFT_TEMPLATES that's giving me the problem. That mapping maps only AM_MODEL as the column in the many-to-many. What I believe it should generate, and what I would like is that the many-to-many should contain both AM_MODEL and TMP_NAME, as is specified in the key of the TEMPLATES table.

While this is not a great deal of work to fix one time - the generated hbm.xml can be easily altered by adding the second key, as demonstrated here (note that this link has a slightly different context but is similar and achieves the same end)...

http://www.mycoolbutton.com/blog/index.php/tutorial/how-to-configure-a-composite-foreign-key-in-hibernate-30/

...this isn't a very good solution since I will need to generate the mappings and classes many times throughout the developmnet lifecycle and I likewise have this problem in many different places.

Hibernate version: 3.2.0.GA (through Eclipse plugin, Eclipse 3.3.0)

hibernate.reveng.xml:
Empty except for type mapping DECIMAL to long and CLOB to string.

Here is the relevant sections from the mapping files...

Mapping documents:

Aircraft.hbm.xml (This one is NOT ok)

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Apr 1, 2008 3:14:29 PM by Hibernate Tools 3.2.0.CR1 -->
<hibernate-mapping>
    <class name="com.sk.common.dao.Aircraft" table="AIRCRAFT" schema="TEST">
.
.
.
        <set name="templates" inverse="false" table="AIRCRAFT_TEMPLATES">
            <key>
                <column name="AF_SERIAL_NUMBER" length="40" not-null="true" />
            </key>
            <many-to-many entity-name="com.sk.common.dao.Template">
                <column name="AM_MODEL" length="80" not-null="true" />
            </many-to-many>
        </set>
.
.
.

Template.hbm.xml (This one is OK)

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Apr 1, 2008 3:14:29 PM by Hibernate Tools 3.2.0.CR1 -->
<hibernate-mapping>
    <class name="com.sk.common.dao.Template" table="TEMPLATE" schema="TEST">
.
.
.
        <set name="aircrafts" inverse="false" table="AIRCRAFT_TEMPLATES">
            <key>
                <column name="AM_MODEL" length="80" not-null="true" />
                <column name="TMP_NAME" length="120" not-null="true" />
            </key>
            <many-to-many entity-name="com.sk.common.dao.Aircraft">
                <column name="AF_SERIAL_NUMBER" length="40" not-null="true" />
            </many-to-many>
        </set>
.
.
.

Name and version of the database you are using:
Oracle 10g with ojdbc14.jar from that installation.

I've tried to be concise while providing all the relevant information. Please do let me know if any additional information would be of use. Any help would be greatly appreciated.

max · **Posted:** Wed Apr 02, 2008 3:37 pm

the many-to-many detection detection does not care about the number of columns within the 2 primary keys - so yes, this is supported. If not its a bug.

midnightraver · **Joined:** Tue Apr 01, 2008 3:34 pm **Posts:** 10

Based on my experience then I'd say there is a bug, as outlined in the test case I describe. Maybe somebody could verify my test case...

Any advice on where to next? We have some influence over the tables so we're seeing if we can negotiate a collapse of the composite keys for any many-to-many tables into a single "native" generated id field and then inforce the old key structure with unique and not null constraints, but we're moving further and further away from our ideal implementation.

Thanks for your help.

max · **Posted:** Wed Apr 02, 2008 4:09 pm

you can implement your own manytomany detection in a custom reverse engineering strategy.

Look in the source of DefaultReverseEngineeringStrategy to see what it does...and if you find the bug please submit the patch back :)