Spring SimpleJdbcInsert: read the generated key after one insert

Let the insert return the identity

The receipt table generates its ID. The fixture supplies tenant and amount, calls executeAndReturnKey, then queries that exact ID and checks the stored amount. Reading a separate maximum ID after insertion would race another writer and could associate the wrong receipt with the request.

The key is an identity, not proof that a wider workflow committed. If a later step fails inside the same transaction, the row may roll back even though the database assigned an ID. Transaction ownership and the outbox boundary cover what must commit together.

Declare columns deliberately

The insert builder names tenant_id and amount_cents explicitly. Depending on database metadata to decide every writable column can make a schema addition change the insert unexpectedly. Generated-key retrieval also depends on driver and database support; run the integration test against the actual engine before relying on it in a release.

Checked code

var insert = new SimpleJdbcInsert(source)
        .withTableName("receipt_entry")
        .usingColumns("tenant_id", "amount_cents")
        .usingGeneratedKeyColumns("id");
Number generatedId = insert.executeAndReturnKey(
        Map.of("tenant_id", "TENANT-A", "amount_cents", 4725L));

Verification boundary

The Maven source kit passes JdbcParametersAndKeysContractTest.generatedKeyIsReadFromTheDatabaseInsertResult on its local Spring Boot 4 and Java 21 fixture.

Cost and ownership

One insert and one verification query run in the fixture. Database-generated identity allocation and metadata inspection vary by driver; this H2 test does not prove another database's generated-key behavior. Keep a configured insert object reusable rather than rebuilding metadata on every request.

Common Mistakes

• Do not use SELECT MAX(id) to discover the row just inserted.
• Do not treat a generated ID as proof that a transaction committed.
• Do not assume H2 key retrieval establishes target-database behavior.

Read next

Spring JdbcTemplate: bound values and visible database constraints, Spring TransactionTemplate: roll back a failed multi-row change, Spring transactional outbox: commit a receipt and event row together, Flyway SQL migrations: record ordered schema changes against a real database.