在JDBC中返回生成的键

1. Overview

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

2. Setup

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

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

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</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.

3. Return Generated Keys Flag

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

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:

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.

Moreover, it’s also possible to use the same technique with normal 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.

4. Returning Columns

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:

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:

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:

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

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

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