What is the standard practice for extracting an auto-incrementing primary key?

Question

I have some objects that will be created an entered into the database. There is no guarantee that any of their fields will be unique except for the auto-incrementing primary key. I would like to be able to access these objects later in the program using their primary key.

What is the best way to get this key out of the database? I can think of two methods to obtain the primary key after adding the object's data to the database:

Find the maximum primary key as this was the one most recently added.
Delete the current object, compare the data in the database with the data in the program, and then create a new object based on the row that is not contained in the program.

Both of these seem error-prone and hacky and I am wondering if there was a more standard way do to this.

possible duplicate of [SELECT LAST\_INSERT\_ID()](http://stackoverflow.com/questions/10294122/select-last-insert-id) — Ted Hopp, Dec 25 '13 at 06:03

score 1 · Accepted Answer · answered Dec 25 '13 at 06:00

Most databases have a way to access the last generated ID, take SQL server for example.

CREATE TABLE [SomeTable] (
    ID INT NOT NULL IDENTITY(1,1),
    FieldOne VARCHAR(MAX) NOT NULL,
    PRIMARY KEY(ID)
)

INSERT INTO [SomeTable](FieldOne) VALUES('a')

SELECT scope_identity() -- returns 1

INSERT INTO [SomeTable](FieldOne) VALUES('b')

SELECT scope_identity() -- returns 2

This has the advantage of being safe for multiple users / transactions.

The first suggestion about guessing what the next ID would be is not safe in that regard.

Your second suggestion will wreck havoc if you have any referential integrity or if there are any triggers.

score 0 · Answer 2 · answered Dec 25 '13 at 06:13

Take a look at

java.sql.Statement.executeUpdate(String sql, int autoGeneratedKeys)

Executes the given SQL statement and signals the driver with the given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.

score 0 · Answer 3 · answered Dec 25 '13 at 08:09

Java has defined methods to find auto generated key values irrespective of the SQL you are using. Following is an example on how to use Java code to find them.

Use getGeneratedKeys() method from your Statement or PreparedStatement object to identify the new auto generated values. Iterate the returned ResultSet object to get the newly generated key values in the order of batch statements.

This call may throw java.sql.SQLFeatureNotSupportedException if the JDBC driver, that you are using, does not support this method.

Sample code snippet:

String sql_insert =  
  "insert into my_table( non_auto_incrmnt_fld_names_,_separated ) " +  
  " values ( record1-values,cs-foreach-field ), "
  " ( record2-values,cs-foreach-field )"; // append as many as required  
...  
int rowsAffected = 
  stmtObject.executeUpdate( sql_insert, Statement.RETURN_GENERATED_KEYS );  
ResultSet rs = stmtObject.getGeneratedKeys();  

//******************************************************  
rs.last();  
int rows = rs.getRow();  
System.out.println( "Generated keys count: " + rows );  
//******************************************************/  

int currentRow = 1;  
rs.beforeFirst();  
while( rs.next() ) {  
    System.out.println( /**/( currentRow++ ) + " = " + /**/rs.getInt( 1 ) );  
} // while rs

What is the standard practice for extracting an auto-incrementing primary key?

3 Answers3