Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA - Many to Many Question

 
Shailesh Kini
Ranch Hand
Posts: 153
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 553
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 153
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic