or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

advanced-patterns.mdcore-operations.mddatabase-init.mddatasource.mdembedded-database.mdexception-translation.mdindex.mdkey-generation.mdlob-support.mdresult-parameter-handling.mdsimplified-operations.mdsqlrowset.mdtransactions.md
tile.json

key-generation.mddocs/

Key Generation

Spring JDBC provides support for retrieving generated keys from insert operations and database-specific sequence/identity column incrementers for primary key generation.

Key Retrieval

KeyHolder

Interface for retrieving generated keys from insert operations.

public interface KeyHolder {
    Number getKey() throws DataAccessException;
    <T> T getKeyAs(Class<T> keyType) throws DataAccessException; // Since 5.3
    Map<String, Object> getKeys() throws DataAccessException;
    List<Map<String, Object>> getKeyList();
}

GeneratedKeyHolder

Default KeyHolder implementation.

public class GeneratedKeyHolder implements KeyHolder {
    public GeneratedKeyHolder() { }
    public GeneratedKeyHolder(List<Map<String, Object>> keyList) { }
    public Number getKey() throws DataAccessException { }
    public <T> T getKeyAs(Class<T> keyType) throws DataAccessException { } // Since 5.3
    public Map<String, Object> getKeys() throws DataAccessException { }
    public List<Map<String, Object>> getKeyList() { }
}

Key Incrementers

DataFieldMaxValueIncrementer

Interface for incrementing database field values.

public interface DataFieldMaxValueIncrementer {
    int nextIntValue() throws DataAccessException;
    long nextLongValue() throws DataAccessException;
    String nextStringValue() throws DataAccessException;
}

Database-Specific Incrementers

Oracle

public class OracleSequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public OracleSequenceMaxValueIncrementer() { }
    public OracleSequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

PostgreSQL

public class PostgresSequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public PostgresSequenceMaxValueIncrementer() { }
    public PostgresSequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

MySQL

public class MySQLMaxValueIncrementer extends AbstractColumnMaxValueIncrementer {
    public MySQLMaxValueIncrementer() { }
    public MySQLMaxValueIncrementer(DataSource dataSource, String incrementerName, String columnName) { }
}

H2

public class H2SequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public H2SequenceMaxValueIncrementer() { }
    public H2SequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

HSQLDB

public class HsqlSequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public HsqlSequenceMaxValueIncrementer() { }
    public HsqlSequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

public class HsqlMaxValueIncrementer extends AbstractIdentityColumnMaxValueIncrementer {
    public HsqlMaxValueIncrementer() { }
    public HsqlMaxValueIncrementer(DataSource dataSource, String incrementerName, String columnName) { }
}

MariaDB

public class MariaDBSequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public MariaDBSequenceMaxValueIncrementer() { }
    public MariaDBSequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

MySQL Identity Column

public class MySQLIdentityColumnMaxValueIncrementer extends AbstractIdentityColumnMaxValueIncrementer {
    public MySQLIdentityColumnMaxValueIncrementer() { }
    public MySQLIdentityColumnMaxValueIncrementer(DataSource dataSource, String incrementerName, String columnName) { }
}

SQL Server

public class SqlServerSequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public SqlServerSequenceMaxValueIncrementer() { }
    public SqlServerSequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

public class SqlServerMaxValueIncrementer extends AbstractColumnMaxValueIncrementer {
    public SqlServerMaxValueIncrementer() { }
    public SqlServerMaxValueIncrementer(DataSource dataSource, String incrementerName, String columnName) { }
}

DB2

public class Db2LuwMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public Db2LuwMaxValueIncrementer() { }
    public Db2LuwMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

public class Db2MainframeMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public Db2MainframeMaxValueIncrementer() { }
    public Db2MainframeMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

Derby

public class DerbyMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public DerbyMaxValueIncrementer() { }
    public DerbyMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

Sybase

public class SybaseMaxValueIncrementer extends AbstractIdentityColumnMaxValueIncrementer {
    public SybaseMaxValueIncrementer() { }
    public SybaseMaxValueIncrementer(DataSource dataSource, String incrementerName, String columnName) { }
}

public class SybaseAnywhereMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public SybaseAnywhereMaxValueIncrementer() { }
    public SybaseAnywhereMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

SAP HANA

public class HanaSequenceMaxValueIncrementer extends AbstractSequenceMaxValueIncrementer {
    public HanaSequenceMaxValueIncrementer() { }
    public HanaSequenceMaxValueIncrementer(DataSource dataSource, String incrementerName) { }
}

SQLite

public class SqliteMaxValueIncrementer extends AbstractIdentityColumnMaxValueIncrementer {
    public SqliteMaxValueIncrementer() { }
    public SqliteMaxValueIncrementer(DataSource dataSource, String incrementerName, String columnName) { }
}

Usage Examples

Retrieve Generated Key from Insert

import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.core.PreparedStatementCreator;
import java.sql.Statement;

// Using PreparedStatementCreator
KeyHolder keyHolder = new GeneratedKeyHolder();

jdbcTemplate.update(
    con -> {
        PreparedStatement ps = con.prepareStatement(
            "INSERT INTO users (name, email) VALUES (?, ?)",
            Statement.RETURN_GENERATED_KEYS
        );
        ps.setString(1, "John Doe");
        ps.setString(2, "john@example.com");
        return ps;
    },
    keyHolder
);

Number generatedId = keyHolder.getKey();
System.out.println("Generated ID: " + generatedId.longValue());

Multiple Generated Keys

// Table with multiple auto-generated columns
KeyHolder keyHolder = new GeneratedKeyHolder();

jdbcTemplate.update(
    con -> {
        PreparedStatement ps = con.prepareStatement(
            "INSERT INTO log_entries (message, level) VALUES (?, ?)",
            new String[]{"id", "created_at"}  // Specify key column names
        );
        ps.setString(1, "Application started");
        ps.setString(2, "INFO");
        return ps;
    },
    keyHolder
);

Map<String, Object> keys = keyHolder.getKeys();
Long id = ((Number) keys.get("id")).longValue();
Timestamp createdAt = (Timestamp) keys.get("created_at");

Named Parameters with Generated Keys

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

KeyHolder keyHolder = new GeneratedKeyHolder();

MapSqlParameterSource params = new MapSqlParameterSource()
    .addValue("name", "Jane Smith")
    .addValue("email", "jane@example.com")
    .addValue("status", "active");

namedTemplate.update(
    "INSERT INTO users (name, email, status) VALUES (:name, :email, :status)",
    params,
    keyHolder
);

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

SimpleJdbcInsert with Key Generation

import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

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

Map<String, Object> params = Map.of(
    "name", "Bob Johnson",
    "email", "bob@example.com",
    "status", "active"
);

Number id = insert.executeAndReturnKey(params);
System.out.println("Generated ID: " + id);

// Get complete KeyHolder
KeyHolder keyHolder = insert.executeAndReturnKeyHolder(params);

Batch Insert with Generated Keys

List<Map<String, Object>> batchValues = Arrays.asList(
    Map.of("name", "User1", "email", "user1@example.com"),
    Map.of("name", "User2", "email", "user2@example.com"),
    Map.of("name", "User3", "email", "user3@example.com")
);

KeyHolder keyHolder = new GeneratedKeyHolder();

// Note: Batch operations with key retrieval support varies by database
for (Map<String, Object> values : batchValues) {
    KeyHolder batchKeyHolder = new GeneratedKeyHolder();
    namedTemplate.update(
        "INSERT INTO users (name, email) VALUES (:name, :email)",
        new MapSqlParameterSource(values),
        batchKeyHolder
    );
    System.out.println("Generated ID: " + batchKeyHolder.getKey());
}

Oracle Sequence

import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;

// Create sequence incrementer
DataFieldMaxValueIncrementer incrementer =
    new OracleSequenceMaxValueIncrementer(dataSource, "user_seq");

// Get next value
long nextId = incrementer.nextLongValue();

// Use in insert
jdbcTemplate.update(
    "INSERT INTO users (id, name, email) VALUES (?, ?, ?)",
    nextId,
    "John Doe",
    "john@example.com"
);

PostgreSQL Sequence

import org.springframework.jdbc.support.incrementer.PostgresSequenceMaxValueIncrementer;

DataFieldMaxValueIncrementer incrementer =
    new PostgresSequenceMaxValueIncrementer(dataSource, "users_id_seq");

// Get next ID
long userId = incrementer.nextLongValue();

// Use with named parameters
namedTemplate.update(
    "INSERT INTO users (id, name, email) VALUES (:id, :name, :email)",
    Map.of("id", userId, "name", "Jane", "email", "jane@example.com")
);

MySQL Table-Based Incrementer

import org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer;

// Requires a table: CREATE TABLE sequence (name VARCHAR(100), value INT)
DataFieldMaxValueIncrementer incrementer =
    new MySQLMaxValueIncrementer(dataSource, "users", "id");

incrementer.setCacheSize(10);  // Cache 10 values

long nextId = incrementer.nextLongValue();

H2 Sequence

import org.springframework.jdbc.support.incrementer.H2SequenceMaxValueIncrementer;

DataFieldMaxValueIncrementer incrementer =
    new H2SequenceMaxValueIncrementer(dataSource, "user_seq");

int nextId = incrementer.nextIntValue();

String Keys with Padding

DataFieldMaxValueIncrementer incrementer =
    new OracleSequenceMaxValueIncrementer(dataSource, "order_seq");

incrementer.setPaddingLength(8);  // Pad to 8 characters

String orderId = incrementer.nextStringValue();  // e.g., "00000123"

jdbcTemplate.update(
    "INSERT INTO orders (order_id, total) VALUES (?, ?)",
    orderId,
    new BigDecimal("99.99")
);

Spring Configuration

@Configuration
public class KeyGenerationConfig {

    @Bean
    public DataFieldMaxValueIncrementer userIdIncrementer(DataSource dataSource) {
        return new PostgresSequenceMaxValueIncrementer(dataSource, "users_id_seq");
    }

    @Bean
    public DataFieldMaxValueIncrementer orderIdIncrementer(DataSource dataSource) {
        OracleSequenceMaxValueIncrementer incrementer =
            new OracleSequenceMaxValueIncrementer(dataSource, "order_seq");
        incrementer.setPaddingLength(10);
        return incrementer;
    }
}

@Service
public class UserService {
    private final JdbcTemplate jdbcTemplate;
    private final DataFieldMaxValueIncrementer userIdIncrementer;

    public UserService(JdbcTemplate jdbcTemplate,
                      DataFieldMaxValueIncrementer userIdIncrementer) {
        this.jdbcTemplate = jdbcTemplate;
        this.userIdIncrementer = userIdIncrementer;
    }

    public Long createUser(String name, String email) {
        long userId = userIdIncrementer.nextLongValue();
        jdbcTemplate.update(
            "INSERT INTO users (id, name, email) VALUES (?, ?, ?)",
            userId, name, email
        );
        return userId;
    }
}

Custom Incrementer

import org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer;

public class CustomIncrementer extends AbstractDataFieldMaxValueIncrementer {

    @Override
    protected long getNextKey() throws DataAccessException {
        // Custom logic to get next key
        return getJdbcTemplate().queryForObject(
            "SELECT next_value FROM custom_sequence WHERE name = ?",
            Long.class,
            getIncrementerName()
        );
    }
}

Best Practices

  1. Use database-specific incrementers - Better performance
  2. Let database generate keys - Simpler and more reliable
  3. Use KeyHolder - Standard way to retrieve generated keys
  4. Cache incrementer values - Reduce database round-trips
  5. Handle concurrent access - Sequences are thread-safe
  6. Use appropriate data types - Long for large tables
  7. Test key generation - Ensure uniqueness
  8. Consider UUIDs - Alternative to sequential keys