ScalikeJDBCでバルクインサート＆バルクアップデート

SQLでたくさんのデータをインサートしたりアップデートしたりすることを探していたら、意外とニッチな要求らしく、ググるにしても難しく、かつそれをScalikeJDBCでやるにはどうしたらいいんだろう？と躓いたので、メモとして残しておきます。

バルクインサート、バルクアップデートとは？

手でSQL書くには多すぎる、例えば20件を超えるようなデータを一気にインサートしたりアップデートしたりすることを指します。後のScalikeJDBCでSQL Interpolationで説明するので、実際のSQL例を提示して説明してみます。

バルクインサート

SQLのインサートの構文はもともとバルクインサートできるようになっています。

insert into hoge(id, fuga, piyo)
  values (1, 'fuga1', 'piyo1'),
         (2, 'fuga2', 'piyo2'),
         (3, 'fuga3', 'piyo3'),
         (4, 'fuga4', 'piyo4'),
         ...省略
         (20, 'fuga20', 'piyo20')

バルクアップデート

バルクアップデートはちょっと工夫が必要になります。

update hoge as h
  set fuga = updated.fuga
      piyo = updated.piyo
  from (values
      (1, 'FUGA1', 'PIYO1'),
      (2, 'FUGA2', 'PIYO2'),
      ...省略
      (20, 'FUGA20', 'PIYO20')
    ) as updated(id, fuga, piyo)
  where h.id = updated.id

fromに続けてアップデートするデータをvaluesで指定する。また、setに指定するフィールド名はh.fuga = updated.fugaとかするとすぐ怒られるので注意。

ScalikeJDBCを使う

ScalikeJDBCではデータをsqlsで作って、それをsqls.csvで挿入するのが肝です。上記クックブックでもSQLInterpolationが推奨されているので、例はSQLInterpolationを利用します。

バルクインサート

val values: Seq[Hoge] = ... // インサートするデータ
val sqlsValues = values.map { s =>
  sqls"""
    |(
    |  ${s.fuga},
    |  ${s.piyo}
    |)""".stripMargin
}

// bulk insert
sql"""
  insert into hoge(fuga, piyo)
    values ${sqls.csv(sqlsValues: _*)}
  """.update().apply()

バルクアップデート

val values: Seq[Hoge] = ... // アップデートするデータ
val sqlsValues = values.map { s =>
  sqls"""
    |(
    |  ${s.id},
    |  ${s.fuga},
    |  ${s.piyo}
    |)""".stripMargin
}

// bulk update
sql"""
  update hoge as h set fuga = updated.fuga, piyo = updated.piyo
    from (values ${sqls.csv(sqlsValues: _*)}) as updated(id, fuga, piyo)
    where h.id = updated.id
  """.update().apply()

SQLInterpolationについてのTips

ScalikeJDBCのSQLInterpolationで${...}は常に?に置換されます。そのため、それを見越したコードを書く必要があります。

値の生成が複雑な場合

例えば複数の値を使う場合は一時変数で作っておきます。

val sqlsValues = values.map { s =>
  val fuga = s"fuga-${s.fuga}"
  sqls"""
    |(
    |  ${fuga},
    |  ${s.piyo}
    |)""".stripMargin
}

timestamp型を設定する場合

java.time.LocalDateTimeをtimestamp型に設定する場合、postgresql/h2互換の設定方法は::timestampサフィックスを追加します。

val sqlsValues = values.map { s =>
  sqls"""
    |(
    |  ${s.fuga},
    |  ${s.piyo},
    |  ${s.updated_at}::timestamp
    |)""".stripMargin