Help with FK in PreparedStatement

[Dominic Steng�rd]

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]

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:

public boolean execute(String sql, int autoGeneratedKeys)
                throws SQLException  

[Shankar Narayana]

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]

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.