Get the latest inserted value for an identity primary key

Question

I have a Statement where I insert some values to a table in my database, this table has a primary key which is identity it's name is : numBon.

When I execute the insert command, I want to get the value of numBon.

for now I'm working with this code :

Statement st = connection.createStatement();
ResultSet result = st.executeQuery("SELECT MAX(numBon) FROM BonInterne");
int numBon;
if (result.next()) {
            numBon = result.getInt("numBon");
        }

Isn't there any other way ?

http://stackoverflow.com/questions/4246646/mysql-java-get-id-of-the-last-inserted-value-jdbc?lq=1 I think this will help — eidsonator, May 31 '13 at 19:18

score 0 · Answer 1 · answered May 31 '13 at 19:19

Is this an autoincrement column? Assuming it is, you can do this:

SELECT LAST_INSERT_ID()

But that's not database agnostic. This article gives a much better answer. Or you can look at @eidsonator's comment to see a quicker snippet of how to get the ID from the insert statement.

score 0 · Accepted Answer · answered May 31 '13 at 19:26

Use Statement.getGeneratedKeys() to retrieve autogenerated values.

Statement st = connection.createStatement();
st.execute("insert into ... "); //query where the server generates a value for
                                 //an auto incremented column
ResultSet rs = st.getGeneratedKeys
int numBon;
if (rs.next()) { 
  numBon = result.getInt(1);
}

Get the latest inserted value for an identity primary key

2 Answers2