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

photo credit: kookykrys 20170129_170852 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のデータベースのレコードを更新・削除をする方法です。

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

前回のおさらい
CRUDとは
UPDATE文でレコードを更新する
1. レコードを更新するスクリプト
2. レコード更新の実行確認
DELETE文でレコードを削除する
1. レコードを削除するスクリプト
2. レコード削除の実行確認
まとめ
連載目次：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と、関数writeOneRecordの２つの関数がありますが、いずれもCloud SQLの「guestbook」というデータベースの、テーブル「entries」を操作するものです。

関数readFromTableはテーブルの各カラムのデータをSELECT文で取得してログ出力するもの、関数writeOneRecordはテーブルにベタ書きのレコードを1行追加するというものです。

CRUDとは

「CRUD」という言葉があります。

これは、データベースアプリケーションが持つべき４つの基本機能のイニシャルを並べた用語で、その４つとは以下のとおりです。

用語	操作	SQL
Create	生成	INSERT
Read	読み取り	SELECT
Update	更新	UPDATE
Delete	削除	DELETE

SQLでいうと、INSERT文とSELECT文はこれまでに紹介してきました。

あと、残るUPDATE文によるレコードの更新と、DELETE文によるレコードの削除ができれば、基本機能は押さえることができるということになります。

UPDATE文でレコードを更新する

レコードの値を変更するには、SQLのUPDATE文を使います。

UPDATE テーブル名 SET カラム名1=値1, カラム名2=値2, … WHERE 条件

WHEREとその後の「条件」の部分を「WHERE句」といい、それにマッチしたレコードを更新の対象とします。WHERE句を省略した場合は、すべてのレコードが対象になります。

そして対象のレコードに対して、SET以降に指定した「カラム名=値」に従って、値を上書きします。この部分を「SET句」といいます。

レコードを更新するスクリプト

GASでCloud SQLのデータベースに対してSQLのUPDATE文を実行するスクリプトを作りました。

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

  var statement = connection.prepareStatement('UPDATE  entries SET guestName=?, content=? WHERE entryID=?');
  statement.setString(1, 'third guest');
  statement.setString(2, 'I just got here too!!!!!');
  statement.setInt(3, 3);

  var row = statement.executeUpdate();
  Logger.log(row);
  
  statement.close();
  connection.close();
}

WHERE句の条件には、プライマリーキーとしていたカラム「entryID」を設定すると、一意に決まりますね。

JdbcPreparedStatementオブジェクトの３つのプレースホルダーに設定する場合は、INT型でしたからsetIntメソッドを使用しています。

レコード更新の実行確認

では、レコード更新の実行確認をしていきます。

上記、updateOneRecord関数を実行してから、readFromTable関数を実行して、テーブル内を確認しました。

以下のようにログが出力されます。

条件に指定したentryIDが3のレコードの値が更新されていることを確認できます。

DELETE文でレコードを削除する

続いて、レコードの削除について見ていきましょう。

レコードを削除するには、SQLのDELETE文を使います。

DELETE FROM テーブル名 WHERE 条件

WHERE句の条件にマッチしたレコードを削除します。WHERE句を省略した場合は、すべてのレコードが対象になります。

レコードを削除するスクリプト

GASでCloud SQLのデータベースに対してSQLのDELETE文を実行するスクリプトを作りました。

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

  var statement = connection.prepareStatement('DELETE FROM entries WHERE entryID=?');
  statement.setInt(1, 2);
  
  var row = statement.executeUpdate();
  Logger.log(row);
  
  statement.close();
  connection.close();
}

JdbcPreparedStatementオブジェクトのプレースホルダーには1つだけなので、setIntメソッドをひとつ実行すれば、SQLが完成します。