バルクアップデート ~ MySQL × MyBatis × Java(Spring Framework) ~

バルクインサートはパッと書けるけど、バルクアップデートって・・・
あれ？ん？

本記事はこんな方におすすめ

・プログラム経験あり
・Javaコーディング経験あり
・何かしらのRDBMSでSQL文を書いた経験あり
・MyBatis使い始め
(最近KintoneやっててSQLから離れてしまった人)

はじめに

バルクアップデートとは、1つのSQL文で、複数レコードを一気に更新するUPATEを行う事です。
バルクインサートはよく聞くので、パッとInsert文が想像できるとして、
バルクアップデートとなると・・・あれ？どうやって書くんだ？となりませんか？
私はつい最近、この状況になってしまったのです。。。
という事で、今回はETL()とFIELD()によるバルクアップデート文と、MyBatis × Java(Spring Framework)での実装について執筆します。

単純な複数レコードアップデート文(Java)

もしSQL1文で実行しなかったら以下の様なイメージになるはずです。
for文(foreach)でぐるぐる回して毎回update()処理をコール・・・

for (hoge) {
   update(); // UPDATE `example` SET name = 'xxx' WHERE id = xx;
}

バルクアップデート文(SQL)

上述のアップデートを、1SQL文で記述すると以下になります。

UPDATE `example` SET 
name = ELT(FIELD(id,1,3,5),'Apple','Banana','Orange') 
WHERE id IN (1,3,5)

解説

さぁ、それでは簡単に解説していきます。

ETL(数値,要素1,要素2,・・・要素n))

・引数1 : 数値、引数2以降 : 要素n
・引数1の数値は、引数2以降の位置を示しており、該当位置の要素を結果として返す

FIELD(検索文字列,要素1,要素2,・・・,要素n)

・引数1 : 検索文字列、引数2以降 : 検索される文字列
・引数1の検索文字列に一致した文字列がある要素の位置を結果として返す

SQLの解説
上記のETL()とFIELD()の動きを踏まえて、SQLを解説していきます。

1. WHERE id IN (1,3,5)でidが1,3,5のレコードが抽出される
2. FIELD(id,1,3,5)により、id=1の時のFIELD(id,1,3,5)の結果は、1である
3. ELT(FIELD(id,1,3,5),'Apple','Banana','Orange')
 → ELT(1,'Apple','Banana','Orange')となる
4. ELT(1,'Apple','Banana','Orange')の結果は、Appleとなる
5. SET name = Appleとなる
上記同様の理屈で、id=3 → SET name = Banana, id=5 → SET name = Orange となる

idの抽出条件を可変にすると(増やすと)、複数のアップデート対象に対して、1つのSQL文で一気にアップデートを実施します!

MyBatis × Java(Spring Framework)

アップデート文だけ書いてもしょうがないので、今回はバルクアップデート文をJavaとMyBatisを利用して生成していきます。

java

List<Map<String,Object>> mapList = mapper.getFruite();
List<Fruite> fruiteList = new ArrayList<Fruite>();
for (Map<String,Object> map : mapList) {			
   fruite = new Fruite();
   fruite.setId((Integer)map.get("id")); // 1,3,5
   fruite.setName(map.get("name").toString()); // Apple,Banana,Orange
   fruiteList.add(registrationSubmission);
}
mapper.bulkUpdate(fruiteList);

1. Fruiteのid/nameを取得します
2. Fruite(entityクラス)のListを作成します
3. 作成したリストに取得したFruiteのid/nameをセットします
4. 複数件セットされたリストをMapperのbulkUpdateに渡します

Mapper(xml)

・parameterTypeには"java.util.List"とする
・可変部分をパラメータでループさせる

<update id="bulkUpdate" parameterType="java.util.List">
   UPDATE update_table
   SET name = ELT(FIELD(id,
   <foreach collection="list" item="list" separator=",">
       #{list.id}
   </foreach>
   ),
   <foreach collection="list" item="list" separator=",">
       #{list.name}
   </foreach>
   )
   WHERE id IN (
   <foreach collection="list" item="list" separator=",">
       #{list.id}
   </foreach>
   );
</update>

これだけで完成です！！

そもそもなぜバルクアップデートにするのか？

・DB(Mysql)との通信頻度を抑える事が一番の目的です
・SQL文を発行するたびに通信が発生している為、SQL構文の解釈、
　データ抽出、処理・・・の一連の流れを1件ごとに実施していると、
　処理が重くなってくる

Web画面などからまとめてアップデートしたいという様な場合は、ほとんどのケースは大量データ件数ではない為、先に記載した単純なループ処理で1件ごとにアップデートしても、さほど問題にならないでしょう。ただし、大量データ件数の場合はバルクアップデートとした方が良いでしょう。

最後に

バルクアップデートは必須ではないのですが、大量データ件数に対するアップデートを行う際には、かなりの効果が得られます。
ちょっと馴染みのないSQLだからと言って遠ざけるほどの内容でもなく、SQLなので1回でも書いたのなら、大量データ件数でなくても当たり前の様に複数件レコードへのアップデートを行う際には利用すると良いでしょう。

kintoneはkintone Utility for JavaScriptでバルクアップデート的な事もAPI一発なので楽チンだなぁと改めて実感しました！