To use auto-generated keys in JDBC (like an AUTO_INCREMENT primary key), you need to follow a three-step process: notify the statement you want the keys, execute the update, and then retrieve them from a special ResultSet.

Here is a practical example using PreparedStatement:

1. Prepare the Statement

When creating your PreparedStatement, you must pass the constant Statement.RETURN_GENERATED_KEYS to let the driver know you want the keys back.

String sql = "INSERT INTO users (username, email) VALUES (?, ?)";
try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    pstmt.setString(1, "john_doe");
    pstmt.setString(2, "[email protected]");

    // ...
}

2. Execute and Retrieve

After calling executeUpdate(), use getGeneratedKeys() to fetch the IDs. Even if you only inserted one row, the keys are returned as a ResultSet because some databases support multiple generated keys per row or batch inserts.

int affectedRows = pstmt.executeUpdate();

if (affectedRows > 0) {
    try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
        if (generatedKeys.next()) {
            long id = generatedKeys.getLong(1);
            System.out.println("Inserted record ID: " + id);
        }
    }
}

Complete Example

Based on standard JDBC practices, here is how the implementation usually looks:

package org.kodejava.jdbc;

import java.sql.*;

public class GetGeneratedKey {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db";
        String user = "root";
        String password = "password";

        String sql = "INSERT INTO authors (name) VALUES (?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, "Herbert Schildt");
            pstmt.executeUpdate();

            // Retrieve the generated key
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (rs.next()) {
                    long generatedId = rs.getLong(1);
                    System.out.println("Generated ID: " + generatedId);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Key Points to Remember:

• Constant: Don’t forget Statement.RETURN_GENERATED_KEYS. Without it, getGeneratedKeys() will return an empty result set or throw an exception depending on the driver.
• Column Index: Usually, the generated key is in the first column of the returned ResultSet, so rs.getLong(1) is standard.
• Database Support: Most modern databases (MySQL, PostgreSQL, SQL Server, Oracle) support this, though the internal mechanism (Sequences vs. Identity columns) varies.