I have two queries executing, I am using the commit/Rollback method. The problem is for the second query, I need the ID from the first query for one of the foriegn key columns in the second query. Is there anyway of getting the ID for the first INSERT record before committing to the transaction? See my code below:
That depends on the DB and the JDBC driver used though. For a long time this wasn't supported by the JDBC drivers of Oracle and PostgreSQL. If I am correct, since one of the latest database and driver versions, Oracle finally supports it. For the older versions and for PostgreSQL you need to fire a specific query to obtain the generated key. Do that on the same Statement as you used for insert. Those of MySQL, DB2 and MSSQL already supported it for a long time.
Use (Prepared)Statement#getGeneratedKeys() on the same statement. The first column of the first row contains the generated key. [ December 21, 2008: Message edited by: Bauke Scholtz ]