Spring Boot - 使用JDBC批量插入并返回ID

Question

假设我们有一个表格用户

id | name
_________
1  | name1
2  | name2

我可以通过以下几种方式向用户表中插入记录并获取插入的ID。

- 尝试使用SimpleJdbcInsert插入记录，并使用executeAndReturnId()方法获取插入的ID。 - 还尝试使用返回子句插入记录并使用queryForObject()获取插入的ID。

query = INSERT INTO users(name) VALUES('name3') RETURNING id;
insertedId = jdbcTemplate.queryForObject(query, Long.class);

对于批量插入：

query = INSERT INTO users(name) VALUES(?) RETURNING id;
List<Object[]> objects = new ArrayList<Object[]>(new Object[]{'name3'}, new Object[]{'name4'});
int[] rowsAffected = jdbcTemplate.batchUpdate(query, objects); // this only return affected rows in array

如何使用批量插入获取所有已插入的ID？

已经尝试过之前提出的问题，但没有帮助 :(

- jagadesh

---

你能具体说明一下使用情况吗？ - Gowtham

需要使用jdbcTemplate获取插入的id @Gowtham - jagadesh

有人似乎已经在这里 https://dev59.com/Rr7pa4cB1Zd3GeqPv0_d#74296674 找到了你的情况的解决方法，但我自己还没有尝试过... - Bernie Lenz

Answer 1

尝试使用int[]代替int[][]

- Harun Cetin

---

为此，我们需要使用另一个batchUpdate()声明，其中包含ParameterizedPreparedStatementSetter。它用于获取批量行影响计数。 - jagadesh

Answer 2

这里的根本问题是JDBC区分更新（仅返回受影响行数的SQL语句）和查询（返回一组行并在ResultSet中结束的SQL语句），并且仅允许对更新进行批处理（即一次将多个语句发送到服务器），而不允许对查询进行批处理。 INSERT ... RETURNING ... 属于 "查询" 类别，因此无法进行批处理。

可能的解决方法在this answer to "Insert batch and Return full object using NamedParameterJdbcTemplate" 中提到 - 基本上是针对每次调用在VALUES子句中构造多行的新SQL语句。 如果您以多种不同大小的批次调用此语句，则您的数据库优化器将看到许多不同的语句，并可能无法正确优化它（或者至少需要每次重新编译）。

我发现了这篇旧文章Batching Select Statement with JDBC，它详细阐述了几种选择，并提出了一种称为 "select batching" 的方法，其中设置了几种大小的预编译语句，然后将其中的几个发送到数据库与数据一起使用。

因为我刚刚也遇到了这个问题，所以我采取了这种方法，使用了Spring的NamedParameterJdbcTemplate，并构建了一个通用类，根据一些模板字符串组合这些查询语句。

完整的代码在我刚刚打开的这个PR中（适用于我需要的库）。

然后可以像这样使用它（使用问题中的示例）：

QueryStatementBatcher<Integer> batcher = new QueryStatementBatcher<>(
        "INSERT INTO users (name) VALUES ", "(:name#)", " RETURNING id",
        (row, n) -> row.getInt("id"));

Stream<MapSqlParameterSource> inputs =
    Stream.of("Paŭlo Ebermann", "jagadesh", "Harun Cetin")
          .map(name -> new MapSqlParameterSource()
                          .addValue("name#", name));

List<Integer> generatedIds =
   batcher.queryForStream(jdbcTemplate, inputs)
          .collect(toList());

当然，您只需要创建批处理程序一次并重复使用它（它没有可变状态，因此是线程安全的，并且在开始时计算模板需要一些工作量）。

- Paŭlo Ebermann