Getting the Insert ID in JDBC

Last updated: July 6, 2024

Written by: Suraj Mishra

Reviewed by: Eric Martin

Persistence

Refactor Java code safely — and automatically — with OpenRewrite.

Refactoring big codebases by hand is slow, risky, and easy to put off. That’s where OpenRewrite comes in. The open-source framework for large-scale, automated code transformations helps teams modernize safely and consistently.

Each month, the creators and maintainers of OpenRewrite at Moderne run live, hands-on training sessions — one for newcomers and one for experienced users. You’ll see how recipes work, how to apply them across projects, and how to modernize code with confidence.

Join the next session, bring your questions, and learn how to automate the kind of work that usually eats your sprint time.

Regression testing is an important step in the release process, to ensure that new code doesn't break the existing functionality. As the codebase evolves, we want to run these tests frequently to help catch any issues early on.

The best way to ensure these tests run frequently on an automated basis is, of course, to include them in the CI/CD pipeline. This way, the regression tests will execute automatically whenever we commit code to the repository.

In this tutorial, we'll see how to create regression tests using Selenium, and then include them in our pipeline using GitHub Actions:, to be run on the LambdaTest cloud grid:

>> How to Run Selenium Regression Tests With GitHub Actions

1. Introduction

When working with JDBC to insert data into a database, retrieving the auto-generated primary key is a common requirement. JDBC provides a mechanism to fetch the insert ID immediately after an insert operation.

This tutorial discusses how to get the insert ID immediately after an insert operation.

2. Setup

Before discussing and implementing logic to get the insert ID, we’ll first discuss the necessary setup steps.

To test our implementation, we’ll use an in-memory H2 database. We can add the h2 database dependency in the pom.xml file:

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

In our test setup, we can connect to the H2 database and populate the database with our sample table, i.e., the Employees table.

private static void populateDB() throws SQLException {
    String createTable = """
        CREATE TABLE EMPLOYEES (
            id SERIAL PRIMARY KEY ,
            first_name VARCHAR(50),
            last_name VARCHAR(50),
            salary DECIMAL(10, 2)
        );
        """;
    PreparedStatement preparedStatement = connection.prepareStatement(createTable);
    preparedStatement.execute();
}

3. Retrieving Insert IDs

When executing the insert statement, if the table has an auto-generated key (such as AUTO_INCREMENT in MySQL, a SERIAL in PostgreSQL, or IDENTITY in the H2 database), JDBC can retrieve these keys using the getGeneratedKeys() method.

For inserting a record, we can use preparedStatement.executeUpdate() which returns the number of rows updated. To fetch the insert IDs we can use Statement.RETURN_GENERATED_KEYS:

String sql = "INSERT INTO employees (first_name, last_name, salary) VALUES (?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
statement.setString(1, "first");
statement.setString(2, "last");
statement.setDouble(3, 100.0);

int numRows = statement.executeUpdate();

Now, we can call statement.getGeneratedKeys() to get the ResultSet, which allows us to fetch the inserted IDs using getLong():

ResultSet generatedKeys = statement.getGeneratedKeys();
List<Long> insertIds = new ArrayList<>();
while(generatedKeys.next()){
    insertIds.add(generatedKeys.getLong(1));
 }

In the above code, getLong(1) retrieves the first generated key from ResultSet. If the insert operation produces multiple generated keys, we can access them using their respective positions. For instance, getLong(2) would fetch the second generated key in the row, getLong(3) would generate get third, and so on. Additionally, we can also access the generated keys with column labels, for example, getLong(“id1”), getLong(“id2”), and so on.

We can verify the result by writing a unit test:

@Test
public void givenDBPopulated_WhenGetInsertIds_ThenReturnsIds() throws SQLException {
    GetInsertIds getInsertIds = new GetInsertIds();
    List<Long> actualIds = getInsertIds.insertAndReturnIds(connection);
    ResultSet resultSet = connection.prepareStatement("select id from employees").executeQuery();
    List<Long> expectedIds = new ArrayList<>();
    while (resultSet.next()){
        expectedIds.add(resultSet.getLong(1));
    }

    assertEquals(expectedIds, actualIds);
}

4. Conclusion

In this article, we discuss the mechanism to get the insert IDs of inserted records using JDBC PreparedStatement. We also implemented the logic and verified it using a unit test.

The code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.