使用Anorm具有多列的批插入表

Question

我试图使用Anorm (在play框架2.3.1中)对MySQL数据库表进行批处理插入。我正在构建的应用程序除了需要批量数据插入之外，还有一个标准的web前端，我想尝试将逻辑保持在同一个软件栈上。

插入的内容只进入相同的几个表。

一次插入的行数将达到数百行，并可能达到数千行，我预计由于anorm / mysql /其他限制，可能需要在某个点限制插入行的数量。

我使用的MySQL驱动程序是mysql-连接器-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)
...

我查看了play框架中测试批处理插入https://github.com/playframework/playframework/blob/master/framework/src/anorm/src/test/scala/anorm/BatchSqlSpec.scala的测试类，据我所知，它应该同样工作。

任何关于如何解决这个问题或者我是否应该以一种不同的方式来解决这个问题的建议都是很棒的。

Answer 1

我将使用选项B。我不太熟悉BatchSql，因为我上次检查它时，它只是按顺序执行大量查询，速度非常慢。我建议将所有内容聚合到一个查询中。使用一千次插入执行单个查询要比执行一千次插入要繁琐得多，但要快得多。

为了方便起见，让我们假设您的Seq是

case class Test(val1: Int, val2: Option[Long], val3: Option[String])

然后您可以像这样构建您的查询：

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语句。

根据插入的行和列数，创建预准备语句的令牌解析器最终会从要解析的令牌数量(以及字符串大小)中减慢。我已经注意到这一点，在几百行后，有几列。这种情况可以有所缓解。由于Scala是一种强类型语言，所以Int和Long不会对SQL注入造成威胁。您可以为这些列使用字符串内插/连接来准备SQL语句，并正常使用NamedParameter绑定不安全的列。这将减少需要解析的令牌的数量。

Answer 2

BatchSql上的一些问题在12d前就已经解决了(在2.3.1中支持)：https://github.com/playframework/playframework/pull/3087。它应该和它一起工作。

Answer 3

试试这个：

  def save(types: List[RoomType]): Unit = {
     DB.withTransaction { implicit c =>
        val update = SQL("insert into TABLE(col1, col2, col3) values ({col1}, {col2}, {col3})")
        val batch = (update.asBatch /: types)(
    (sql, _type) => sql.addBatchParams(_type.id, _type.name, _type.lang))
    batch.execute
  }
}