Spring JdbcTemplate批量更新未按预期批量插入问题求助

问题背景 #

期望通过Spring JdbcTemplate实现单次10000条记录的批量插入，数据库日志呈现多值INSERT格式：

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

但实际系统仅以1-3条的小批次发送请求，PostgreSQL日志显示为逐条执行插入。已尝试设置rewriteBatchedStatements=true、改用BatchPreparedStatementSetter形式，均未解决问题。

当前使用代码 #

public void insertTest(UUID var1, int var2, List<Shape> tests)
{
    jdbcTemplate.batchUpdate(
            "INSERT INTO database.table VALUES (?, ?, ?, ?, ?, ?, ?)",
            tests,
            10000,
            (ps, entity) ->
            {
                ps.setObject(1, var1);
                ps.setLong(2, var2);
                ps.setObject(3, entity.getVar3());
                ps.setObject(4, entity.getVar4());
                ps.setObject(5, entity.getVar5());
                ps.setObject(6, entity.getVar6());
                ps.setObject(7, entity.getVar7());
            });
}

数据库日志 #

2023-09-26T12:47:13.000+13:00   2023-09-25 23:47:13 UTC:10.20.0.125(28120):database:[4882]:LOG: duration: 0.003 ms bind S_1: INSERT INTO database.table VALUES ($1, $2, $3, $4, $5, $6)

2023-09-26T12:47:13.000+13:00   2023-09-25 23:47:13 UTC:10.20.0.125(28120):database:[4882]:DETAIL: parameters: $1 = 'data', $2 = '1', $3 = 'data', $4 = '1', $5 = 'data', $6 = 'data'

2023-09-26T12:47:13.000+13:00   2023-09-25 23:47:13 UTC:10.20.0.125(28120):database:[4882]:LOG: execute S_1: INSERT INTO database.table  VALUES ($1, $2, $3, $4, $5, $6)

尝试过的代码 #

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

public int[] batchInsert(List<Book> books) {

    return this.jdbcTemplate.batchUpdate(
        "insert into books (name, price) values(?,?)",
        new BatchPreparedStatementSetter() {

            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, books.get(i).getName());
                ps.setBigDecimal(2, books.get(i).getPrice());
            }

            public int getBatchSize() {
                return books.size();
            }

        });
}

解决方案 #

1. 纠正JDBC连接参数（核心问题） #

你设置的rewriteBatchedStatements=true是MySQL JDBC驱动专用参数，PostgreSQL驱动不识别。需要替换为PostgreSQL驱动的批量重写参数：
在JDBC URL中添加rewriteBatchedInserts=true，示例：

jdbc:postgresql://your-aws-pg-host:5432/your-db?rewriteBatchedInserts=true

注意：PostgreSQL JDBC驱动版本需≥4.2.0（建议使用最新稳定版），该参数是从4.2.0版本开始引入的。

2. 确保JdbcTemplate批量配置正确 #

你的现有代码中已经设置了batchSize=10000，这部分无需修改，但要确保传入的List<Shape>实际包含足够数量的记录（比如10000条）。设置rewriteBatchedInserts=true后，PostgreSQL驱动会自动将JdbcTemplate的批量操作转换为单条多值INSERT语句。

3. 备选方案：使用NamedParameterJdbcTemplate #

如果上述方式仍未生效，可以尝试使用NamedParameterJdbcTemplate实现批量插入，这种方式在驱动支持的情况下也会生成多值INSERT：

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

import java.util.List;
import java.util.UUID;
import java.util.stream.Collectors;

public void insertTest(UUID var1, int var2, List<Shape> tests) {
    String sql = "INSERT INTO database.table (col1, col2, col3, col4, col5, col6, col7) " +
                 "VALUES (:var1, :var2, :var3, :var4, :var5, :var6, :var7)";

    List<SqlParameterSource> paramSources = tests.stream()
            .map(shape -> new MapSqlParameterSource()
                    .addValue("var1", var1)
                    .addValue("var2", var2)
                    .addValue("var3", shape.getVar3())
                    .addValue("var4", shape.getVar4())
                    .addValue("var5", shape.getVar5())
                    .addValue("var6", shape.getVar6())
                    .addValue("var7", shape.getVar7()))
            .collect(Collectors.toList());

    namedParameterJdbcTemplate.batchUpdate(sql, paramSources.toArray(new SqlParameterSource[0]));
}

4. 验证效果 #

修改配置和代码后，查看PostgreSQL日志，应该会出现类似如下的多值INSERT语句：

LOG: execute <unnamed>: INSERT INTO database.table VALUES ($1,$2,$3,$4,$5,$6,$7),($8,$9,$10,$11,$12,$13,$14),...

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