JDBC

Apps Script 可透過 JDBC 服務連線至外部資料庫,這個套件是標準 Java 資料庫連線技術的包裝函式。JDBC 服務支援 Google Cloud SQL MySQL、MySQL、Microsoft SQL Server 和 Oracle 資料庫。

如要使用 JDBC 更新外部資料庫,指令碼必須開啟與資料庫的連線,然後傳送 SQL 陳述式進行變更。

Google Cloud SQL 資料庫

Google Cloud SQL 可讓您建立儲存於 Google 雲端的關聯資料庫。請注意,與 Apps Script 不同,Cloud SQL 完全免費

您可以按照 Cloud SQL 快速入門導覽課程中列出的步驟建立 Google Cloud SQL 執行個體。

建立 Google Cloud SQL 連線

有兩種方式可以透過 Apps Script' JDBC 服務與 Google Cloud SQL 資料庫建立連線:

這些方法說明如下。兩者都有效,但第二個方法會要求您將一組 IP 範圍加入許可清單才能存取資料庫。

這個方法會使用 Jdbc.getCloudSqlConnection(url) 方法建立連線至 Google Cloud SQL MySQL 執行個體。資料庫網址格式為 jdbc:google:mysql://subname,其中 subnameGoogle Cloud Platform Console 中的 Cloud SQL 執行個體「總覽」頁面列出的 MySQL 執行個體連線名稱

如要連線至 Cloud SQL SQL Server,請參閱 Jdbc.getConnection(url)

使用 Jdbc.getConnection(url)

如要使用這個方法,您必須將特定 CIDR IP 位址範圍加入許可清單,以便 Apps Script 的伺服器連線至資料庫。執行指令碼之前,請先完成下列步驟:

  1. 在 Google Cloud SQL 執行個體中,從這個資料來源逐一授權 IP 範圍

  2. 複製指派給資料庫的網址,格式應為 jdbc:mysql:subname

加入許可清單這些 IP 範圍後,您可以使用其中一種 Jdbc.getConnection(url) 方法及您在上方複製的網址來建立與 Google Cloud SQL 執行個體的連線。

其他資料庫

如果您已有 MySQL、Microsoft SQL Server 或 Oracle 資料庫,您可以透過 Apps Script' 的 JDBC 服務連線至這個資料庫。

建立其他資料庫連線

如要使用 JDBC 服務建立資料庫連線,您必須將資料庫設定中的特定 IP 範圍加入許可清單,允許 Apps Script 存取。您必須設定這些範圍才能加入許可清單

備妥這些許可清單後,您可以使用其中一種 Jdbc.getConnection(url) 方法和資料庫的網址建立與資料庫的連線。

程式碼範例

下列程式碼範例假設您連線至 Google Cloud SQL 資料庫,並使用 Jdbc.getCloudSqlConnection(url) 方法建立資料庫連線。至於其他資料庫,您必須使用 Jdbc.getConnection(url) 方法建立資料庫連線。

如要進一步瞭解 JDBC 方法,請參閱 JDBC 的 Java 說明文件

建立資料庫、使用者和資料表

大多數開發人員會使用 MySQL 指令列工具來建立資料庫、使用者和資料表。不過,您也可以在 Apps Script 中執行相同操作,如下所示。最好建立至少另一位使用者,這樣指令碼不需總是像 root 一樣連結至資料庫。

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

寫入資料庫

以下範例說明如何將單一記錄和批次 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 服務頁面保持開啟,但單一 google.script.run 呼叫仍會計為完整執行作業)。

然而,如果您知道在指令碼結尾之前,已透過連線、陳述式或結果集執行連線,建議呼叫 JdbcConnection.close()JdbcStatement.close()JdbcResultSet.close(),手動關閉。

顯示快訊或提示對話方塊也會終止所有開啟的 JDBC 連線。不過,其他顯示 UI 元素 (例如自訂選單或包含自訂內容的對話方塊和側欄) 則不會。