How to retrieve INSERT-generated IDs with JDBC.
December 17th, 2007
The answer is very simple. You just have to call the getGeneratedKeys() method of your statement object, right after its execution.
The getGeneratedKeys() method will return a ResultSet object that contains any keys that was generated by the execution of your statement.
Let's see a simple example. Assume the following MySQL table:
This is the code that will insert a row into this table. We do not provide a value for id, because it is an AUTO_INCREMENT field; MySQL will automatically assign a unique value for this field.
After the execution of the statement, we call getGeneratedKeys() to get the ResultSet to get the value that automatically was generated for the id field. We call next() to read the first line of the result set, and then we read the first column, which we expect to be an integer.
The getGeneratedKeys() method will return a ResultSet object that contains any keys that was generated by the execution of your statement.
Let's see a simple example. Assume the following MySQL table:
mysql> show fields from Person;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | | |
+-------+------------------+------+-----+---------+----------------+This is the code that will insert a row into this table. We do not provide a value for id, because it is an AUTO_INCREMENT field; MySQL will automatically assign a unique value for this field.
String sql = "INSERT INTO Person (name) VALUES ( ? )";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1,"Giannis");
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
rs.next();
int personId = rs.getInt(1);
rs.close();
statement.close();After the execution of the statement, we call getGeneratedKeys() to get the ResultSet to get the value that automatically was generated for the id field. We call next() to read the first line of the result set, and then we read the first column, which we expect to be an integer.