AUTO_INCREMENT
Column Values through JDBCStatement
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. AUTO_INCREMENT
column values using Statement.getGeneratedKeys()
AUTO_INCREMENT
column values using SELECT LAST_INSERT_ID()
AUTO_INCREMENT
column values in Updatable ResultSets
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 is 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.