Java 使用 JdbcTemplate 插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3165730/
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 multiple rows using JdbcTemplate
提问by Edward Dale
How can I execute the following SQL in a scalable way using JdbcTemplaterunning on mySQL. In this case, scalable means:
如何使用在 mySQL 上运行的JdbcTemplate以可扩展的方式执行以下 SQL 。在这种情况下，可扩展意味着：
- Only one SQL statement is executed on the server
- it works for any number of rows.
- 服务器上只执行一条SQL语句
- 它适用于任意数量的行。
Here's the statement:
这是声明：
INSERT INTO myTable (foo, bar) VALUES ("asdf", "asdf"), ("qwer", "qwer")
Assume that I have a list of POJO's with fooand barfields. I realize that I could just iterate over the list and execute:
假设我有一个包含foo和bar字段的 POJO 列表。我意识到我可以遍历列表并执行：
jdbcTemplate.update("INSERT INTO myTable(foo, bar) VALUES (?, ?)", paramMap)
but that doesn't doesn't accomplish the first criterion.
但这并不能满足第一个标准。
I believe I could also execute:
我相信我也可以执行：
jdbcTemplate.batchUpdate("INSERT INTO myTable(foo, bar) VALUES (?, ?)", paramMapArray)
but from what I can tell, that will just compile the SQL once and execute it multiple times, failing the first criterion again.
但据我所知，这只会编译 SQL 一次并执行多次，再次失败第一个标准。
The final possibility, which seems to pass both criteria, would be to simply build the SQL myself with a StringBuffer, but I'd like to avoid that.
最后一种可能性，似乎通过了这两个标准，就是自己用 a 简单地构建 SQL StringBuffer，但我想避免这种情况。
采纳答案by Will
Multirow inserts (using "row value constructors") are in fact part of the SQL-92 standard. See http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts.
多行插入（使用“行值构造函数”）实际上是 SQL-92 标准的一部分。请参阅 http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts。
Some databases do not support this syntax, but many do. In my experience Derby/Cloudscape, DB2, Postgresql and the newer Hypersonic 2.*+ releases do support this.
一些数据库不支持这种语法，但很多都支持。根据我的经验，Derby/Cloudscape、DB2、Postgresql 和较新的 Hypersonic 2.*+ 版本确实支持这一点。
Your concern about getting this to work as a PreparedStatement is understandable, but I've seen similar cases where Spring JDBC does automatically handle a Collection of items for certain queries (like where in (?)), but I cannot vouch for this case.
您对让它作为 PreparedStatement 工作的担忧是可以理解的，但我见过类似的情况，其中 Spring JDBC 会自动处理某些查询的项目集合（例如 (?) 中的位置），但我不能保证这种情况。
I did find some possibly helpful information at (can't add second link to this post) which might be of some help.
我确实在（无法在此帖子中添加第二个链接）找到了一些可能有用的信息，这可能会有所帮助。
I can tell you that its probably not possible for your second requirement (works for any number of arguments) to be met in the most strict sense: every database I've used does impose query length limitations that would come into play.
我可以告诉您，在最严格的意义上，您的第二个要求（适用于任意数量的参数）可能无法满足：我使用的每个数据库都施加了会起作用的查询长度限制。
回答by Chochos
You can't do this in JDBC, period. In MySQL it's just syntactic sugar, but the effect of the statement will be the same as issuing several INSERT statements. So you can use batchUpdate and it will have the same effect.
您不能在 JDBC 中执行此操作。在 MySQL 中，它只是语法糖，但语句的效果将与发出多个 INSERT 语句相同。所以你可以使用batchUpdate，它会产生同样的效果。
回答by Desorder
You can use BatchPreparedStatementSetterlike below.
您可以使用如下所示的BatchPreparedStatementSetter。
public void insertListOfPojos(final List<MyPojo> myPojoList) {
String sql = "INSERT INTO "
+ "MY_TABLE "
+ "(FIELD_1,FIELD_2,FIELD_3) "
+ "VALUES " + "(?,?,?)";
getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i)
throws SQLException {
MyPojo myPojo = myPojoList.get(i);
ps.setString(1, myPojo.getField1());
ps.setString(2, myPojo.getField2());
ps.setString(3, myPojo.getField3());
}
@Override
public int getBatchSize() {
return myPojoList.size();
}
});
}
回答by Sheetal Mohan Sharma
you can also try with jdbcInsert.executeBatch(sqlParamSourceArray)
您也可以尝试使用 jdbcInsert.executeBatch(sqlParamSourceArray)
// define parameters
jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
jdbcInsert.withTableName("TABlE_NAME");
SqlParameterSource[] sqlParamSourceArray = new SqlParameterSource[apiConsumer
.getApiRoleIds().size()];
for (int i = 0; i < myCollection.size(); i++)
{
sqlParamSourceArray[i] = new MapSqlParameterSource().addValue("COL1");
......................
}
// execute insert
int[] keys = jdbcInsert.executeBatch(sqlParamSourceArray);
回答by Anatolii Stepaniuk
It looks to me that batchUpdate() method of JdbcTemplate could be helpful in this case (copied from here http://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/):
在我看来， JdbcTemplate 的 batchUpdate() 方法在这种情况下可能会有所帮助（从这里复制http://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/）：
//insert batch example
public void insertBatch(final List<Customer> customers){
String sql = "INSERT INTO CUSTOMER " +
"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Customer customer = customers.get(i);
ps.setLong(1, customer.getCustId());
ps.setString(2, customer.getName());
ps.setInt(3, customer.getAge() );
}
@Override
public int getBatchSize() {
return customers.size();
}
});
}
