JDBC

Apps Script может подключаться к внешним базам данных через службу JDBC , представляющую собой оболочку для стандартной технологии Java Database Connectivity . Служба JDBC поддерживает базы данных Google Cloud SQL для MySQL , MySQL, Microsoft SQL Server, Oracle и PostgreSQL.

Для обновления внешней базы данных с помощью JDBC ваш скрипт должен установить соединение с базой данных, а затем внести изменения, отправляя SQL-запросы.

Базы данных Google Cloud SQL

Google Cloud SQL позволяет создавать реляционные базы данных, размещенные в облаке Google. Обратите внимание, что использование Cloud SQL может быть платным в зависимости от объема предоставляемых услуг.

Создать экземпляр Google Cloud SQL можно, следуя инструкциям, приведенным в кратком руководстве по Cloud SQL .

Создание подключений к Google Cloud SQL

Существует два способа установить соединение с базой данных Google Cloud SQL с помощью службы JDBC в Apps Script:

Эти методы описаны в следующих разделах. Оба метода допустимы, но второй метод требует авторизации набора диапазонов IP-адресов для доступа к вашей базе данных.

Этот метод устанавливает соединение с экземпляром Google Cloud SQL MySQL, используя метод Jdbc.getCloudSqlConnection(url) . URL базы данных имеет вид jdbc:google:mysql://subname , где subname — это имя соединения с экземпляром MySQL, указанное на странице обзора экземпляра Cloud SQL в консоли Google Cloud .

Для подключения к Cloud SQL SQL Server см. Jdbc.getConnection(url) .

Используйте Jdbc.getConnection(url)

Для использования этого метода необходимо авторизовать определенные диапазоны IP-адресов CIDR (Classless Inter-Domain Routing) , чтобы серверы Apps Script могли подключаться к вашей базе данных. Перед запуском скрипта выполните следующие шаги:

  1. В вашем экземпляре Google Cloud SQL авторизуйте диапазоны IP-адресов по одному из этого источника данных .

  2. Скопируйте URL-адрес, присвоенный вашей базе данных; он должен иметь вид jdbc:mysql:subname .

После авторизации этих диапазонов IP-адресов вы можете создавать подключения к своему экземпляру Google Cloud SQL, используя один из методов Jdbc.getConnection(url) и URL-адрес, который вы скопировали ранее.

Другие базы данных

Если у вас уже есть собственная база данных MySQL, Microsoft SQL Server, Oracle или PostgreSQL, вы можете подключиться к ней через службу JDBC в Apps Script.

Создайте другие подключения к базе данных.

Для создания подключения к базе данных с использованием службы Apps Script JDBC необходимо в настройках базы данных разрешить использование диапазонов IP-адресов из этого источника данных .

После того, как эти списки разрешенных адресов будут настроены, вы сможете установить соединение с базой данных, используя один из методов Jdbc.getConnection(url) и URL-адрес вашей базы данных.

Пример кода

Приведенный ниже пример кода предполагает подключение к базе данных Google Cloud SQL и создает соединения с базой данных с помощью метода Jdbc.getCloudSqlConnection(url) . Для других баз данных необходимо использовать метод Jdbc.getConnection(url) для создания соединений с базой данных.

Для получения дополнительной информации о методах JDBC см. документацию Java по JDBC .

Создайте базу данных, пользователя и таблицу.

Большинство разработчиков используют инструмент командной строки 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);
  }
}

/**
 * Write 500 rows of data to a table in a single batch.
 * Recommended for faster writes
 */
function writeManyRecordsUsingExecuteBatch() {
  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 (?, ?)",
    );
    const params = [];
    for (let i = 0; i < 500; i++) {
      params.push([`Name ${i}`, `Hello, world ${i}`]);
    }

    const batch = stmt.executeBatch(params);
    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);
  }
}

/**
 * Read up to 1000 rows of data from the table and log them.
 * Recommended for faster reads
 */
function readFromTableUsingGetRows() {
  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();
    const getRowArgs = [];
    for (let col = 0; col < numCols; col++) {
      getRowArgs.push(`getString(${col + 1})`);
    }
    const rows = results.getRows(getRowArgs.join(","));
    for (let i = 0; i < rows.length; i++) {
      console.log(rows[i].join("\t"));
    }

    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-соединения автоматически закрываются после завершения выполнения скрипта. (Обратите внимание, что один вызов google.script.run считается завершенным выполнением, даже если HTML-страница сервиса, выполнившего вызов, остается открытой.)

Тем не менее, если вы знаете, что завершили работу с соединением, оператором или набором результатов до окончания выполнения скрипта, рекомендуется закрыть их вручную, вызвав методы JdbcConnection.close() , JdbcStatement.close() или JdbcResultSet.close() .

Отображение предупреждающего или диалогового окна также разрывает все открытые JDBC-соединения. Однако отображение других элементов пользовательского интерфейса — таких как пользовательские меню, диалоговые окна и боковые панели с пользовательским содержимым — этого не делает.

Google, Google Workspace, а также связанные с ними знаки и логотипы являются товарными знаками Google LLC. Все остальные названия компаний и продуктов являются товарными знаками компаний, с которыми они связаны.