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


select

photo credit: JohnSeb DSCN8475 via photopin (license)

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

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

前回の記事はこちら。

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

GASのJDBCサービスを使って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'); 
  
  //結果を取り出す処理

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

データベースURLを生成して、getCloudSqlConnectionメソッドで接続します。

続いて、SQLのクエリ「SELECT * FROM entries」を実行するというものですね。

その実行結果が、変数resultsに格納されるわけですが、その中身を取り出す処理はまだ作成していませんでした。

ということで、SQLクエリを実行した結果を取り出す処理を作成していきます。

JdbcResultSetオブジェクトとは

さて、11行目のexecuteQueryメソッドにより、変数resultsにはクエリを実行した結果であるJdbcResultSetオブジェクトが格納されます。

JdbcResultSetオブジェクトはテーブルのような構造のオブジェクトになっていて、以下のような動作をします。

  1. 「現在のレコード」を表すカーソルを持っている
  2. 最初のカーソルの位置は、最初のレコードの前を指し示している
  3. nextメソッドを実行するたびに、次のレコードにカーソルが移動する
  4. 現在のレコードに対して、getStringメソッドや、getIntメソッドで、指定のカラムの値を取リ出すことができる
  5. 最終レコードの次の行にカーソルが移動したとき、nextメソッドはfalseを返す

以下でひとつひとつ詳しく見ていきましょう。

nextメソッドでJdbcResultSetオブジェクトをループする

JdbcResultSetオブジェクトのnextメソッドは、カーソルを次のレコードへ移動します。

JdbcResultSetオブジェクト.next()

初期状態でカーソルは最初のレコードの前にありますので、nextメソッドを1回実行したときに、1つ目のレコードを指し示します。

nextメソッドで移動したレコードが有効であれば戻り値としてtrueを返します。

最終レコードのときにnextメソッドを実行すると、そこにはレコードはもうありませんので、戻り値としてfalseを返します。

これらの特性を利用して、JdbcResultSetオブジェクトが表すテーブルに対して、以下のようにwhile文でループ処理を構成できます。

while (results.next()) {
  //処理
}

現在のレコードからデータを取り出す

あとは、ループの中で、現在カーソルがあるレコードについて、データを取り出していけばよいわけですね。

現在カーソルがあるレコードについて、指定のカラムのデータを取り出すには、getStringメソッドや、getIntメソッドを使用します。

JdbcResultSetオブジェクト.getString(カラム名)
JdbcResultSetオブジェクト.getInt(カラム名)

いずれも、カラム名で指定したカラムのデータを取り出しますが、getStringメソッドなら文字列で、getIntメソッドなら整数で取り出します。

他にもデータ型に応じたメソッドが用意されているので、必要に応じて調べてみてください。

Class JdbcResultSet  |  Apps Script  |  Google Developers

つまり、以下のようにすれば、JdbcResultSetオブジェクトの現在のレコードについて、カラムentryIDの値を整数で、カラムguestNameとcontentの値を文字列で取得し、ログに出力できます。

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

}

クエリで取り出したテーブルを表示する

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

では、実行して動作を確認しましょう。

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

実行してログを確認すると、以下のように各レコードのデータを取り出すことができます。

GASのJDBCサービスでクエリを実行した結果を出力

まとめ

以上、GASのJDBCサービスでCloud SQLにクエリを実行した結果を取り出す方法をお伝えしました。

SELECT文では、条件や並び替えなど、いろいろなパターンのクエリを作れます、

今回のスクリプトをベースにそれらどんなパターンでも応用できます。

さて、次回以降ですが、SELECT文ではなく、他のSQLを実行していく方法を見ていきましょう。

GASのJDBCサービスでCloud SQLのデータベースにレコードを追加する方法
GASからJDBCサービスを使ってCloud SQLのデータベースにアクセスする方法をシリーズでお伝えしています。今回はGASのJDBCサービスでCloud 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をコピーしました