JDBC

Apps Script può connettersi a database esterni tramite il servizio JDBC, un wrapper attorno alla tecnologia di connettività dei database Java standard. Il servizio JDBC supporta i database Google Cloud SQL MySQL, MySQL, Microsoft SQL Server e Oracle.

Per aggiornare un database esterno con JDBC, lo script deve aprire una connessione al database e quindi apportare modifiche inviando le istruzioni SQL.

Database Google Cloud SQL

Google Cloud SQL ti consente di creare database relazionali che risiedono nel cloud di Google. Tieni presente che, a differenza di Apps Script, Cloud SQL non è senza costi.

Puoi creare un'istanza di Google Cloud SQL seguendo i passaggi elencati nella guida rapida di Cloud SQL.

Creazione di connessioni SQL Cloud di Google Cloud

Esistono due modi per stabilire una connessione con un database Google Cloud SQL utilizzando il servizio JDBC di Apps Script:

Questi metodi sono illustrati di seguito. Entrambi sono validi, ma il secondo metodo richiede che tu consenta l'autorizzazione di un insieme di intervalli IP per l'accesso al tuo database.

Questo metodo crea una connessione a un'istanza MySQL di Google Cloud SQL utilizzando il metodo Jdbc.getCloudSqlConnection(url). L'URL del database ha il formato jdbc:google:mysql://subname, dove subname è il nome di connessione istanza di MySQL elencato nella pagina Panoramica dell'istanza Cloud SQL nella console di Google Cloud Platform.

Per stabilire la connessione a Cloud SQL SQL Server, vedi Jdbc.getConnection(url).

Utilizzo di Jdbc.getConnection(url)

Per utilizzare questo metodo, devi consentire alcuni elenchi di indirizzi IP CIDR in modo che i server di Apps Script possano connettersi al tuo database. Prima di eseguire lo script, completa i seguenti passaggi:

  1. Nell'istanza Google Cloud SQL, autorizza gli intervalli IP, uno alla volta da questa origine dati.

  2. Copia l'URL assegnato al tuo database. Dovrebbe avere il formato jdbc:mysql:subname.

Dopo aver autorizzato questi intervalli IP, puoi creare connessioni all'istanza Google Cloud SQL utilizzando uno dei metodi Jdbc.getConnection(url) e l'URL che hai copiato in precedenza.

Altri database

Se hai già il tuo database MySQL, Microsoft SQL Server o Oracle, puoi connetterti a quest'ultimo tramite il servizio JDBC di Apps Script.

Creazione di altre connessioni al database

Per creare una connessione al database utilizzando il servizio JDBC, devi autorizzare alcuni intervalli IP nelle impostazioni del database per consentire ad Apps Script di accedervi. Questi sono gli intervalli di indirizzi che devi aggiungere alla lista consentita.

Dopo aver predisposto queste liste consentite, puoi creare una connessione al database utilizzando uno dei metodi Jdbc.getConnection(url) e l'URL del tuo database.

Codice di esempio

Il codice campione seguente presuppone che tu ti stia connettendo a un database Google Cloud SQL e crei connessioni di database utilizzando il metodo Jdbc.getCloudSqlConnection(url). Per gli altri database, devi utilizzare il metodo Jdbc.getConnection(url) per creare connessioni al database.

Per ulteriori informazioni sui metodi JDBC, consulta la documentazione di JDBC per Java.

Crea un database, un utente e una tabella

La maggior parte degli sviluppatori utilizza lo strumento a riga di comando MySQL per creare database, utenti e tabelle. Tuttavia, è possibile fare la stessa cosa in Apps Script, come mostrato di seguito. Ti consigliamo di creare almeno un altro utente, in modo che lo script non debba sempre connettersi al database come root.

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

Scrivi nel database

Gli esempi riportati di seguito mostrano come scrivere un singolo record nel database e un batch di 500 record. Il batch è fondamentale per le operazioni collettive.

Nota anche l'utilizzo di istruzioni con parametri, in cui le variabili sono indicate da ?. Per evitare iniezioni SQL, devi utilizzare istruzioni con parametri per eseguire l'escape di tutti i dati forniti dall'utente.

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

Lettura dal database

Questo esempio mostra come leggere un numero elevato di record dal database, eseguendo il loop del set di risultati secondo necessità.

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

Chiusura dei collegamenti

Le connessioni JDBC si chiudono automaticamente al termine dell'esecuzione di uno script. Tieni presente che una singola chiamata google.script.run viene conteggiata come un'esecuzione completa, anche se la pagina del servizio HTML che ha effettuato la chiamata rimane aperta.

Tuttavia, se hai la certezza di aver impostato una connessione, un'istruzione o un risultato prima della fine dello script, ti consigliamo di chiuderli manualmente chiamando JdbcConnection.close(), JdbcStatement.close() o JdbcResultSet.close().

Se viene visualizzata una finestra di dialogo o un avviso, verranno terminate anche tutte le connessioni JDBC aperte. Al contrario di altri elementi dell'interfaccia utente, come i menu o le finestre di dialogo e le barre laterali personalizzate con contenuti personalizzati.