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

sql jdbc javadb
Kryptic picture Kryptic · Feb 4, 2011 · Viewed 11.6k times · Source

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

a_horse_with_no_name picture a_horse_with_no_name · Feb 4, 2011

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

Related questions

How can I identify columns when SELECTing from multiple tables with JDBC?

I have two tables that I join on the id-column, they look like: +-------+ | users | +----+--+---+ | id | name | +----+------+ +-------+ | posts | +-------+------+---------+ | id | user_id | message | +----+---------+---------+ And now I want to select …

Read More
How do I get the size of a java.sql.ResultSet?

Shouldn't this be a pretty straightforward operation? However, I see there's neither a size() nor length() method.

Read More
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname

I have this Java program: MySQLConnectExample.java import java.sql.*; import java.util.Properties; public class MySQLConnectExample { public static void main(String[] args) { Connection conn1 = null; Connection conn2 = null; Connection conn3 = null; try { String url1 = "jdbc:mysql://localhost:3306/aavikme"; String …

Read More