Retrieving Auto-Generated Keys

Direct Answer

Request generated keys when creating the PreparedStatement, execute the insert, then read the ResultSet from getGeneratedKeys. The capability and returned columns are driver/database dependent; check DatabaseMetaData.supportsGetGeneratedKeys and vendor documentation. Some databases work better with SQL such as INSERT ... RETURNING or with explicit key column names.

Code Example

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement ps =
        con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { // opt in
    ps.setString(1, "Ravi");
    ps.setString(2, "ravi@x.com");
    ps.executeUpdate();
    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) {
            long newId = keys.getLong(1);   // the auto-generated PK
            System.out.println("new id: " + newId);
        }
    }
}

Common Mistakes & Pitfalls

• Forgetting RETURN_GENERATED_KEYS: getGeneratedKeys() returns nothing without it.
• SELECT MAX(id) after insert: racy under concurrency — may return another session's id.
• Not calling keys.next(): the keys come back as a ResultSet you must advance.
• Assuming column name: read by index getLong(1) (key column naming varies).