ash__999 ash__999 - 5 months ago 17
Java Question

How to return the autoincremented BIGINT key of the row inserted in MySQL through JDBC?

I have a table, say

Users
with an autoincremented primary key of type BIGINT.

In Java, I'm looking to


  • insert a row to
    Users

  • return the key of that row inserted.



I tried the following without much expectations since the key field of
Users
is
Long
and
executeBatch()
is returning an integer array.

String query2 = "INSERT INTO Users (name, status) VALUES (?, ?); "
+ "SELECT LAST_INSERT_ID();";
// ... code deleted for clarity
int [] tmp = pStat.executeBatch();


The array returned out of this code is empty.

How to do this in MySQL?

I've seen Get the new record primary key ID from mysql insert query? and some other relevant discussions.

TIA.

Answer Source

You need to pass Statement.RETURN_GENERATED_KEYS as one of the arguments while creating preparedstatement, have a look at javadoc here:

autoGeneratedKeys - a flag indicating whether auto-generated keys should be returned; one of Statement.RETURN_GENERATED_KEYS or Statement.NO_GENERATED_KEYS

Code would look like this:

PreparedStatement pStat = connection.prepareStatement(SQL_INSERT,
                                      Statement.RETURN_GENERATED_KEYS);

Once the statement gets executed, you can get the keys via getGeneratedKeys() method (javadoc here):

ResultSet rsKeys = statement.getGeneratedKeys();
//Iterate the resultset using next