使用Anorm批量插入具有很多列的表 [英] Batch insert with table that has many columns using Anorm
问题描述
我正在尝试使用Anorm(在play框架2.3.1中)向MySQL数据库表中进行批量插入.我正在构建的应用程序除了需要插入批处理数据外，还具有标准的Web前端，我想尝试将逻辑保持在同一软件堆栈上.
I'm trying to do a batch insert into a MySQL database table using Anorm (in the play framework 2.3.1). The application I'm building has a standard web front end in addition to requiring batch data inserts and I would like to try and keep the logic on the same software stack.
插入内容仅进入相同的几张表中.
The inserts only go into the same few tables.
一次要插入的行数将达到数百，甚至可能成千上万，我预计由于anorm/mysql/其他限制，我有时可能需要限制插入的行数.
The number of rows to be insert at once will reach hundreds and may get into thousands, I expect I may need to limit the number of inserted rows at some point due to anorm / mysql / other limits.
我正在使用的MySQL驱动程序是mysql-connector-java-5.1.31
The MySQL driver I'm using is mysql-connector-java - 5.1.31
以下是精简的用例.
使用表格:
CREATE TABLE table1
(
col1 INTEGER NOT NULL,
col2 BIGINT,
col3 VARCHAR(255)
);
和scala代码:
import play.api.Play.current
import play.api.db.DB
import anorm._
object TestInserts {
DB.withConnection("spo") { implicit conn =>
val theInserts = Seq(
Seq[NamedParameter]('val1 -> 1, 'val2 -> Some(1L), 'val3 -> Some("One"))
,Seq[NamedParameter]('val1 -> 2, 'val2 -> Some(2L), 'val3 -> Some("Two"))
,Seq[NamedParameter]('val1 -> 3, 'val2 -> Some(3L), 'val3 -> Some("Three"))
)
val insertBatchSQL = BatchSql( SQL("insert into table1 (col1, col2, col3) values ({val1}, {val2}, {val3})"), theInserts)
insertBatchSQL.execute
}
}
我遇到以下错误
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3688)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3670)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3715)
at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3659)
at com.jolbox.bonecp.PreparedStatementHandle.setInt(PreparedStatementHandle.java:828)
at anorm.ToStatement$intToStatement$.set(ToStatement.scala:164)
at anorm.ToStatement$intToStatement$.set(ToStatement.scala:163)
...
I looked at the test classes in the play framework that test the batch insert https://github.com/playframework/playframework/blob/master/framework/src/anorm/src/test/scala/anorm/BatchSqlSpec.scala and as far as I can tell it should work the same.
关于如何解决此问题的任何指针，或者我是否应该以其他方式解决此问题，都是很好的方法.
Any pointers on how to fix this or if I should be tackling this in a different way would be great.
推荐答案
我将使用选项B.我对BatchSql不太熟悉，因为上次我检查它只是按顺序执行了一系列查询，这太慢了.我建议将所有内容汇总到一个查询中.这样做比较乏味，但是执行一次带有一千次插入的查询要比一千次单次插入要快得多.
I'll go with option B. I'm not very familiar with BatchSql since last I checked it just executes a boat load of queries in sequence, which is awfully slow. I'd recommend aggregating everything into a single query. It's a little more tedious, but much faster to execute a single query with one thousand inserts than one thousand single inserts.
为方便起见，假设您拥有
For convenience, lets say you have Seq of
case class Test(val1: Int, val2: Option[Long], val3: Option[String])
然后您可以像这样构建查询:
Then you could build your query like this:
val values: Seq[Test] = Seq(....)
/* Index your sequence for later, to map to inserts and parameters alike */
val indexedValues = values.zipWithIndex
/* Create the portion of the insert statement with placeholders, each with a unique index */
val rows = indexValues.map{ case (value, i) =>
s"({val1_${i}}, {val2_${i}}, {val3_${i}})"
}.mkString(",")
/* Create the NamedParameters for each `value` in the sequence, each with their unique index in the token, and flatten them together */
val parameters = indexedValues.flatMap{ case(value, i) =>
Seq(
NamedParameter(s"val1_${i}" -> value.val1),
NamedParameter(s"val2_${i}" -> value.val2),
NamedParameter(s"val3_${i}" -> value.val3)
)
}
/* Execute the insert statement, applying the aggregated parameters */
SQL("INSERT INTO table1 (col1, col2, col3) VALUES " + rows)
.on(parameters: _ *)
.executeInsert()
注释:
在继续操作之前，您必须检查values是否为非空，因为它会生成无效的SQL语句.
You will have to check that values is non-empty before proceeding, as it would generate an invalid SQL statement if it was.
取决于要插入的行和列的数量，最终创建准备好的语句的令牌解析器将从要解析的令牌总数(和字符串大小)的速度变慢.我注意到几百行几列之后的情况.这可以有所缓解.由于Scala是一种强类型语言，Int和Long不会对SQL注入构成威胁.您可以只对这些列使用字符串插值/串联来准备您的SQL语句，然后通常将不安全的列与NamedParameter绑定.这样可以减少需要解析的令牌数量.
Depending on how many rows and columns you're inserting, eventually the token parsers that created the prepared statement will slow down from the sheer amount of tokens to parse (and the string size). I've noticed this after a few hundred rows with several columns. This can be mitigated somewhat. Thanks to Scala being a strongly typed language, Int and Long pose no threat for SQL injection. You could prepare your SQL statements using string interpolation/concatenation for just those columns and bind the unsafe columns with NamedParameter normally. That would cut down on the number of tokens that need to be parsed.
这篇关于使用Anorm批量插入具有很多列的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
