JPA - Many to Many Question

[Shailesh Kini]

Hello Ranchers,

I have a question on Many to Many relationship.

Here's my system info.
Using Toplink Essentials JPA
Using Oracle 10g database.

Here's my scenario - * = pk and ** = fk
I use these following tables

User
* userId (using oracle sequence) not null
name
age

Group
* groupId (using oracle sequence) not null
groupName


UserGroup
* userGroupId (Using oracle sequence) not null
** userId
** groupId

I have created 2 Entities => User and Group
UserGroup table contains the Many to Many mapping between User and Group tables.

Here's my problem
When I try to persist the user entity after adding new groups I get a database exception ->
Cannot insert NULL into UserGroup.userGroupId

JPA generated insert statement is
INSERT INTO UserGroup(userId, groupId) values(?,?)
bind => (111,10)

As you can see there is no userGroupId in the insert statement generated by toplink engine.
The correct insert statement should have been

INSERT INTO UserGroup(userGroupId, userId, groupId) values(?,?,?)
bind => (seq.next_val, 111, 10)

Is there any way in JPA I can add an autogenerated column to the @JoinTable annotation?

The only option i see is to use a trigger, just wanted to know if it is possible to insert a value into a column not participating as one of the joincolumns.
[ November 07, 2007: Message edited by: Shailesh Kini ]

[James Sutherland]

If your many-to-many join table has additional data, typically the best solution is to map the join table as an object.

So you would have,

User
-OneToMany->UserGroup

UserGroup
-id @Id, @Generated
-users @ManyToOne->User
-groups @ManyToOne->Group

Group
-OneToMany->UserGroup

But in your case since you only have a sequence id, you could also just use a trigger to assign the sequence and continue to use the ManyToMany mapping, it would probably be simpler. You could of course also just remove the sequence id.

[Shailesh Kini]

Hello James,

Thank you very much for your response. Yes, I just used the simpler approach... removed the auto generated column from the mapping table.