GASのJDBCサービスでCloud SQLのデータベースに複数のSQLを一括で実行する方法

photo credit: verchmarco Sonnenblumenkerne geschält und in der Schale mit einer frischen Sonnenblume auf Sackleinen via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

GASからJDBCサービスを使ってCloud SQLのデータベースにアクセスする方法をシリーズでお伝えしています。

前回の記事はこちら。

GASのJDBCサービスを使ってCloud SQLのデータベースのレコードを更新・削除をする方法

GASからJDBCサービスを使ってCloud SQLのデータベースにアクセスする方法をシリーズでお伝えしています。今回はGASのJDBCサービスを使ってCloud SQLのデータベースのレコードを更新・削除をする方法です。

GASからCloud SQLへレコード更新または削除のSQLを実行する方法をお伝えしました。

さて、これまでのレコード追加、更新そして削除は一行ずつ実行していましたが、当然複数のレコードを操作したいこともありますよね。

そのような時に使用するのが「バッチ処理」です。

今回は、GASのJDBCサービスでCloud SQLのデータベースに複数のSQLを実行する方法です。

では、行ってみましょう！

これまでのおさらい
SQL文を一括で実行する
1. バッチにパラメータのセットを追加する
2. バッチに追加されたセットに対してSQL文をまとめて実行する
Cloud SQLのデータベースへ複数レコード追加の一括実行を確認する
まとめ
連載目次：GASのJDBCサービスでCloud SQLのDBを操作する

これまでのおさらい

では、これまでのおさらいです。

今回は前々回の記事に戻りまして、そこからスタートしたいと思います。

前々回で紹介したコードはこちらです。

var connectionName = '*****データベース接続名*****'; //Instance_connection_name
var userName = '*****ユーザー名*****'; //user_name
var password = '*****パスワード*****'; //'user_password'
var databaseName = 'guestbook'; //database_name

var url = 'jdbc:google:mysql://' + connectionName + '/' + databaseName;

function readFromTable() {
  var connection = Jdbc.getCloudSqlConnection(url, userName, password);
  var statement = connection.createStatement();
  var results = statement.executeQuery('SELECT * FROM entries'); 
  
  while (results.next()) {
    var entryID = results.getInt('entryID');
    var guestName = results.getString('guestName');
    var content = results.getString('content');
    
    Logger.log('%s t %s t %s', entryID, guestName, content);
  }

  results.close();
  statement.close();
  connection.close();
}

function writeOneRecord() {
  var connection = Jdbc.getCloudSqlConnection(url, userName, password);

  var statement = connection.prepareStatement('INSERT INTO entries (guestName, content) values (?, ?)');
  statement.setString(1, 'Third Guest');
  statement.setString(2, 'I just got here too!');
  var count = statement.executeUpdate();
  Logger.log(count);
  
  statement.close();
  connection.close();
}

関数readFromTableは、Cloud SQLのデータベース「guestbook」の、「entries」というテーブルの内容をログ出力するものです。

また、関数writeOneRecordは、その「entries」というテーブルに一行追加するという処理です。

それで、このデータ追加ですが、一行ではなくて複数のレコードを追加したいとき、どうすれば良いでしょうか？

今回は、まとめてたくさんのSQLを実行する際の作り方について紹介します。

SQL文を一括で実行する

さて、レコードの追加は更新系のSQLの実行です。

更新系のSQLステートメントを実行する際には、prepareStatementメソッドにプレースホルダー付きのSQL文を渡して、JdbcPreparedStatementオブジェクトを生成するのでした。

JdbcPreparedStatementオブジェクトには、ひとつのSQL文に対して、プレースホルダーの値のセットを複数持たせて置いて、一括で実行できるしくみがあります。

いわゆる「バッチ処理」というやつですね。

バッチにパラメータのセットを追加する

JdbcPreparedStatementオブジェクトのバッチにパラメータのセットを追加するには、addBatchメソッドを使います。

JdbcPreparedStatementオブジェクト.addBatch()

例えば、JdbcPreparedStatementオブジェクトが変数statementに割り当てられている場合、

statement.setString(1, 'Name ' + i);
statement.setString(2, 'Hello, world ' + i);
statement.addBatch();

とすれば、プレースホルダーに設定したSQL文がバッチに追加されるわけです。

バッチに追加されたセットに対してSQL文をまとめて実行する

JdbcPreparedStatementオブジェクトのバッチにパラメータのセットが追加されているのであれば、ひとつのSQL文に対して各セットを当てはめたものを、まとめて一括実行することができます。

バッチに追加されたセットに対してSQL文をまとめて実行するには、executeBatchメソッドを使います。

JdbcPreparedStatementオブジェクト.executeBatch()

戻り値は、各セットに対して更新されたレコード数の配列です。

例えば、以下のような使い方になります。

var statement = conn.prepareStatement('INSERT INTO entries (guestName, content) values (?, ?)');
  for (var i = 0; i < 5; i++) {
    statement.setString(1, 'Name ' + i);
    statement.setString(2, 'Hello, world ' + i);
    statement.addBatch();
  }
var batch = stmt.executeBatch();

Cloud SQLのデータベースへ複数レコード追加の一括実行を確認する

では、複数レコード追加の一括実行についてその動作を確認してみましょう。

まず、実行前の状態が以下だとします。

それで、これまでの内容をもとに複数レコードを一括で追加する処理として、以下の関数writeManyRecordsとしました。

function writeManyRecords() {
  var connection = Jdbc.getCloudSqlConnection(url, userName, password);

  var statement = connection.prepareStatement('INSERT INTO entries (guestName, content) values (?, ?)');
  for (var i = 0; i < 5; i++) {
    statement.setString(1, 'Name ' + i);
    statement.setString(2, 'Hello, world ' + i);
    statement.addBatch();
  }

  var batch = statement.executeBatch();
  Logger.log(batch);

  statement.close();
  connection.close();
}

これを実行してログを確認すると以下のような内容でした。