[Hibernate] many-to-many, invalid primary key in the join table?

[Raf Szczypiorski]

Hi. I am having some problems with my hibernate mapping. Please take a look at the hbm files:

<!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.Person">
		<id name="id" column="id">
			<generator class="native" />
		</id>
		<list name="addresses" table="Person_Address">
			<key column="person_id" foreign-key="FK_PERSON" />
			<list-index column="IDX" />
			<many-to-many column="address_id" class="com.Address" />
		</list>
	</class>
</hibernate-mapping>

and

<!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.Address">
		<id name="id" column="id">
			<generator class="native" />
		</id>
		<bag name="people" inverse="true" table="Person_Address">
			<key column="address_id" foreign-key="FK_ADDRESS" />
			<many-to-many column="person_id" class="com.Person" />
		</bag>
	</class>
</hibernate-mapping>

The list is necessary for the address, as I want to preserve the order of these elements (well, the real problem has different classes, but this is the simplest one that depicts what my question is).
The DDL that get's generated is:

create table Address (
        id number(19,0) not null,
        primary key (id)
    );

    create table Person (
        id number(19,0) not null,
        primary key (id)
    );

    create table Person_Address (
        address_id number(19,0) not null,
        person_id number(19,0) not null,
        IDX number(10,0) not null,
        primary key (person_id, IDX)
    );

    alter table Person_Address 
        add constraint FK_ADDRESS 
        foreign key (address_id) 
        references Address;

    alter table Person_Address 
        add constraint FK_PERSON 
        foreign key (person_id) 
        references Person;

The code for the classes is the simplest possible, just to make SchemaExport work:

package com;

import java.util.List;

public class Address {

    public long id;

    public List<Person> people;
}

package com;

import java.util.Set;

public class Person {

    public long id;

    public Set<Address> addresses;
}

As you can see, the join table has the primary key wrong, right? I would like it to be (person_id, adress_id). When it is like this, it could violate the contraint - a single person could be in different Address's collections, and on the same position (so that person_id and IDX are the same, but not address_id). Why is the IDX in the join table anyway, not in the Person table?
Please, help me configure it properly.

[Omar Al Kababji]

The IDX is in the join table because since its a list hibernate should maintain the order of how items are added to the list

person.getAddresses().add(address1);
person.getAddresses().add(address2);

this would generate some items in the Person_Address join table, and to know that address1 is at index 0 and address2 is at index 1 it needs the IDX so that when you get back the data from the DB the order is preserved.

id_person	id_address	IDX
0	id_address1	0
0	id_address2	1

for your real problem i can't help you. i usually write my DDL by hand and then map it to hibernate using annotations, i never generate DDL SQL automatically.

[Raf Szczypiorski]

I thought maybe it would be stored in the Person table, don't know why. Thanks.