JDBC

Apps Script は、標準の Java Database Connectivity テクノロジーのラッパーである JDBC サービスを介して外部データベースに接続できます。JDBC サービスは、Google Cloud SQL MySQL、MySQL、Microsoft SQL Server、Oracle データベースをサポートしています。

JDBC で外部データベースを更新するには、スクリプトがデータベースへの接続を開き、SQL ステートメントを送信して変更する必要があります。

Google Cloud SQL データベース

Google Cloud SQL を使用すると、Google のクラウド内に存在するリレーショナル データベースを作成できます。Apps Script とは異なり、Cloud SQL は無料ではありません

Google Cloud SQL インスタンスを作成するには、Cloud SQL クイックスタートに記載されている手順を行います。

Google Cloud SQL 接続の作成

Apps Script の JDBC サービスを使用して Google Cloud SQL データベースとの接続を確立する方法は 2 つあります。

以下では、これらの方法について説明します。どちらも有効ですが、2 番目の方法では、データベースにアクセスするための IP 範囲のセットを許可リストに登録する必要があります。

このメソッドは、Jdbc.getCloudSqlConnection(url) メソッドを使用して Google Cloud SQL MySQL インスタンスへの接続を作成します。データベースの URL の形式は jdbc:google:mysql://subname です。ここで、subname は、Google Cloud Platform Console で、Cloud SQL インスタンスの [概要] ページにある MySQL インスタンス接続名です。

Cloud SQL SQL Server に接続するには、Jdbc.getConnection(url) をご覧ください。

Jdbc.getConnection(url) の使用

この方法を使用するには、特定の CIDR IP アドレス範囲を許可リストに登録して、Apps Script サーバーがデータベースに接続できるようにする必要があります。スクリプトを実行する前に、次の手順を行います。

  1. Google Cloud SQL インスタンスで、この データソースから一度に 1 つずつ IP 範囲を承認します。

  2. データベースに割り当てられた URL をコピーします。形式は jdbc:mysql:subname です。

これらの IP 範囲を許可リストに登録したら、Jdbc.getConnection(url) メソッドのいずれかと上記の URL を使用して、Google Cloud SQL インスタンスへの接続を作成できます。

その他のデータベース

すでに独自の MySQL、Microsoft SQL Server、または Oracle データベースがある場合は、Apps Script の JDBC サービスを介してデータベースに接続できます。

他のデータベース接続の作成

JDBC サービスを使用してデータベース接続を作成するには、データベース設定で特定の IP 範囲を許可リストに登録し、Apps Script からアクセスできるようにする必要があります。これらは、許可リストに登録する必要があるアドレス範囲です。

これらの許可リストを設定すると、Jdbc.getConnection(url) メソッドのいずれかとデータベースの URL を使用して、データベースへの接続を作成できます。

サンプルコード

次のサンプルコードでは、Google Cloud SQL データベースに接続し、Jdbc.getCloudSqlConnection(url) メソッドを使用してデータベース接続を作成しています。他のデータベースの場合は、Jdbc.getConnection(url) メソッドを使用してデータベース接続を作成する必要があります。

JDBC メソッドの詳細については、JDBC の Java ドキュメントをご覧ください。

データベース、ユーザー、テーブルを作成する

ほとんどのデベロッパーは MySQL コマンドライン ツールを使用して、データベース、ユーザー、テーブルを作成します。ただし、次に示すように Apps Script でも同じことができます。スクリプトが少なくとも root としてデータベースに接続する必要をなくすため、他のユーザーを 1 人以上作成することをおすすめします。

service/jdbc.gs
/**
 * Create a new database within a Cloud SQL instance.
 */
function createDatabase() {
  try {
    const conn = Jdbc.getCloudSqlConnection(instanceUrl, root, rootPwd);
    conn.createStatement().execute('CREATE DATABASE ' + db);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    Logger.log('Failed with an error %s', err.message);
  }
}

/**
 * Create a new user for your database with full privileges.
 */
function createUser() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, root, rootPwd);

    const stmt = conn.prepareStatement('CREATE USER ? IDENTIFIED BY ?');
    stmt.setString(1, user);
    stmt.setString(2, userPwd);
    stmt.execute();

    conn.createStatement().execute('GRANT ALL ON `%`.* TO ' + user);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    Logger.log('Failed with an error %s', err.message);
  }
}

/**
 * Create a new table in the database.
 */
function createTable() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
    conn.createStatement().execute('CREATE TABLE entries ' +
      '(guestName VARCHAR(255), content VARCHAR(255), ' +
      'entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));');
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    Logger.log('Failed with an error %s', err.message);
  }
}

データベースへの書き込み

以下の例は、1 つのレコードをデータベースに書き込む方法と 500 レコードのバッチを書き込む方法を示しています。一括処理にはバッチ処理が不可欠です。

パラメータ化されたステートメント(変数が ? で表される)の使用にも注意してください。SQL インジェクションを防ぐには、パラメータ化されたステートメントを使用して、ユーザー提供データをすべてエスケープする必要があります。

service/jdbc.gs
/**
 * Write one row of data to a table.
 */
function writeOneRecord() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);

    const stmt = conn.prepareStatement('INSERT INTO entries ' +
      '(guestName, content) values (?, ?)');
    stmt.setString(1, 'First Guest');
    stmt.setString(2, 'Hello, world');
    stmt.execute();
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    Logger.log('Failed with an error %s', err.message);
  }
}

/**
 * Write 500 rows of data to a table in a single batch.
 */
function writeManyRecords() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
    conn.setAutoCommit(false);

    const start = new Date();
    const stmt = conn.prepareStatement('INSERT INTO entries ' +
      '(guestName, content) values (?, ?)');
    for (let i = 0; i < 500; i++) {
      stmt.setString(1, 'Name ' + i);
      stmt.setString(2, 'Hello, world ' + i);
      stmt.addBatch();
    }

    const batch = stmt.executeBatch();
    conn.commit();
    conn.close();

    const end = new Date();
    Logger.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    Logger.log('Failed with an error %s', err.message);
  }
}

データベースからの読み取り

この例では、データベースから多数のレコードを読み取り、必要に応じて結果セットをループする方法を示します。

service/jdbc.gs
/**
 * Read up to 1000 rows of data from the table and log them.
 */
function readFromTable() {
  try {
    const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
    const start = new Date();
    const stmt = conn.createStatement();
    stmt.setMaxRows(1000);
    const results = stmt.executeQuery('SELECT * FROM entries');
    const numCols = results.getMetaData().getColumnCount();

    while (results.next()) {
      let rowString = '';
      for (let col = 0; col < numCols; col++) {
        rowString += results.getString(col + 1) + '\t';
      }
      Logger.log(rowString);
    }

    results.close();
    stmt.close();

    const end = new Date();
    Logger.log('Time elapsed: %sms', end - start);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    Logger.log('Failed with an error %s', err.message);
  }
}

接続を閉じる

JDBC 接続は、スクリプトの実行が終了すると自動的に閉じます。(呼び出しを行った HTML サービスのページが開かれている場合でも、1 回の google.script.run 呼び出しは実行全体としてカウントされることに注意してください)。

それでも、スクリプトの終了前に接続、ステートメント、または結果セットを完了している場合は、JdbcConnection.close()JdbcStatement.close()、または JdbcResultSet.close() を呼び出して、それらを手動で終了することをおすすめします。

アラートまたはプロンプト ダイアログを表示すると、開いている JDBC 接続も終了します。ただし、カスタム メニューやダイアログ、カスタム コンテンツのサイドバーなど、他の UI 要素は表示されません。