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

update

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

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

スポンサーリンク

前回のおさらい

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

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の2つの関数がありますが、いずれもCloud SQLの「guestbook」というデータベースの、テーブル「entries」を操作するものです。

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

CRUDとは

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

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

用語 操作 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オブジェクトの3つのプレースホルダーに設定する場合は、INT型でしたからsetIntメソッドを使用しています。

レコード更新の実行確認

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

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

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

GASでCloud SQLのデータベースにUPDATE文を実行した

条件に指定した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が完成します。

レコード削除の実行確認

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

上記のdeleteOneRecord関数を実行してから、readFromTable関数を実行して、テーブル内を確認してみましょう。

GASでCloud SQLのデータベースにDELETE文を実行した

setIntメソッドで指定した、entryIDが2のレコードが削除されていることが確認できますね。

まとめ

以上、GASのJDBCサービスを使ってCloud SQLのデータベースのレコードを更新・削除をする方法をお伝えしました。

SQL文を作るのに注意が必要ですが、基本的には同じようなスクリプトを使い回せますね。

さて、次回は更新系の複数のSQL文をまとめて実行する方法について見ていきましょう。

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