JDBC

使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

Apps 脚本可以通过 JDBC 服务(标准 Java 数据库连接技术的封装容器)连接到外部数据库。JDBC 服务支持 Google Cloud SQL MySQL、MySQL、Microsoft SQL Server 和 Oracle 数据库。

如需使用 JDBC 更新外部数据库,您的脚本必须打开与数据库的连接,然后通过发送 SQL 语句进行更改。

Google Cloud SQL 数据库

借助 Google Cloud SQL,您可以创建位于 Google 云端的关系型数据库。请注意,与 Apps 脚本不同,Cloud SQL 不是免费的

您可以按照 Cloud SQL 快速入门中列出的步骤创建 Google Cloud SQL 实例。

创建 Google Cloud SQL 连接

您可以通过以下两种方式使用 Apps 脚本的 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 脚本的服务器可以连接到您的数据库。在运行脚本之前,请完成以下步骤:

  1. 在您的 Google Cloud SQL 实例中,向 IP 范围授权(一次使用此数据源)。

  2. 复制分配给您的数据库的网址;它应采用 jdbc:mysql:subname 格式。

将这些 IP 范围列入许可名单后,您可以使用任一 Jdbc.getConnection(url) 方法和上面复制的网址创建与 Google Cloud SQL 实例的连接。

其他数据库

如果您已有自己的 MySQL、Microsoft SQL Server 或 Oracle 数据库,可以通过 Apps 脚本的 JDBC 服务连接到该数据库。

创建其他数据库连接

为了使用 JDBC 服务创建数据库连接,您必须在数据库设置中将特定 IP 范围列入许可名单,以允许 Apps 脚本访问该服务。这些是您需要列入许可名单的地址范围。

将这些许可名单添加到适当位置后,您就可以使用其中一种 Jdbc.getConnection(url) 方法和数据库的网址来创建与数据库的连接。

示例代码

以下示例代码假定您连接到 Google Cloud SQL 数据库,并使用 Jdbc.getCloudSqlConnection(url) 方法创建数据库连接。对于其他数据库,您必须使用 Jdbc.getConnection(url) 方法创建数据库连接。

如需详细了解 JDBC 方法,请参阅有关 JDBC 的 Java 文档

创建数据库、用户和表

大多数开发者使用 MySQL 命令行工具来创建数据库、用户和表。不过,您也可以在 Apps 脚本中执行相同的操作,如下所示。建议您至少再创建一个用户,这样您的脚本就不必始终以 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 连接。不过,其他显示界面元素(如自定义菜单或包含自定义内容的对话框和边栏)则不会。