Retrieval of AutoGenerated Keys

We often face the problem of retrieving a 'auto-generated' column after a 'INSERT' operation. For e.g.

insert into myTable (name) values ('Naren')");

Now immediately after the insert, I want to get the 'auto-generated' key of the inserted row. The only way to do it earlier in JDBC was to fire another query to get the last inserted row. But this method is fundamentally flawed, as there could be other inserts that have happened in-between.

Now, in JDBC 3.0 (in JDK 1.4), we have core JDBC methods that enable us to retrive the autogenerated key automatically. For e.g.

int rowcount = stmt.executeUpdate("sql here...",Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys ();

The ability to retrieve auto generated keys provides flexibility to the JDBC programmer and it provides a mechanism to realize performance boosts when accessing data.

The following methods are available since JDK 1.4

public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException
Executes the given SQL statement and signals the driver with the given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.
Parameters:
sql - must be an SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing.
autoGeneratedKeys - a flag indicating whether auto-generated keys should be made available for retrieval; one of the following constants: Statement.RETURN_GENERATED_KEYS
Statement.NO_GENERATED_KEYS

---------------------------------------
public int executeUpdate(String sql, int[] columnIndexes)
throws SQLExceptionExecutes the given SQL statement and signals the driver that
the auto-generated keys indicated in the given array should be made available for retrieval. The driver will ignore the array if the SQL statement is not an INSERT statement.

Parameters:
columnIndexes - an array of column indexes indicating the columns that should be returned from the inserted row
------------------------------------------
public int executeUpdate(String sql, String[] columnNames)
throws SQLExceptionExecutes the given SQL statement and signals the driver that
the auto-generated keys indicated in the given array should be made available for retrieval. The driver will ignore the array if the SQL statement is not an INSERT statement.

Parameters:
columnNames - an array of the names of the columns that should be returned from the inserted row
----------------------------------------

In Spring JDBC API, the JDBCTemplate has a utility method that directly gives the genetated key:
int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder)