26.3.1.4. Retrieving AUTO_INCREMENT Column Values
Before version 3.0 of the JDBC API, there was no standard way of
retrieving key values from databases that supported “auto
increment” or identity columns. With older JDBC drivers
for MySQL, you could always use a MySQL-specific method on the
Statement interface, or issue the query
SELECT LAST_INSERT_ID() after issuing an
INSERT to a table that had an
AUTO_INCREMENT key. Using the MySQL-specific
method call isn't portable, and issuing a
SELECT to get the
AUTO_INCREMENT key's value requires another
round-trip to the database, which isn't as efficient as
possible. The following code snippets demonstrate the three
different ways to retrieve AUTO_INCREMENT
values. First, we demonstrate the use of the new JDBC-3.0 method
getGeneratedKeys() which is now the
preferred method to use if you need to retrieve
AUTO_INCREMENT keys and have access to
JDBC-3.0. The second example shows how you can retrieve the same
value using a standard SELECT
LAST_INSERT_ID() query. The final example shows how
updatable result sets can retrieve the
AUTO_INCREMENT value when using the
insertRow() method.
Example 26.8. Retrieving AUTO_INCREMENT Column Values using
Statement.getGeneratedKeys()
Statement stmt = null;
ResultSet rs = null;
try {
//
// Create a Statement instance that we can use for
// 'normal' result sets assuming you have a
// Connection 'conn' to a MySQL database already
// available
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_UPDATABLE);
//
// Issue the DDL queries for the table for this example
//
stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
stmt.executeUpdate(
"CREATE TABLE autoIncTutorial ("
+ "priKey INT NOT NULL AUTO_INCREMENT, "
+ "dataField VARCHAR(64), PRIMARY KEY (priKey))");
//
// Insert one row that will generate an AUTO INCREMENT
// key in the 'priKey' field
//
stmt.executeUpdate(
"INSERT INTO autoIncTutorial (dataField) "
+ "values ('Can I Get the Auto Increment Field?')",
Statement.RETURN_GENERATED_KEYS);
//
// Example of using Statement.getGeneratedKeys()
// to retrieve the value of an auto-increment
// value
//
int autoIncKeyFromApi = -1;
rs = stmt.getGeneratedKeys();
if (rs.next()) {
autoIncKeyFromApi = rs.getInt(1);
} else {
// throw an exception from here
}
rs.close();
rs = null;
System.out.println("Key returned from getGeneratedKeys():"
+ autoIncKeyFromApi);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) {
// ignore
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ex) {
// ignore
}
}
}
Example 26.9. Retrieving AUTO_INCREMENT Column Values using
SELECT LAST_INSERT_ID()
Statement stmt = null;
ResultSet rs = null;
try {
//
// Create a Statement instance that we can use for
// 'normal' result sets.
stmt = conn.createStatement();
//
// Issue the DDL queries for the table for this example
//
stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
stmt.executeUpdate(
"CREATE TABLE autoIncTutorial ("
+ "priKey INT NOT NULL AUTO_INCREMENT, "
+ "dataField VARCHAR(64), PRIMARY KEY (priKey))");
//
// Insert one row that will generate an AUTO INCREMENT
// key in the 'priKey' field
//
stmt.executeUpdate(
"INSERT INTO autoIncTutorial (dataField) "
+ "values ('Can I Get the Auto Increment Field?')");
//
// Use the MySQL LAST_INSERT_ID()
// function to do the same thing as getGeneratedKeys()
//
int autoIncKeyFromFunc = -1;
rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
if (rs.next()) {
autoIncKeyFromFunc = rs.getInt(1);
} else {
// throw an exception from here
}
rs.close();
System.out.println("Key returned from " + "'SELECT LAST_INSERT_ID()': "
+ autoIncKeyFromFunc);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) {
// ignore
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ex) {
// ignore
}
}
}
Example 26.10. Retrieving AUTO_INCREMENT Column Values in
Updatable ResultSets
Statement stmt = null;
ResultSet rs = null;
try {
//
// Create a Statement instance that we can use for
// 'normal' result sets as well as an 'updatable'
// one, assuming you have a Connection 'conn' to
// a MySQL database already available
//
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_UPDATABLE);
//
// Issue the DDL queries for the table for this example
//
stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
stmt.executeUpdate(
"CREATE TABLE autoIncTutorial ("
+ "priKey INT NOT NULL AUTO_INCREMENT, "
+ "dataField VARCHAR(64), PRIMARY KEY (priKey))");
//
// Example of retrieving an AUTO INCREMENT key
// from an updatable result set
//
rs = stmt.executeQuery("SELECT priKey, dataField "
+ "FROM autoIncTutorial");
rs.moveToInsertRow();
rs.updateString("dataField", "AUTO INCREMENT here?");
rs.insertRow();
//
// the driver adds rows at the end
//
rs.last();
//
// We should now be on the row we just inserted
//
int autoIncKeyFromRS = rs.getInt("priKey");
rs.close();
rs = null;
System.out.println("Key returned for inserted row: "
+ autoIncKeyFromRS);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) {
// ignore
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ex) {
// ignore
}
}
}
When you run the preceding example code, you should get the
following output: Key returned from
getGeneratedKeys(): 1 Key returned from
SELECT LAST_INSERT_ID(): 1 Key returned for
inserted row: 2 You should be aware, that at times, it can be
tricky to use the SELECT LAST_INSERT_ID()
query, as that function's value is scoped to a connection. So,
if some other query happens on the same connection, the value
will be overwritten. On the other hand, the
getGeneratedKeys() method is scoped by the
Statement instance, so it can be used
even if other queries happen on the same connection, but not on
the same Statement instance.
