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

Question

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?

Answer 1

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);