GASのJDBCサービスでCloud SQLのデータベースにレコードを追加する方法

photo credit: Marc Wathieu Dust Dealers, Bruxelles via photopin (license)

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

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

前回の記事はこちら。

GASのJDBCサービスでCloud SQLにクエリを実行した結果を取り出す方法

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

GASでCloud SQLにクエリを実行した結果を取り出す方法をお伝えしました。

さて、データベースの値を取り出すだけでなく、レコードの追加や更新、削除などもしていきたいですよね…

ということで、今回はGASのJDBCサービスでCloud SQLのデータベースにレコードを追加する方法をお伝えします。

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

前回のおさらい
GASから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();
}

既にCloud SQLに存在している「guestbook」というデータベースの、「entries」というテーブルからすべてのレコードを取り出して、カラム「guestName」「content」の値を表示するというものです。

Cloud SQLのCloud Shellで2つほどレコードを追加していましたので、実行すると以下のように表示されました。

このデータベースについて、他の操作を実行していきましょう。

GASからCloud SQLのデータベースにレコードを追加する

今回は、Cloud SQLのデータベースへ新たなレコードを追加していきたいと思います。

レコードを追加するには、SQLのINSERT文を使うんでしたね。

INSERT INTO テーブル名 (カラム名1, カラム名2, …) VALUES (値1, 値2, …);

GCPのCloud SQLデータベースのテーブルにSQLでレコードを追加する方法

GASからJDBCサービスを使ってCloud SQLのデータベースにアクセスする方法をシリーズでお伝えしています。今回は、GCPのCloud SQLデータベースのテーブルにSQLでレコードを追加する方法です。

このSQLステートメントを実行する命令を、GASのJDBCサービスから探していけばよいわけですね。

更新系のSQLステートメントを実行する

今回実行しようとしている、INSERT文や、レコードを更新するUPDATE文、レコードを削除するDELETE文など、更新系のSQLを実行する命令として、executeUpdateメソッドがあります。

JdbcPreparedStatementオブジェクト.executeUpdate()

executeUpdateメソッドの対象は、JdbcPreparedStatementオブジェクトで、そこに既にセットされているSQLステートメントを実行するというものです。

ちなみに、整数の戻り値として、SQLの実行により更新された行数を返します。

JdbcPreparedStatementオブジェクトが変数statementに割り当てられているとすると、以下のように使用することができます。

var count = statement.executeUpdate();
Logger.log(count);

さて、SELECT文を実行するときには、JdbcStatementオブジェクトを使いました。

実は、INSERT文もJdbcStatementオブジェクトで実行できるのですが、実際は何が違うんでしょうか？

JdbcPreparedStatementオブジェクトとは

JdbcPreparedStatementオブジェクトとは、SQLステートメントを前もって準備しておくためのオブジェクトです。

具体的には以下のような表記をします。

INSERT INTO entries (guestName, content) values (?, ?)

命令の中に「?」記号が2つ含まれています。

この「?」記号は、プレースホルダーとして使用することができ、後から適宜プレースホルダーに値を入れ込んで使用することができます。

JdbcStatementオブジェクトとの違いは、このプレースホルダーがあるかどうかです。

複数のレコードについて更新系のSQL文を実行したいときには、プレースホルダーを使って効率的に実行できるので、JdbcPreparedStatementオブジェクトを使うと便利なのです。

JdbcPreparedStatementオブジェクトを生成する

JdbcPreparedStatementオブジェクトを生成するには、以下のprepareStatementメソッドを使います。

JdbcConnectionオブジェクト.prepareStatement(SQL)

引数SQLには、ひとつ以上の「?」記号を含むパラメータ化されたSQLステートメントを指定します。

前述のINSERT文を使うのであれば、以下のようなコードになりますね。

var statement = connection.prepareStatement('INSERT INTO entries (guestName, content) values (?, ?)');
// 何らかの処理
var count = statement.executeUpdate();
Logger.log(count);

プレースホルダーに値を設定する

続いて、SQL文を実行する前に、プレースホルダーに値を設定する必要がありますね。

JdbcPreparedStatementオブジェクトのプレースホルダーに文字列を設定するには、setStringメソッドを使います。

JdbcPreparedStatementオブジェクト.setString(パラメータインデックス, 文字列)

データ型によって、setIntメソッドや、setDateメソッドなど、異なるメソッドを使う必要がありますので、必要に応じて以下で調べてみてください。

Class JdbcPreparedStatement | Apps Script | Google for Developers

レコードをひとつ追加するとして、各カラムに対して以下のような文字列を格納するとしましょう。

guestName: Third guest
content: I just got here too!

すると、スクリプトは以下のようになります。

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

JdbcPreparedStatementオブジェクトを閉じる

JdbcStatementオブジェクトと同様、JdbcPreparedStatementオブジェクトも、使用後はcloseメソッドで明示的に閉じておきます。

JdbcPreparedStatementオブジェクト.close()

レコードの追加を実行を確認する

では、実際にコードを実行して、GASからCloud SQLへのレコード追加について動作を確認していきましょう。

まとめのコードは以下のとおりになります。

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

まず、writeOneRecord関数を実行してログを確認します。