如何在JDBC中获取插入ID?

问题描述

我想在 Java 中使用 JDBC 在数据库(在我的例子中是 Microsoft SQL Server)中INSERT 一条记录.同时，我想获取插入ID.如何使用 JDBC API 实现这一点?

I want to INSERT a record in a database (which is Microsoft SQL Server in my case) using JDBC in Java. At the same time, I want to obtain the insert ID. How can I achieve this using JDBC API?

推荐答案

如果是自动生成的key，那么可以使用Statement#getGeneratedKeys() 为此.您需要在与用于 INSERT 的语句相同的 Statement 上调用它.您首先需要使用 Statement.RETURN_GENERATED_KEYS 通知 JDBC 驱动程序返回密钥.

If it is an auto generated key, then you can use Statement#getGeneratedKeys() for this. You need to call it on the same Statement as the one being used for the INSERT. You first need to create the statement using Statement.RETURN_GENERATED_KEYS to notify the JDBC driver to return the keys.

这是一个基本示例:

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("Creating user failed, no rows affected.");
        }

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

请注意，您依赖于 JDBC 驱动程序是否有效.目前，大多数最新版本都可以工作，但如果我是对的，Oracle JDBC 驱动程序在这方面仍然有些麻烦.MySQL 和 DB2 已经支持它很久了.PostgreSQL 不久前开始支持它.我无法评论 MSSQL，因为我从未使用过它.

Note that you're dependent on the JDBC driver as to whether it works. Currently, most of the last versions will work, but if I am correct, Oracle JDBC driver is still somewhat troublesome with this. MySQL and DB2 already supported it for ages. PostgreSQL started to support it not long ago. I can't comment about MSSQL as I've never used it.

对于 Oracle，您可以使用 RETURNING 子句或 SELECT CURRVAL(sequencename)(或任何特定于数据库的语法)调用 CallableStatement这样做)直接在同一事务中的 INSERT 之后获取最后生成的密钥.另请参阅此答案.

For Oracle, you can invoke a CallableStatement with a RETURNING clause or a SELECT CURRVAL(sequencename) (or whatever DB-specific syntax to do so) directly after the INSERT in the same transaction to obtain the last generated key. See also this answer.

这篇关于如何在JDBC中获取插入ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！