jdbctemplate.batchupdate()是否按照给定列表相同的顺序插入行?
我有一类员工
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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论