Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help with FK in PreparedStatement

 
Dominic Steng�rd
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey JDBC gurus!

Need help. Im working with PreparedStatement to commit a transaction with info to two database tables. The problem is that the second tables INSERT statement needs to know which primary key the first tables INSERT was given, so that the second tables foreign key can be set to the correct value. The foreign key in the second table refers to the primary key in the first table.

How can I get to know which primary key the first INSERT statement is given, before I INSERT the second data chunk?

Hm ... hope you guys understand what my problem is.
Thanks in advance.

Regards
 
Craig Jackson
Ranch Hand
Posts: 405
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One option is to use the method below, where the autoGenertedKeys is
Statement.RETURN_GENERATED_KEYS. Then you can use then use the getGeneratedKeys method which will return a resultset of the generated keys:

 
Shankar Narayana
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

What I do basically to get around with this type of problem is create a sequence table for ex: for table name 'abc', i have the corresponding sequence table 'abc_sequence',which contains a single field 'sequence_id' and before you update 'abc' table, u insert a record into 'abc_sequence' table and get the 'sequence_id' which will be the primary key for abc table.

This way you can work with any database.


Regards,
Shankar.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dominic,
Not all JDBC drivers (and databases) support Craig's suggestion of using the Statement.RETURN_GENERATED_KEYS. Oracle, for example, does not. So if you are using an Oracle database, you're out of luck.

Shankar failed to mention that you need to lock the "sequence" table (in a multi-user environment) so that no-one else "steals" your primary key. While most databases provide facilities for locking tables, not all do.

In other words, the way to solve your problem depends on the database you are using. So if you care to mention what database that is, you may get some more relevant replies.

Good Luck,
Avi.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic