How to Retrieve the Insert ID After Using JDBC?

Accessing Newly Inserted Record IDs with JDBC

This guide details how to retrieve the automatically generated ID after inserting a database record using JDBC. Several approaches exist, depending on your database system.

Leveraging Statement#getGeneratedKeys()

For databases supporting auto-generated keys, the Statement#getGeneratedKeys() method provides a straightforward solution. This method returns a ResultSet containing the generated keys. Crucially, you must instruct the JDBC driver to return these keys by specifying Statement.RETURN_GENERATED_KEYS when preparing the statement.

Illustrative Example:

<code class="language-java">try (
    Connection connection = dataSource.getConnection();
    PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                               Statement.RETURN_GENERATED_KEYS);
) {
    // Set statement parameters
    // ...

    int affectedRows = statement.executeUpdate();

    if (affectedRows == 0) {
        throw new SQLException("Record insertion failed, no rows affected.");
    }

    try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
        if (generatedKeys.next()) {
            user.setId(generatedKeys.getLong(1));
        } else {
            throw new SQLException("Record insertion failed, no ID obtained.");
        }
    }
}</code>

Database-Specific Alternatives

It's important to note that Statement#getGeneratedKeys() isn't universally supported across all JDBC drivers. Alternative methods exist for specific database systems:

• Oracle: Employ a CallableStatement with a RETURNING clause, or execute a SELECT CURRVAL(sequencename) query after the insert operation.
• MySQL and DB2: These databases generally support Statement#getGeneratedKeys().
• PostgreSQL: Recent versions offer support for Statement#getGeneratedKeys().
• MSSQL: Support varies based on the driver version; check your driver's documentation for the appropriate method.

The above is the detailed content of How to Retrieve the Insert ID After Using JDBC?. For more information, please follow other related articles on the PHP Chinese website!