Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Inserting into tables connected by a many-to-many relationship

 
Olimpiu Pop
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I'm working on a DB with the following structure:

[Events]
eventid:int - auto generated, primary Key
name:varchar(200)
type:int

[Problems]
problemid:int - auto generated, primary key
name:varchar(200)
type:int

[EventsProblems]
linkId:int - auto generated, primary key
eventId: int - FK referencing events table
problemId: int - FK referencing problems table

So I need to add a problem to an event, this implies to insert in the link table the id's
of the other two tables. Can you recommand me a decent way to do this?

ZT
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just insert the desired ID values? Sorry, I don't see a problem here.
 
Olimpiu Pop
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe I just can't see the wood because of the trees..but:

- I insert a problem, then I have to insert in the link table, in order to do that
I need both the ids(eventid,problemid). My problem is how to obtain the problemid.
Can you recommend a way to obtain this id(problemid) besides the one where I
select this ID from the problems table by extracting the last inserted row.
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, now I see, your actual problem is that you don't know how to obtain the generated key (the insert ID) from the statement?

If so, just use Statement#getGeneratedKeys() on the same statement directly after the executeUpdate(). It returns a ResultSet with the generated key in the 1st column of the 1st row.
 
Olimpiu Pop
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
that's it . Sorry for the confusion. Do you think that this can be done with a stored procedure?

Tz
 
Campbell Ritchie
Sheriff
Pie
Posts: 49466
64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"zup troll" please read the important administrative private message I am about to send you.

CR
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic