Access to auto increment identity field after SQL insert in Java

Question

Any advice on how to read auto-incrementing identity field assigned to newly created record from call through java.sql.Statement.executeUpdate?

I know how to do this in SQL for several DB platforms, but would like to know what database independent interfaces exist in java.sql to do this, and any input on people's experience with this across DB platforms.

Answer 1

The following snibblet of code should do ya':

PreparedStatement stmt = conn.prepareStatement(sql, 
                                 Statement.RETURN_GENERATED_KEYS);
// ...

ResultSet res = stmt.getGeneratedKeys();
while (res.next())
    System.out.println("Generated key: " + res.getInt(1));

This is known to work on the following databases

• Derby
• MySQL
• SQL Server

For databases where it doesn't work (HSQLDB, Oracle, PostgreSQL, etc), you will need to futz with database-specific tricks. For example, on PostgreSQL you would make a call to SELECT NEXTVAL(...) for the sequence in question.

Note that the parameters for executeUpdate(...) are analogous.