Get generated id after insert

sql

database-resources

performance-optimization

best-practices

byNikita Barsukov·Jan 5, 2025

Use PreparedStatement along with Statement.RETURN_GENERATED_KEYS, execute the insert and then invoke getGeneratedKeys() to retrieve the auto-generated ID.

String sql = "INSERT INTO my_table (col1) VALUES (?)";  // Pretend col1 is cool data
try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    pstmt.setString(1, "Witty value, like '42'");  // Replace '42' with a real value
    pstmt.executeUpdate();
    ResultSet rs = pstmt.getGeneratedKeys();
    if (rs.next()) {
        System.out.println("Generated ID: " + rs.getLong(1));  // Who doesn't love surprises?
    }
}

Tackling multi-row inserts

With JDBC batch inserts, calling getGeneratedKeys() after executing the insert will return a ResultSet containing the generated ID of each row you inserted.

When doing batch inserts, you keep adding to the batch with addBatch() and then invoke executeBatch() to insert all rows. Then, getGeneratedKeys() will return a ResultSet containing an ID for each inserted row.

Handling errors

In programming, exceptions are inevitable. Always use try-with-resources or ensure to properly close your ResultSet and PreparedStatement from within a finally block to properly free up database resources.

Cracking the Room database in Android

Android developers using Room, an abstraction over SQLiteOpenHelper, also encounter this. With Room, entities and data access objects are defined using @Entity and @Dao annotations respectively.

With Room: Specify @Insert on your Dao method with onConflict = OnConflictStrategy.REPLACE. The @Insert annotation returns the new row ID of the inserted item.

SQLite under the hood

For SQLite, the operating logic is a bit different:

The rowid is a unique identifier for each row. sqlite3_last_insert_rowid() will give you the rowid.
Creating an INTEGER PRIMARY KEY column will make that column an alias for the rowid.

In case of multiple inserts, keep track of rowid or you can use a ContentValues object. And do remember to correctly handle the Cursor after rawQuery to prevent any memory leaks.

Performance and reliability matters

Up in the high traffic world, you need to ensure performance and reliability:

SQLite locks a row when you use last_insert_rowid().
With Room, inserts are transactional and thread-safe.
If your data is complex, you might need to select the last ID using other fields or timestamp for greater accuracy.

Large-scale concerns

When your small application grows big, you need to be careful with generated IDs:

If you are running a distributed system, ensure your generated IDs are unique across systems.
Use efficient retrieval methods to reduce database operation bottlenecks.
Make sure to set appropriate transaction isolation levels to ensure data integrity.

explain-codes / Sql / Get generated id after insert

Linked

Solutions for INSERT OR UPDATE on SQL Server



How to get the insert ID in JDBC?



Preparedstatement IN clause alternatives?



Multiple queries executed in java in a single statement



Postgresql INSERT FROM SELECT RETURNING ID

