aspose file tools*
The moose likes JDBC and the fly likes Inserting into tables connected by a many-to-many relationship Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Inserting into tables connected by a many-to-many relationship" Watch "Inserting into tables connected by a many-to-many relationship" New topic
Author

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

Olimpiu Pop
Greenhorn

Joined: Mar 11, 2009
Posts: 3
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


a
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Just insert the desired ID values? Sorry, I don't see a problem here.
Olimpiu Pop
Greenhorn

Joined: Mar 11, 2009
Posts: 3
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

Joined: Oct 08, 2006
Posts: 2458
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

Joined: Mar 11, 2009
Posts: 3
that's it . Sorry for the confusion. Do you think that this can be done with a stored procedure?

Tz
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39869
    
  28
"zup troll" please read the important administrative private message I am about to send you.

CR
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Inserting into tables connected by a many-to-many relationship