wood burning stoves 2.0*
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
Author

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

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 ]

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,

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.


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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPA - Many to Many Question
 
Similar Threads
Nested tag - Invalid Argument looking up property (missing something?)
Spring/JPA/TopLink 11g extensions
Hibernate (HQL) and many-to-many issues
ManyToMany assoicate delete join table entry
Hibernate Association table - extra column