Many databases have hidden columns (pseudo-columns) that represent a unique key for each row in a table. Typically, using these types of columns in a query is the fastest way to access a row because the pseudo-columns usually represent the physical disk address of the data. Prior to JDBC 3.0, an application could only return the value of the pseudo-columns by executing a Select statement immediately after inserting the data. For example:
//insert row
int rowcount = stmt.executeUpdate (
"INSERT INTO LocalGeniusList (name)
VALUES ('Karen')");
// now get the disk address – rowid -
// for the newly inserted row
ResultSet rs = stmt.executeQuery (
"SELECT rowid FROM LocalGeniusList
WHERE name = 'Karen'");
Retrieving pseudo-columns this way has two major flaws. First, retrieving the pseudo-column requires a separate query to be sent over the network and executed on the server. Second, because there may not be a primary key over the table, the search condition of the query may be unable to uniquely identify the row. In the latter case, multiple pseudo-column values can be returned, and the application may not be able to determine which value is actually the value for the most recently inserted row.
An optional feature of the JDBC 3.0 specification is the ability to return auto-generated key information for a row when the row is inserted into a table. For example:
int rowcount = stmt.executeUpdate(
"INSERT INTO LocalGeniusList(name) VALUES('Karen')",
// insert row AND return key
Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
// key is automatically available
Now, the application contains a value that can be used in a search condition to provide the fastest access to the row and a value that uniquely identifies the row, even when a primary key doesn't exist on the table.
The ability to return keys provides flexibility to the JDBC developer and creates performance boosts when accessing data.
