How to Efficiently Retrieve the Insert ID in JDBC?

Efficiently obtain insertion ID in JDBC

When inserting data into a database table using JDBC, you often need to obtain the generated key (or insertion ID), which identifies the newly added record. JDBC provides a mechanism to efficiently retrieve this ID.

Use Statement#getGeneratedKeys()

For automatically generated keys, JDBC provides the Statement#getGeneratedKeys() method. This method is called on the same statement that performs the INSERT operation. To enable the generation of keys, the statement must be created with the Statement.RETURN_GENERATED_KEYS flag.

<code class="language-java">public void create(User user) throws SQLException {
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                                      Statement.RETURN_GENERATED_KEYS);
    ) {
        statement.setString(1, user.getName());
        statement.setString(2, user.getPassword());
        statement.setString(3, user.getEmail());
        // ...

        int affectedRows = statement.executeUpdate();

        if (affectedRows == 0) {
            throw new SQLException("创建用户失败，没有影响行。");
        }

        try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                user.setId(generatedKeys.getLong(1));
            }
            else {
                throw new SQLException("创建用户失败，未获得 ID。");
            }
        }
    }
}</code>

Driver Compatibility

Success of this approach depends on JDBC driver support. Most modern JDBC drivers support this feature, including MySQL, DB2, and PostgreSQL. However, the Oracle JDBC driver may still have limitations in this area.

Oracle Alternatives

For Oracle, consider using CallableStatement or a SELECT query with a RETURNING clause to retrieve the last generated key.

<code class="language-sql">"{? = call INSERT_FUNCTION(?, ?, ?)}"</code>

Placeholder ? represents a variable holding the insertion ID.

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