JDBC,

Apps Script może łączyć się z zewnętrznymi bazami danych za pomocą usługi JDBC, która jest otoczką standardowej technologii Java Database Connectivity. Usługa JDBC obsługuje bazy danych Google Cloud SQL for MySQL, MySQL, Microsoft SQL Server, Oracle i PostgreSQL.

Aby zaktualizować zewnętrzną bazę danych za pomocą JDBC, skrypt musi otworzyć połączenie z bazą danych, a następnie wprowadzić zmiany, wysyłając instrukcje SQL.

Bazy danych Google Cloud SQL

Google Cloud SQL umożliwia tworzenie relacyjnych baz danych, które znajdują się w chmurze Google. Pamiętaj, że Cloud SQL może generować opłaty w zależności od wykorzystania.

Instancję Google Cloud SQL możesz utworzyć, wykonując czynności opisane w przewodniku Szybki start Cloud SQL.

Tworzenie połączeń z Google Cloud SQL

Połączenie z bazą danych Google Cloud SQL można nawiązać na 2 sposoby za pomocą usługi JDBC Apps Script:

Metody te są opisane w sekcjach poniżej. Obie metody są prawidłowe, ale druga wymaga autoryzacji zestawu zakresów adresów IP w celu uzyskania dostępu do bazy danych.

Ta metoda tworzy połączenie z instancją MySQL w Google Cloud SQL za pomocą metody Jdbc.getCloudSqlConnection(url). Adres URL bazy danych ma postać jdbc:google:mysql://subname, gdzie subname to nazwa połączenia instancji MySQL podana na stronie Przegląd instancji Cloud SQL w konsoli Google Cloud.

Aby połączyć się z Cloud SQL SQL Server, zapoznaj się z artykułem Jdbc.getConnection(url).

Użyj Jdbc.getConnection(url)

Aby użyć tej metody, musisz autoryzować określone zakresy adresów IP międzydomenowego routingu bez klasy (CIDR), aby serwery Apps Script mogły łączyć się z Twoją bazą danych. Przed uruchomieniem skryptu wykonaj te czynności:

  1. W instancji Google Cloud SQL autoryzuj zakresy adresów IP, po jednym z tego źródła danych.

  2. Skopiuj adres URL przypisany do bazy danych. Powinien mieć format jdbc:mysql:subname.

Po autoryzacji tych zakresów adresów IP możesz utworzyć połączenia z instancją Google Cloud SQL za pomocą jednej z metod Jdbc.getConnection(url) i skopiowanego wcześniej adresu URL.

Inne bazy danych

Jeśli masz już własną bazę danych MySQL, Microsoft SQL Server, Oracle lub PostgreSQL, możesz połączyć się z nią za pomocą usługi JDBC w Apps Script.

Tworzenie innych połączeń z bazą danych

Aby utworzyć połączenie z bazą danych za pomocą usługi JDBC Apps Script, musisz autoryzować zakresy adresów IP z tego źródła danych w ustawieniach bazy danych.

Gdy te listy dozwolonych adresów będą już gotowe, możesz utworzyć połączenie z bazą danych za pomocą jednej z metod Jdbc.getConnection(url) i adresu URL bazy danych.

Przykładowy kod

Poniższy przykładowy kod zakłada, że łączysz się z bazą danych Google Cloud SQL, i tworzy połączenia z bazą danych za pomocą metody Jdbc.getCloudSqlConnection(url). W przypadku innych baz danych do tworzenia połączeń z bazą danych musisz użyć metody Jdbc.getConnection(url).

Więcej informacji o metodach JDBC znajdziesz w dokumentacji Javy dotyczącej JDBC.

Tworzenie bazy danych, użytkownika i tabeli

Większość programistów używa narzędzia wiersza poleceń MySQL do tworzenia baz danych, użytkowników i tabel. Jednak w Apps Script można zrobić to samo, jak pokazano w tym przykładzie. Warto utworzyć co najmniej 1 innego użytkownika, aby skrypt nie musiał zawsze łączyć się z bazą danych jako 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);
  }
}

Zapisywanie w bazie danych

Poniższe przykłady pokazują, jak zapisać w bazie danych pojedynczy rekord oraz partię 500 rekordów. Grupowanie jest niezbędne w przypadku operacji zbiorczych.

Zwróć też uwagę na użycie instrukcji sparametryzowanych, w których zmienne są oznaczane symbolem ?. Aby zapobiec atakom typu SQL injection, używaj instrukcji sparametryzowanych do ucieczki wszystkich danych dostarczonych przez użytkownika.

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

Odczytywanie z bazy danych

Ten przykład pokazuje, jak odczytać z bazy danych dużą liczbę rekordów, w razie potrzeby wykonując pętlę po zbiorze wyników.

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

Zamknij połączenia

Połączenia JDBC zamykają się automatycznie po zakończeniu wykonywania skryptu. (Pamiętaj, że pojedyncze wywołanie google.script.run jest traktowane jako pełne wykonanie, nawet jeśli strona usługi HTML, która dokonała wywołania, pozostaje otwarta).

Jeśli jednak wiesz, że nie będziesz już korzystać z połączenia, instrukcji lub zbioru wyników przed zakończeniem skryptu, warto zamknąć je ręcznie, wywołując funkcje JdbcConnection.close(), JdbcStatement.close() lub JdbcResultSet.close().

Wyświetlenie okna alertu lub okna z prośbą o potwierdzenie powoduje też zakończenie wszystkich otwartych połączeń JDBC. Jednak inne elementy interfejsu wyświetlania, takie jak menu niestandardowe czy okna dialogowe i paski boczne z treściami niestandardowymi, nie są objęte tym wymogiem.

Google, Google Workspace i inne powiązane nazwy są znakami towarowymi Google LLC. Wszystkie inne nazwy firm i produktów są znakami towarowymi należącymi do ich właścicieli.