Using Auto-Generated Keys

Most database products support automatic counters or auto-generated keys. Such a feature is used to maintain an ordered sequence of numbers or string values used as a record key when inserting new data in a table. When a key is defined as an auto-generated value, the application code doesn’t provide a value for the key when inserting rows. Instead, the database auto-increments the key and then performs the insertion of the data for the non-automatic fields. Note that in some cases, more than a single field is defined as an auto-generated key. In this case, the database generates independent values for each key. It maintains independent sequences for such keys.

JDBC provides a way to retrieve the values generated by the database for automatic keys. Statement.RETURN_GENERATED_KEYS must be passed as an argument to the execute(), executeUpdate(), and prepareStatement() methods in order to be able to retrieve auto-generated keys.

You retrieve the key values by using a ResultSet returned by the getGeneratedKeys() method. This method must be called just after an execute() or executeUpdate() call, as illustrated in Listing 8-5. In this example, I assume that one key is a generated key. The remaining record field values are explicitly passed by the program.

Listing 8-5: Using Auto-generated Keys

... ... Connection myConnection = DriverManager.getConnection(url,         "javauser", "hotjava"); PreparedStatement myStmt = myConnection.prepareStatement(         "INSERT INTO employees (firstname, name, dept, phone) " +         "VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATE_KEYS); myStmt.setString(1, "John"); myStmt.setString(2, "Doe"); myStmt.setString(3, "Engineering"); myStmt.setString(4, "x48888"); int res = myStmt.executeUpdate(); ResultSet rs = myStmt.getGeneratedKeys(); boolean isKeyAvailable = rs.next(); if (isKeyAvailable) {          String key = rs.getString(1);          System.out.println("The auto-generated key is: " + key); } rs.close(); myStmt.close(); myConnection.close(); ... ...

Note that the metadata object associated with this result set provides information such as the name(s) of the column(s) for which a key has been auto-generated, as well as the associated type.