jdbctemplate.batchupdate()是否按照给定列表相同的顺序插入行?

发布于 2025-01-23 05:45:00 字数 1254 浏览 0 评论 0原文

我有一类员工

public class Employee {
  int id;
  String name;
}

和一张带有自动化PK的Postgres中的相应表。

CREATE TABLE employee (
 id INT GENERATED BY DEFAULT AS PRIMARY KEY,
 name VARCHAR,
 employee_group INT
);

我想使用springs jdbctemplate.batchupdate()插入许多员工。

List<Employee> employees = List.of(new Employee("Carlos"), new Employee("Jane"), ...);
String sql = "insert into employee (name, employee_group) values (?, ?)";
int batchSize = 100;

jdbcTemplate.batchUpdate(sql, employees, batchSize, (ps, employee) -> {
  ps.setString(1, employee.getName());
  ps.setInt(2, 1);
}); 

假设此表是全新的,并且生成的ID从1开始,则Batchupdate(或Postgres?)是否保证插入行插入的行会按照给定列表的顺序获得键? 即,结果表看起来像这样吗?:

id | name  | employee_group
1    Carlos     1
2    Jane       1
...

我的问题的原因是,我有第二个桌子

CREATE TABLE employee_info (
 employee_id REFERENCES employee(id),
 phone VARCHAR
 ...
);

插入员工之后,我需要批量插入员工info。为此,我需要生成的主键。如果这些是按我的列表的顺序生成的,我知道通过使用给定的员工_group查找所有ID,我知道哪个ID属于哪个员工。不幸的是,jdbctemplate.batchupdate据我发现,这是最佳解决方案。

I have a class of Employees

public class Employee {
  int id;
  String name;
}

And a corresponding table in postgres with autogenerated PK.

CREATE TABLE employee (
 id INT GENERATED BY DEFAULT AS PRIMARY KEY,
 name VARCHAR,
 employee_group INT
);

I want to use Springs jdbcTemplate.batchUpdate() to insert many employees.

List<Employee> employees = List.of(new Employee("Carlos"), new Employee("Jane"), ...);
String sql = "insert into employee (name, employee_group) values (?, ?)";
int batchSize = 100;

jdbcTemplate.batchUpdate(sql, employees, batchSize, (ps, employee) -> {
  ps.setString(1, employee.getName());
  ps.setInt(2, 1);
}); 

Assuming that this table is brand new, and that generated ids start from 1, does batchUpdate (or postgres?) guarantee that inserted rows will get keys following the order of the given list?
I.e, will the resulting table look like this?:

id | name  | employee_group
1    Carlos     1
2    Jane       1
...

The reason for my question is that I have a second table

CREATE TABLE employee_info (
 employee_id REFERENCES employee(id),
 phone VARCHAR
 ...
);

After I have batch-inserted the employees, I need to batch insert employee-info. For that I need the generated primary keys. If these are generated in the order of my list I know which ID belongs to which employee, by finding all ids with the given employee_group. Unfortunately jdbcTemplate.batchUpdate does not have a way of returning generated keys as far as I can find which would be the optimal solution.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文