How to get the insert ID in JDBC?

sql

jdbc

best-practices

database-operations

byAnton Shumikhin·Sep 24, 2024

To instantly fetch the last insert ID in JDBC, use the mega-helpful getGeneratedKeys() method. Execute it post-insert to collect a fresh auto-generated key via ResultSet. Here's the cheat-code:

// Prepare the statement - this won't BLOW UP your DB, promise! ;)
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO myTable (col1) VALUES ('data')", Statement.RETURN_GENERATED_KEYS);
// Now, unleash the INSERT command!
pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
// Now the precious INSERT ID is ours! (evil laugh...)
if (rs.next()) System.out.println("The Goblet of Fire has chosen: " + rs.getLong(1));

Replace myTable and the INSERT values to match your *ahem* EVIL PLANS. End scheme.

Stomach various databases

Be mindful! The chameleons we call databases need specific strategies for retrieving the automatically-generated insert ID:

MySQL and DB2: Just follow the Fast answer, they are type easyMode.
Oracle: You can adopt the getGeneratedKeys() method, though the RETURNING clause in an INSERT statement or a CallableStatement are also good confidants.
PostgreSQL: Easy-peasy now due to recent updates, practically a sibling to MySQL and DB2 with RETURN_GENERATED_KEYS.
SQL Server: After INSERT, execute a boss move SELECT @@IDENTITY to pull the last insert ID. But watch for sneaky concurrent inserts!

Remember to inspect driver-specific scrolls (also known as documentation) to capture subtle behaviours of their JDBC implementation.

Best practices to rob(insert, not literally!) and run

Brace for INSERT storm

Operation Successful?: Verify executeUpdate() was a victorious cause (returns more than zero), confirming an insert before ID retrieval.
Handle with Care: Encase your JDBC endeavours within try-catch magic circles for graceful handling of exceptions.
Close all Portals: Employ try-with-resources magic for your PreparedStatement and ResultSet to assure all the Rifts (resources) are closed.

Prepare for parallel threats

Single-Threaded Champions: In moments of dealing with SELECT @@IDENTITY in SQL Server, you might resort to a one-man-army approach to fetch accurate ID amid other transactions.
Be Isolated, Stay Undisturbed: Call upon the spirit of transaction isolation to guard against dirty reads affecting your insert ID.

Choose your weapon (platform) wisely

Engaging with JDBC drivers, reckon the following:

Oracle JDBC: Invoke CallableStatement.getGeneratedKeys() or conjure a query with RETURNING into ResultSet.
MySQL JDBC: Unleash getGeneratedKeys() on a PreparedStatement or Statement like an almighty spell.
PostgreSQL JDBC: Consider the RETURNING clause right in the INSERT statement, or use the getGeneratedKeys() method.