JDBC 3.0 Auto Generated Keys

One of cool feature in JDBC 3.0 is retrieving auto generated keys (autonumber/sequence) while inserting a row into the table. Until now we need to fire two queries one to execute the insert and the next to retrieve the sequence/autonumber of the inserted rows (in any order). It's quite obvious that this is not an efficient way as both the statement must be parsed, explain plan generated and executed. JDBC 3.0 makes it simple to retrieve the autogenerated keys with just one SQL, while inserting the row.

Here is an example.

1. String insertSQL=
2. "INSERT INTO client_info(client_id,first_name,update_time) values (client_id.nextval,?,systimestamp)";
3. int[] keys={1};
4. // retrieve connection
5. PreparedStatement stmt=cn.prepareStatement(insertSQL,keys);
6. int cnt = stmt.executeUpdate();
7. ResultSet rs = stmt.getGeneratedKeys();
8. rs.next();
9. clientIdKey=rs.getInt(1);

In line 3 an int array is created to specify the column index of the auto generated key in the Insert SQL. In line 5 you pass the array created in line 3 as second argument to prepareStatement call. Once the query is executed you call getGeneratedKeys() method on the prepared statement object to retrieve the result set and then access this resultset to retrieve the generated keys.


This feature is supported from Oracle 10g R2 JDBC Drivers, not in previous JDBC versions (though this driver can be used for Oracle 9i as well). The ROWID is the default generated key retuned.