使用JdbcTemplate插入多行 [英] Inserting multiple rows using JdbcTemplate

问题描述

如何使用 JdbcTemplate 。在这种情况下，可伸缩意味着：

1. 服务器上只执行一条SQL语句


2. 它适用于任意行数。

以下是声明：

  INSERT INTO myTable（foo，bar）VALUES（asdf，asdf），（qwer，qwer）
  

假设我有一个POJO列表，其中 foo 和 bar 字段。我意识到我可以遍历列表并执行：

  jdbcTemplate.update（INSERT INTO myTable（foo，bar） VALUES（？，？），paramMap）
  

但这并不能完成第一次我认为我也可以执行：

  jdbcTemplate。 batchUpdate（INSERT INTO myTable（foo，bar）VALUES（？，？），paramMapArray）
  

但据我所知，这只会编译SQL一次并多次执行，再次失败第一个标准。

最后的可能性，似乎都通过了标准，只是用 StringBuffer 简单地构建SQL，但我想避免这种情况。

解决方案

多行插入（使用行值构造函数）实际上是SQL-92标准的一部分。请参阅
http://en.wikipedia.org/wiki/Insert_(SQL ）#Multirow_inserts 。

有些数据库不支持这种语法，但很多数据库都支持这种语法。根据我的经验，Derby / Cloudscape，DB2，Postgresql和较新的Hypersonic 2。* +版本支持这一点。

您对将其作为PreparedStatement工作的担忧是可以理解的，但我看到类似的情况，Spring JDBC会自动处理某些查询的项目集合（例如（？）中的位置），但我无法保证这种情况。

我确实找到了一些可能有用的信息（无法添加此帖子的第二个链接）
可能会有所帮助。

我可以告诉你，对于你的第二个要求（适用于任何数量的参数）可能不可能在最严格的意义上得到满足：我使用过的每个数据库都会产生可能发挥作用的查询长度限制。

How can I execute the following SQL in a scalable way using JdbcTemplate running on mySQL. In this case, scalable means:

1. Only one SQL statement is executed on the server
2. it works for any number of rows.

Here's the statement:

INSERT INTO myTable (foo, bar) VALUES ("asdf", "asdf"), ("qwer", "qwer")

Assume that I have a list of POJO's with foo and bar fields. I realize that I could just iterate over the list and execute:

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.

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.

解决方案

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.

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.

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.

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.

这篇关于使用JdbcTemplate插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！