JDBC

Apps Script 可透過 JDBC 服務 (標準 Java Database Connectivity 技術的包裝函式) 連線至外部資料庫。JDBC 服務支援 Google Cloud SQL for MySQL、MySQL、Microsoft SQL Server 和 Oracle 資料庫。

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

Google Cloud SQL 資料庫

Google Cloud SQL 可讓您在 Google 雲端建立關聯式資料庫。請注意，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，其中 subname 是 MySQL 執行個體連線名稱，列於 Google Cloud 控制台的 Cloud SQL 執行個體「總覽」頁面。

如要連線至 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 服務連線。

建立其他資料庫連線

如要使用 Apps Script JDBC 服務建立資料庫連線，您必須在資料庫設定中授權這個資料來源的 IP 範圍。

設定好這些允許清單後，您就可以使用 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
    console.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
    console.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
    console.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
    console.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();
    console.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.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';
      }
      console.log(rowString);
    }

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

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

關閉連線

指令碼執行完畢後，JDBC 連線會自動關閉。(請注意，即使發出呼叫的 HTML 服務頁面保持開啟狀態，單一 google.script.run 呼叫仍會計為完整執行。)

不過，如果您知道在指令碼結束前，連線、陳述式或結果集已完成，建議呼叫 JdbcConnection.close()、JdbcStatement.close() 或 JdbcResultSet.close() 手動關閉這些項目。

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

​Google、Google Workspace 和其他相關符號及標誌均為 Google LLC 的商標。所有其他公司和產品名稱則是與個別公司關聯的商標。