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

Question

I have a table, say Users with an auto-incremented 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.

score 0 · Accepted Answer · answered Jun 20 '17 at 14:30

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

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

1 Answers1