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

record

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のデータベースにレコードを追加する方法をお伝えします。

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

スポンサーリンク

前回のおさらい

前回作成したコードはこちらです。

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のJDBCサービスでクエリを実行した結果を出力

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

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関数を実行してログを確認します。

GASからCloud SQLに更新系SQLを実行した行数

実行の結果はここでは確認できませんが、SQLの実行結果として1行の更新を行ったということは確認できます。

続いて、前回作成したreadFromTable関数を実行して、テーブルの中身を確認してみましょう。

GASからCloud SQLのデータベースにレコードを追加した結果

INSERT文による3つ目のデータが追加されていることが確認できますね。

まとめ

以上、GASのJDBCサービスでCloud SQLのデータベースにレコードを追加する方法をお伝えしました。

おそらく、大きく分けてSELECT文によるクエリを実行する方法と、今回紹介した更新系SQL文を実行する方法の2パターンを使うことで、ほぼ対応できるかなと思っています。

なので、いくつか覚えることがありますが、頑張って使いこなしていきましょう!

次回は、他の更新系のSQLを実行していきましょう。

どうぞお楽しみに!

連載目次:GASのJDBCサービスでCloud SQLのDBを操作する

GASのデータベースの多くはスプレッドシートを使用しますが、ちゃんとしたデータベースを使用したい場合があります。そこで、JBDCサービスを使ってCloud SQLを使用する方法をシリーズでお伝えしていきます。
  1. GASのデータベースとして、なぜCloud SQLが有効なのか?その概要とメリット
  2. GASでデータベースとして使用するCloud SQLインスタンスを作成する方法
  3. GCPのCloud Shellを使ってCloud SQLインスタンスに接続する方法
  4. GCPのCloud SQLインスタンスにSQL文でデータベースを作成する方法
  5. GCPのCloud SQLデータベースにSQLでテーブルを作成する方法
  6. GCPのCloud SQLデータベースのテーブルにSQLでレコードを追加する方法
  7. GASのJDBCサービスでCloud SQLデータベースに接続をする方法
  8. GASのJDBCサービスでCloud SQLデータベースにクエリを実行する方法
  9. GASのJDBCサービスでCloud SQLにクエリを実行した結果を取り出す方法
  10. GASのJBDCサービスでCloud SQLのデータベースにレコードを追加する方法
  11. GASのJDBCサービスでCloud SQLのデータベースに複数のSQLを一括で実行する方法
タイトルとURLをコピーしました