CODEWITHSUNDEEP
javamysqljdbc
Prostitutor
Prostitutor

Reputation: 383

MYSQL Connector/J insert and get generated autoincrement key

I can insert into MySQL via Connector/J. But:

res = stmt.executeUpdate(query);

returns nothing. How do I know the new generated row auto increment key value. I need to have reference to row I just generated.

Upvotes: 0

Views: 423

Answers (3)

user330315
user330315

Reputation:

You can either use last_insert_id() or the the JDBC API to retrieve the generated keys:

String query = "insert into ....";
PreparedStatement pstmt = connection.prepareStatement(query,  Statement.RETURN_GENERATED_KEYS);
int rowsAffected = pstmt.executeUpdate();
int newId = -1;
ResultSet rs = pstmt.getGeneratedKeys();
if (rs.next()) 
{
  newId = rs.getInt(1);
}
rs.close();
pstmt.close();

Upvotes: 3

Farlan
Farlan

Reputation: 1900

the executeUpdate returns the number of affected rows by your query. to get the latest auto increment id you will need to execute another query. I have used SELECT LAST_INSERT_ID(); which should return the latest value

Upvotes: 0

O. Jones
O. Jones

Reputation: 108651

Immediately after your insert, on the same connection, issue this SQL query:

SELECT LAST_INSERT_ID()

It returns the most recently generated autoincrement value. It's stored on a per-connection basis, so it's reliable even on a busy multiclient database.

http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-last-insert-id.html

Upvotes: 0

Related Questions