使用NamedParameterJDBCTemplate批量插入时如何获取生成的主键

Great question! I’ve run into this exact challenge before—needing to do efficient bulk inserts with NamedParameterJdbcTemplate while capturing all generated primary keys, since KeyHolder alone only works for single inserts. Let’s break down a few solid solutions depending on your use case.

Solution 1: Use SimpleJdbcInsert (Simplest Approach) #

If you’re using Spring (especially Spring Boot), SimpleJdbcInsert is a hidden gem that handles bulk inserts and generated keys out of the box, and it plays nicely with NamedParameterJdbcTemplate via its underlying JdbcTemplate.

Here’s how to implement it:

// Initialize the SimpleJdbcInsert with your table and primary key details
SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(namedParameterJdbcTemplate.getJdbcTemplate())
    .withTableName("your_table_name")
    .usingGeneratedKeyColumns("id") // Replace with your primary key column name
    .usingColumns("column1", "column2"); // List your insertable columns

// Convert your domain objects into a list of parameter maps
List<Map<String, Object>> batchParams = yourDomainObjects.stream()
    .map(obj -> {
        Map<String, Object> params = new HashMap<>();
        params.put("column1", obj.getColumn1());
        params.put("column2", obj.getColumn2());
        return params;
    })
    .collect(Collectors.toList());

// Execute the batch insert and get all generated keys
List<Number> generatedKeys = jdbcInsert.executeBatch(batchParams);

// Convert the keys to your desired type (e.g., Long)
List<Long> primaryKeys = generatedKeys.stream()
    .map(Number::longValue)
    .collect(Collectors.toList());

Pros: Clean, minimal code, handles database-specific key generation under the hood.
Cons: Less control over the raw SQL compared to writing your own query.

Solution 2: Batch Insert with Multi-Valued SQL (Highest Performance) #

For maximum performance (especially with large batches), use a single multi-valued INSERT statement (e.g., INSERT INTO table (col1, col2) VALUES (...), (...), (...)). Most modern databases (MySQL, PostgreSQL, SQL Server 2008+) support this, and you can capture all generated keys in one go.

// Step 1: Build the multi-valued SQL query with named parameters
String tableName = "your_table_name";
List<String> columns = Arrays.asList("column1", "column2");
int batchSize = yourDomainObjects.size();

// Build the columns part (e.g., "column1, column2")
String columnsPart = String.join(", ", columns);

// Build the values template (e.g., "(:column1_1, :column2_1)")
String valueTemplate = "(" + columns.stream()
    .map(col -> ":" + col + "_%d")
    .collect(Collectors.joining(", ")) + ")";

// Build the full values part by repeating the template for each record
String valuesPart = IntStream.rangeClosed(1, batchSize)
    .mapToObj(i -> String.format(valueTemplate, i))
    .collect(Collectors.joining(", "));

// Final SQL
String sql = String.format("INSERT INTO %s (%s) VALUES %s", tableName, columnsPart, valuesPart);

// Step 2: Prepare the combined parameter map
Map<String, Object> params = new HashMap<>();
for (int i = 0; i < batchSize; i++) {
    YourDomainObject obj = yourDomainObjects.get(i);
    params.put("column1_" + (i+1), obj.getColumn1());
    params.put("column2_" + (i+1), obj.getColumn2());
}

// Step 3: Execute and capture keys
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
namedParameterJdbcTemplate.update(sql, params, keyHolder, new String[]{"id"});

// Extract all generated keys from the KeyHolder
List<Long> primaryKeys = keyHolder.getKeyList().stream()
    .map(map -> ((Number) map.get("id")).longValue())
    .collect(Collectors.toList());

Pros: Single database round-trip, optimal performance for large batches.
Cons: Requires database support for multi-valued inserts; watch out for database-specific limits (e.g., MySQL’s max_allowed_packet for very large batches).

Solution 3: Low-Level JdbcTemplate Batch with Key Holders #

If you need full control over the prepared statements (e.g., for complex logic), you can drop down to the underlying JdbcTemplate and use a batch of PreparedStatementCreator instances paired with KeyHolders for each record.

// Your named parameter SQL
String namedSql = "INSERT INTO your_table_name (column1, column2) VALUES (:column1, :column2)";

// Convert named parameters to a parsed SQL object
ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(namedSql);
String plainSql = NamedParameterUtils.substituteNamedParameters(parsedSql, new MapSqlParameterSource());
List<String> paramNames = parsedSql.getParameterNames();

// Create a factory to generate prepared statements that return keys
PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(plainSql);
factory.setReturnGeneratedKeys(true);
factory.setGeneratedKeysColumnNames(new String[]{"id"});

// Prepare parameter sources for each record
SqlParameterSource[] paramSources = SqlParameterSourceUtils.createBatch(yourDomainObjects.toArray());

// Create a PreparedStatementCreator and KeyHolder for each record
List<PreparedStatementCreator> creators = new ArrayList<>();
KeyHolder[] keyHolders = new KeyHolder[paramSources.length];
Arrays.fill(keyHolders, new GeneratedKeyHolder());

for (int i = 0; i < paramSources.length; i++) {
    Map<String, Object> params = new HashMap<>();
    for (String paramName : paramNames) {
        params.put(paramName, paramSources[i].getValue(paramName));
    }
    creators.add(factory.newPreparedStatementCreator(params));
}

// Execute the batch and capture keys
JdbcTemplate jdbcTemplate = namedParameterJdbcTemplate.getJdbcOperations();
int[] updateCounts = jdbcTemplate.batchUpdate(
    creators.toArray(new PreparedStatementCreator[0]),
    keyHolders
);

// Extract the keys
List<Long> primaryKeys = Arrays.stream(keyHolders)
    .map(holder -> holder.getKey().longValue())
    .collect(Collectors.toList());

Pros: Full control over statement creation; works with all databases that support generated keys.
Cons: More boilerplate code; slightly less performant than the multi-valued approach (multiple round-trips depending on database batch handling).

Key Notes #

• Ensure your primary key column is configured to auto-generate values (e.g., AUTO_INCREMENT in MySQL, SERIAL in PostgreSQL).
• For Oracle, you’ll need to adjust the SQL to use the RETURNING clause (e.g., INSERT INTO ... VALUES (...) RETURNING id INTO :id) and use array binding for batch operations.
• Always test batch sizes to find the sweet spot—too small and you lose performance gains; too large and you might hit database limits.

内容的提问来源于stack exchange，提问作者LouisS