Java 使用 spring jdbcTemplate 将数据插入到多个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24589138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting data into multiple tables with spring jdbcTemplate
提问by Still Learning
I am working to insert n number of records into two tables with using java, spring jdbc template. some like this
我正在使用 java、spring jdbc 模板将 n 条记录插入到两个表中。有些像这样
assume daos.xml correctly configured.
假设 daos.xml 配置正确。
ApplicationContext ctxt = new ClassPathXmlApplicationContext("daos.xml");
JdbcTemplate template = (JdbcTemplate) ctxt.getBean("jdbcTemplate");
final List<Person> list = new ArrayList<>();
final List<Role> roles = new ArrayList<>();
for(int i =1; i<=100; i++){
Person item = new Person();
item.setFirstName("Naveen" + i);
item.setLastName("kumar" + i);
item.setDescription("D" + i);
list.add(item);
Role role = new Role();
role.setName("Admin");
role.setCode("c" + i);
roles.add(role);
}
String sql = "insert into person(first_name, last_name, description) values(?,?,?)";
int[] arr = template.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Person person = list.get(i);
ps.setObject(1, person.getFirstName());
ps.setObject(2, person.getLastName());
ps.setObject(3, person.getDescription());
}
@Override
public int getBatchSize() {
return list.size()
}
});
I am also configured Transaction Manager.
我还配置了事务管理器。
So my question is how can i insert data into both person and role table using batch. Because Person can have role. when i insert into person it require role id to be insert together. In this case person insertion query will looks like this.
所以我的问题是如何使用批处理将数据插入到人员和角色表中。因为 Person 可以有角色。当我插入人时，它需要将角色 ID 插入在一起。在这种情况下，人员插入查询将如下所示。
String sql = "insert into person(first_name, last_name, description, role_id) values(?,?,?, ?)";
I want to perform it into batch batch. because in my case i have min 10k person list to parse using file. So it can be a performance killer i insert role into table than get it and they insert person again.
我想批量执行它。因为在我的情况下，我有最少 10k 人的列表可以使用文件进行解析。所以它可能是一个性能杀手，我将角色插入表而不是获取它然后他们再次插入人。
采纳答案by Nailgun
You can use multilane statements and LAST_INSERT_ID()MySql function:
您可以使用多车道语句和LAST_INSERT_ID()MySql 函数：
String sql = "insert into role(name, code) values(?,?);" +
"insert into person(first_name, last_name, description, role_id) values(?,?,?,(SELECT LAST_INSERT_ID()));";
int[] arr = template.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Role role = roles.get(i);
Person person = list.get(i);
ps.setObject(1, role.getName());
ps.setObject(2, role.getCode();
ps.setObject(3, person.getFirstName());
ps.setObject(4, person.getLastName());
ps.setObject(5, person.getDescription());
}
@Override
public int getBatchSize() {
return list.size()
}
});
