jQuery in Action, 3rd edition
The moose likes Object Relational Mapping and the fly likes JPA - Many to Many Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA - Many to Many Question" Watch "JPA - Many to Many Question" New topic

JPA - Many to Many Question

Shailesh Kini
Ranch Hand

Joined: Oct 17, 2001
Posts: 153
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

* userId (using oracle sequence) not null

* groupId (using oracle sequence) not null

* 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 ]

Shailesh Kini.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
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,


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


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.

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Shailesh Kini
Ranch Hand

Joined: Oct 17, 2001
Posts: 153
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.
Have you checked out Aspose?
subject: JPA - Many to Many Question
It's not a secret anymore!