sqljdbcjavadb

Retrieve id of record just inserted into a Java DB (Derby) database

I am connecting to a Java DB database with JDBC and want to retrieve the id (which is on auto increment) of the last record inserted.

I see this is a common question, but I see solutions using for example MS SQL Server, what is the equivalent for Java DB?

Solution

  • No need to use a DBMS specific SQL for that.

    That's what getGeneratedKeys() is for.

    When preparing your statement you pass the name(s) of the auto-generated columns which you can then retrieve using getGeneratedKeys()

    PreparedStatement pstmt = connection.prepareStatement(
     "insert into some_table (col1, col2, ..) values (....)", 
      new String[] { "ID_COLUMN"} ); 

pstmt.executeUpdate();

ResultSet rs = pstmt.getGeneratedKeys(); // will return the ID in ID_COLUMN

    Note that column names are case sensitive in this case (in Derby and many other DBMS).
    new String[] { "ID_COLUMN"} is something different than new String[] { "id_column"}

    Alternatively you can also use:

    connection.prepareStatement("INSERT ...", PreparedStatement.RETURN_GENERATED_KEYS);