Spring JDBC Auto-Generated Keys: A Practical Guide

Every time you insert a record into a database with an auto-increment primary key, you almost always need that generated ID back. Maybe you're creating an invoice and need to link line items, or registering a user and need to create a session. Without the ID, you're stuck — you'd have to query the database again, which is inefficient and race-condition-prone.

Spring JDBC's JdbcTemplate offers a clean way to retrieve auto-generated keys using KeyHolder. But here's where it gets tricky: the implementation varies across databases. MySQL returns keys differently than PostgreSQL, and Oracle requires sequences. Most tutorials show a simple example that works in H2 and fails in production.

I've seen teams burn hours debugging why getGeneratedKeys() returns empty or throws exceptions. One fintech startup had a production outage because their key retrieval logic worked fine on local H2 but silently failed on MySQL, causing duplicate invoice numbers.

This guide covers everything you need to know about auto-generated keys in Spring JDBC — the right way, the wrong way, and the production gotchas that will save your weekend.

Understanding Auto-Generated Keys in SQL Databases

Auto-generated keys are a database feature that automatically assigns a unique identifier to a new row upon insertion. This is typically implemented as an AUTO_INCREMENT column in MySQL, SERIAL in PostgreSQL, or IDENTITY in H2 and SQL Server. When you insert a row without specifying this column, the database generates a value.

To retrieve this value after an insert, JDBC provides the getGeneratedKeys() method on Statement. Spring JDBC wraps this with KeyHolder and GeneratedKeyHolder. The core idea is that you prepare your insert statement, execute it, and then ask for the generated keys.

Here's the basic pattern:

```java KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(connection -> { PreparedStatement ps = connection.prepareStatement( "INSERT INTO users (username, email) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS ); ps.setString(1, "john_doe"); ps.setString(2, "john@example.com"); return ps; }, keyHolder);

Long userId = keyHolder.getKey().longValue(); ```

This works, but it's verbose. Spring also offers SimpleJdbcInsert which is more declarative:

```java SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource) .withTableName("users") .usingGeneratedKeyColumns("id");

Map<String, Object> params = new HashMap<>(); params.put("username", "jane_doe"); params.put("email", "jane@example.com");

Number newId = insert.executeAndReturnKey(params); ```

This is cleaner and less error-prone. I recommend SimpleJdbcInsert for most cases unless you need fine-grained control over the prepared statement.

import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

public class SimpleJdbcInsertExample {
    private SimpleJdbcInsert insert;

    public SimpleJdbcInsertExample(DataSource dataSource) {
        this.insert = new SimpleJdbcInsert(dataSource)
                .withTableName("orders")
                .usingGeneratedKeyColumns("order_id");
    }

    public long createOrder(String customerName, double total) {
        Map<String, Object> params = new HashMap<>();
        params.put("customer_name", customerName);
        params.put("total", total);
        Number newId = insert.executeAndReturnKey(params);
        return newId.longValue();
    }
}

Batch Inserts with Auto-Generated Keys

Batch inserts are common when importing data or processing high-volume transactions. Retrieving auto-generated keys in a batch is trickier because each insert may produce one or more keys, and the JDBC driver's behavior varies.

Spring JDBC provides an overloaded batchUpdate method that accepts a BatchPreparedStatementSetter and a KeyHolder. Here's how to use it:

```java List<Order> orders = getOrders(); // list of orders to insert

KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.batchUpdate( "INSERT INTO orders (customer_name, total) VALUES (?, ?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Order order = orders.get(i); ps.setString(1, order.getCustomerName()); ps.setDouble(2, order.getTotal()); }

@Override public int getBatchSize() { return orders.size(); } }, keyHolder );

List<Map<String, Object>> keyList = keyHolder.getKeyList(); // Each entry corresponds to a batch statement's generated keys ```

Important: The KeyHolder will contain a list of maps. Each map represents the keys generated by one batch execution. However, the mapping between the inserted rows and the keys in the list is not always 1:1 — some databases return keys per batch, not per row.

For MySQL with rewriteBatchedStatements=true, the driver may return only the first generated key per batch. To get all keys, you might need to disable batch rewriting or use a different approach like inserting one by one in a transaction.

Here's a safer batch approach that works across databases:

``java public List<Long> insertOrdersBatch(List<Order> orders) { List<Long> ids = new ArrayList<>(); jdbcTemplate.execute((ConnectionCallback<Void>) connection -> { connection.setAutoCommit(false); try (PreparedStatement ps = connection.prepareStatement( "INSERT INTO orders (customer_name, total) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)) { for (Order order : orders) { ps.setString(1, order.getCustomerName()); ps.setDouble(2, order.getTotal()); ps.addBatch(); } ps.executeBatch(); try (ResultSet rs = ps.getGeneratedKeys()) { while (rs.next()) { ids.add(rs.getLong(1)); } } connection.commit(); } catch (SQLException e) { connection.rollback(); throw e; } return null; }); return ids; } ``

This gives you control over the transaction and key retrieval, but it's more code. Choose based on your reliability needs.

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class BatchInsertWithKeys {
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> insertOrdersBatch(List<Order> orders) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.batchUpdate(
            "INSERT INTO orders (customer_name, total) VALUES (?, ?)",
            new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    Order order = orders.get(i);
                    ps.setString(1, order.getCustomerName());
                    ps.setDouble(2, order.getTotal());
                }

                @Override
                public int getBatchSize() {
                    return orders.size();
                }
            },
            keyHolder
        );
        return keyHolder.getKeyList();
    }
}

What the Official Docs Won't Tell You

Spring's official documentation on KeyHolder is deceptively simple. It shows a single-row insert and assumes it works everywhere. Here's what they leave out:

1. Column Name Mismatches

The GeneratedKeyHolder constructor accepts a string or array of strings representing the key column names. If you don't specify them, the JDBC driver may return keys under a default name like 'GENERATED_KEY' or an empty result set. Always specify the column name.

2. Database Driver Quirks

• MySQL Connector/J: With useGeneratedKeys=true (default), getGeneratedKeys() returns the first auto-increment value generated by the statement. For batch inserts, if rewriteBatchedStatements=true, it returns only the first value of the first batch. To get all values, set rewriteBatchedStatements=false or use individual inserts.
• PostgreSQL: Returns all generated keys correctly, but only if the table has a column with SERIAL or IDENTITY. If you use BIGSERIAL, it works. If you use a sequence manually, you must retrieve it separately.
• Oracle: Does not support auto-generated keys in the traditional sense. You must use sequences and triggers, or use RETURNING INTO clause with PreparedStatement. Spring's KeyHolder works with Oracle's RETURN_GENERATED_KEYS only if you use identity columns (12c+).

3. KeyHolder Reuse

The KeyHolder object is stateful. If you reuse the same instance across multiple inserts, it accumulates keys from all inserts. This can lead to confusion. Always create a new KeyHolder per insert operation.

4. Empty Key List

If your insert statement doesn't actually insert a row (e.g., due to a condition or trigger), getKey() returns null and getKeyList() returns an empty list. Check for this before calling getKey().longValue() to avoid NullPointerException.

5. SimpleJdbcInsert Limitations

SimpleJdbcInsert is great for single-row inserts, but it doesn't support batch operations. For batches, you're back to raw JdbcTemplate.

// BAD: No column name specified
KeyHolder keyHolder = new GeneratedKeyHolder();
// ... insert ...
Long id = keyHolder.getKey().longValue(); // may throw or return null

// GOOD: Specify column name
KeyHolder keyHolder = new GeneratedKeyHolder("user_id");
// ... insert ...
Long id = keyHolder.getKey().longValue(); // reliable

Using SimpleJdbcInsert for Cleaner Code

SimpleJdbcInsert is a higher-level abstraction that simplifies inserts with auto-generated keys. It uses a fluent API to configure the table name, column names, and key columns. Under the hood, it uses KeyHolder and PreparedStatementCreator, but with less boilerplate.

Example:

```java @Autowired private DataSource dataSource;

public long addUser(String username, String email) { SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource) .withTableName("users") .usingGeneratedKeyColumns("id");

MapSqlParameterSource params = new MapSqlParameterSource() .addValue("username", username) .addValue("email", email);

Number newId = insert.executeAndReturnKey(params); return newId.longValue(); } ```

You can also use Map<String, Object> instead of MapSqlParameterSource. The executeAndReturnKey method returns a Number, which you can convert to Long or Integer.

If you need to insert multiple rows and get all keys, SimpleJdbcInsert doesn't support batch — you'd need to loop and execute individually, which is inefficient. For batch, stick with JdbcTemplate.

One gotcha: SimpleJdbcInsert by default compiles the insert statement once and caches it. If you change the table structure dynamically (e.g., adding columns), you need to create a new SimpleJdbcInsert instance.

Also, SimpleJdbcInsert works well with NamedParameterJdbcTemplate if you need named parameters.

I prefer SimpleJdbcInsert for 90% of insert use cases because it's concise and less error-prone. Only drop down to JdbcTemplate + KeyHolder when you need batch operations or custom SQL.

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import javax.sql.DataSource;

public class UserDao {
    private SimpleJdbcInsert insert;

    public UserDao(DataSource dataSource) {
        this.insert = new SimpleJdbcInsert(dataSource)
                .withTableName("users")
                .usingGeneratedKeyColumns("id");
    }

    public long createUser(String username, String email) {
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("username", username)
                .addValue("email", email);
        return insert.executeAndReturnKey(params).longValue();
    }
}

Handling Multiple Generated Keys

Some tables have multiple auto-generated columns (e.g., a composite key with two auto-increment columns, though rare). More commonly, a trigger might generate additional values. Spring's KeyHolder supports multiple keys via getKeyList() which returns a List<Map<String, Object>>.

For a single insert, getKeyList() returns a list with one map. That map contains entries for each generated key column. For example, if your table has id and version both auto-generated, you can retrieve both:

```java KeyHolder keyHolder = new GeneratedKeyHolder(new String[]{"id", "version"}); jdbcTemplate.update(connection -> { PreparedStatement ps = connection.prepareStatement( "INSERT INTO documents (content) VALUES (?)", new String[]{"id", "version"} ); ps.setString(1, "content"); return ps; }, keyHolder);

Map<String, Object> keys = keyHolder.getKeys(); Long id = (Long) keys.get("id"); Long version = (Long) keys.get("version"); ```

Note that the column names must match exactly. Also, not all databases support returning multiple generated keys. PostgreSQL does; MySQL returns only the first auto-increment column unless you specify multiple columns in the prepareStatement call.

If you only need the first key, use keyHolder.getKey() which returns a Number. This is safe only if you're sure there's exactly one key column.

For batch inserts with multiple keys, the getKeyList() returns one map per batch execution, not per row. So if you insert 10 rows in one batch, you might get only one map. This is a known limitation; avoid relying on multiple keys in batch mode.

KeyHolder keyHolder = new GeneratedKeyHolder(new String[]{"id", "version"});
jdbcTemplate.update(connection -> {
    PreparedStatement ps = connection.prepareStatement(
        "INSERT INTO documents (content) VALUES (?)",
        new String[]{"id", "version"}
    );
    ps.setString(1, "sample content");
    return ps;
}, keyHolder);

Map<String, Object> keys = keyHolder.getKeys();
System.out.println("ID: " + keys.get("id"));
System.out.println("Version: " + keys.get("version"));

Testing Auto-Generated Key Retrieval

Testing key retrieval is critical, especially when targeting multiple databases. Use an in-memory database like H2 for unit tests, but always run integration tests against your target database.

Here's a test using Spring Boot's test slice for JDBC:

```java @SpringBootTest @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.ANY) // uses H2 class UserDaoTest {

@Autowired private JdbcTemplate jdbcTemplate;

@Autowired private DataSource dataSource;

@Test void shouldReturnGeneratedKey() { SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource) .withTableName("users") .usingGeneratedKeyColumns("id");

Map<String, Object> params = new HashMap<>(); params.put("username", "testuser"); params.put("email", "test@example.com");

Number newId = insert.executeAndReturnKey(params); assertNotNull(newId); assertTrue(newId.longValue() > 0); }

@Test void shouldReturnGeneratedKeyWithJdbcTemplate() { KeyHolder keyHolder = new GeneratedKeyHolder("id"); jdbcTemplate.update(connection -> { PreparedStatement ps = connection.prepareStatement( "INSERT INTO users (username, email) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS ); ps.setString(1, "testuser2"); ps.setString(2, "test2@example.com"); return ps; }, keyHolder);

Long id = keyHolder.getKey().longValue(); assertNotNull(id); assertTrue(id > 0); } } ```

For database-specific testing, use Testcontainers:

```java @Testcontainers @SpringBootTest class UserDaoMySqlTest {

@Container static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0");

@DynamicPropertySource static void configureProperties(DynamicPropertyRegistry registry) { registry.add("spring.datasource.url", mysql::getJdbcUrl); registry.add("spring.datasource.username", mysql::getUsername); registry.add("spring.datasource.password", mysql::getPassword); }

@Autowired private UserDao userDao;

@Test void shouldReturnKeyFromMySql() { long id = userDao.createUser("mysqluser", "mysql@example.com"); assertTrue(id > 0); } } ```

Always test with the exact JDBC driver version you use in production. Driver upgrades can change behavior.

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import static org.junit.jupiter.api.Assertions.*;

@JdbcTest
class UserDaoTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private DataSource dataSource;

    @Test
    void testSimpleJdbcInsert() {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource)
                .withTableName("users")
                .usingGeneratedKeyColumns("id");
        Map<String, Object> params = new HashMap<>();
        params.put("username", "test");
        params.put("email", "test@test.com");
        Number id = insert.executeAndReturnKey(params);
        assertNotNull(id);
        assertTrue(id.longValue() > 0);
    }

    @Test
    void testJdbcTemplateWithKeyHolder() {
        KeyHolder keyHolder = new GeneratedKeyHolder("id");
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO users (username, email) VALUES (?, ?)",
                Statement.RETURN_GENERATED_KEYS
            );
            ps.setString(1, "test2");
            ps.setString(2, "test2@test.com");
            return ps;
        }, keyHolder);
        Long id = keyHolder.getKey().longValue();
        assertNotNull(id);
        assertTrue(id > 0);
    }
}