Sean Sean - 2 months ago 12
Java Question

Access to auto increment identity field after SQL insert in Java

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

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.