Returning the Generated Keys in JDBC – 在JDBC中返回生成的密钥

1. Overview

1.概述

In this quick tutorial, we’re going to see how we can get the last auto-generated keys with pure JDBC.

在这个快速教程中，我们将看到如何用纯JDBC获得最后的自动生成的键。

2. Setup

2.设置

In order to be able to execute SQL queries, we’re going to use an in-memory H2 database.

为了能够执行SQL查询，我们将使用一个内存H2数据库。

For our first step, then, let’s add its Maven dependency:

第一步，我们来添加其Maven依赖性。

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.200</version>
</dependency>

Also, we’ll use a very simple table with just two columns:

另外，我们将使用一个非常简单的表格，只有两列。

public class JdbcInsertIdIntegrationTest {

    private static Connection connection;

    @BeforeClass
    public static void setUp() throws Exception {
        connection = DriverManager.getConnection("jdbc:h2:mem:generated-keys", "sa", "");
        connection
          .createStatement()
          .execute("create table persons(id bigint auto_increment, name varchar(255))");
    }

    @AfterClass
    public static void tearDown() throws SQLException {
        connection
          .createStatement()
          .execute("drop table persons");
        connection.close();
    }

    // omitted
}

Here, we’re connecting to the generated-keys in-memory database and creating a table named persons in it.

这里，我们要连接到generated-keys内存数据库，并在其中创建一个名为persons的表。

3. Return Generated Keys Flag

3.返回生成的钥匙标志

One way to fetch the keys after the automatic generation is to pass Statement.RETURN_GENERATED_KEYS to the prepareStatement() method:

在自动生成后获取密钥的一种方法是将Statement.RETURN_GENERATED_KEYS传给prepareStatement()方法：

String QUERY = "insert into persons (name) values (?)";
try (PreparedStatement statement = connection.prepareStatement(QUERY, Statement.RETURN_GENERATED_KEYS)) {
    statement.setString(1, "Foo");
    int affectedRows = statement.executeUpdate();
    assertThat(affectedRows).isPositive();

    // omitted
} catch (SQLException e) {
    // handle the database related exception appropriately
}

After preparing and executing the query, we can call the getGeneratedKeys() method on the PreparedStatement to get the id:

在准备和执行查询后，我们可以调用getGeneratedKeys()方法，在PreparedStatement上获得id。

try (ResultSet keys = statement.getGeneratedKeys()) {
    assertThat(keys.next()).isTrue();
    assertThat(keys.getLong(1)).isGreaterThanOrEqualTo(1);
}

As shown above, we first call the next() method to move the result cursor. Then we use the getLong() method to get the first column and convert it to long at the same time.

如上所示，我们首先调用next() 方法来移动结果光标。然后我们使用getLong()方法来获取第一列，并同时将其转换为long。

Moreover, it’s also possible to use the same technique with normal Statements:

此外，也可以对普通的Statements使用同样的技术。

try (Statement statement = connection.createStatement()) {
    String query = "insert into persons (name) values ('Foo')";
    int affectedRows = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
    assertThat(affectedRows).isPositive();

    try (ResultSet keys = statement.getGeneratedKeys()) {
        assertThat(keys.next()).isTrue();
        assertThat(keys.getLong(1)).isGreaterThanOrEqualTo(1);
    }
}

Also, it’s worth mentioning that we’re using try-with-resources extensively to let the compiler to clean up after us.

另外，值得一提的是，我们正在使用try-with-resources广泛地让编译器在我们之后进行清理。

4. Returning Columns

4.返回列

As it turns out, we can also ask JDBC to return specific columns after issuing a query. In order to do that, we just have to pass an array of column names:

事实证明，我们也可以要求JDBC在发出查询后返回特定的列。为了做到这一点，我们只需要传递一个列名的数组。

try (PreparedStatement statement = connection.prepareStatement(QUERY, new String[] { "id" })) {
    statement.setString(1, "Foo");
    int affectedRows = statement.executeUpdate();
    assertThat(affectedRows).isPositive();

    // omitted
}

As shown above, we’re telling the JDBC to return the value of id column after executing the given query. Similar to the previous example, we can fetch the id afterward:

如上所示，我们要告诉JDBC在执行完给定的查询后返回id列的值。与之前的例子类似，我们可以在之后获取id。

try (ResultSet keys = statement.getGeneratedKeys()) {
    assertThat(keys.next()).isTrue();
    assertThat(keys.getLong(1)).isGreaterThanOrEqualTo(1);
}

We can use the same approach with simple Statements, too:

我们也可以对简单的Statements使用同样的方法。

try (Statement statement = connection.createStatement()) {
    int affectedRows = statement.executeUpdate("insert into persons (name) values ('Foo')", 
      new String[] { "id" });
    assertThat(affectedRows).isPositive();

    try (ResultSet keys = statement.getGeneratedKeys()) {
        assertThat(keys.next()).isTrue();
        assertThat(keys.getLong(1)).isGreaterThanOrEqualTo(1);
    }
}

5. Conclusion

5.总结

In this quick tutorial, we saw how we can fetch the generated keys after query execution with pure JDBC.

在这个快速教程中，我们看到了如何用纯JDBC在查询执行后获取生成的密钥。

As usual, all the examples are available over on GitHub.

像往常一样，所有的例子都可以在GitHub上找到。