GASのJDBCサービスでトランザクション処理を行う方法

photo credit: Tiomax80 Here’s your change! via photopin (license)

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

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

前回の記事はこちら。

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

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

Cloud SQLのデータベースに対して、バッチ処理で複数の更新系SQLを実行する方法をお伝えしました。

ただ、たっっくさんのSQLをバッチで処理するときに、途中でエラーなどの問題が起きた時…データベースどうなっちゃうんですかね？

そんな心配を払拭する「トランザクション処理」というテクニックがあります。

ということで、今回はGASのJDBCサービスでトランザクション処理を行う方法をお伝えします。

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

前回のおさらい
トランザクション処理とは
自動コミットモードをオフにする
データベースへの一連の変更を確定する
トランザクション処理を加えた更新処理
まとめ
連載目次：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 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();
}

関数readFromTableは、Cloud SQLのデータベース「guestbook」内に作成したテーブル「entries」のレコードをログ出力するというものです。

関数writeManyRecordsは、そのテーブル「entries」に、複数のレコードを一括して追加するバッチ処理を行うものです。

トランザクション処理とは

前述で紹介したコードで、実行されるSQLは5件だけですが、これが何百件とあった場合のことを考えてみましょう。

バッチでまとめているとはいえ、SQLステートメントが逐一実行され、その都度データベースに変更が加わります。

その途中で、何らかの不可抗力が発生し、中断してしまったときを考えましょう。

すると、作業を途中から再開しようとしても「あれ？どこまで更新作業進めたっけ？」となってしまいます。

そのような問題を解決するのがトランザクション処理です。

トランザクション処理とは、データベースを利用するシステムにおいて、処理の一貫性を保証するために、関連する一連の処理全体を一個の処理単位として管理する仕組みのことである。

引用: トランザクション処理|IT用語辞書バイナリ

つまり、複数のSQLステートメント実行が全部成功した時だけ、データベースの変更を確定するというしくみです。

バッチ処理のときには、ぜひ使うべきですね。

自動コミットモードをオフにする

では、トランザクション処理の方法について見ていきましょう。

まず、デフォルトではSQLステートメントがひとつ実行されるたびに、その変更が確定する、「自動コミットモード」がオンになっています。

トランザクション処理を使用する場合、この「自動コミットモード」をオフにする必要があります。

それを切り替えるのが、JdbcConnectionオブジェクトのsetAutoCommitメソッドです。

JdbcConnectionオブジェクト.setAutoCommit(自動コミット)

引数「自動コミット」には、モードをオンにするかどうかをブール値で指定します。

ですから、冒頭のコードでは、JdbcConnectionオブジェクトを生成した直後の、27行目に以下の命令を差し込めば、それ以降の処理について自動コミットがなされないようになります。

connection.setAutoCommit(false);

データベースへの一連の変更を確定する

一連のSQLステートメントの実行が完了した時点で、そのすべての変更を確定するという手順を「コミット」といいます。

そのための命令が、JdbcConnectionオブジェクトのcommitメソッドです。

JdbcConnectionオブジェクト.commit()

つまり、37行目の後に以下命令を入れれば、その時点で保留されていた変更がデータベースに確定されるということになります。

connection.commit();

トランザクション処理を加えた更新処理

では、トランザクション処理を加えたコードのまとめです。

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 writeManyRecords() {
  var connection = Jdbc.getCloudSqlConnection(url, userName, password);
  connection.setAutoCommit(false);

  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);

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

実行結果は前回のものと同様ですが、一連の処理が成功した時点でデータベースに変更が確定されています。