JDBC

Apps Script peut se connecter à des bases de données externes via le service JDBC, un wrapper autour de la technologie standard Java Database Connectivity. Le service JDBC est compatible avec les bases de données Google Cloud SQL MySQL, MySQL, Microsoft SQL Server et Oracle.

Pour mettre à jour une base de données externe avec JDBC, votre script doit ouvrir une connexion à la base de données, puis effectuer des modifications en envoyant des instructions SQL.

Bases de données Google Cloud SQL

Google Cloud SQL vous permet de créer des bases de données relationnelles hébergées dans le cloud de Google. Notez que, contrairement à Apps Script, Cloud SQL n'est pas offert.

Pour créer une instance Google Cloud SQL, suivez la procédure décrite dans le guide de démarrage rapide de Cloud SQL.

Créer des connexions Google Cloud SQL

Il existe deux façons d'établir une connexion avec une base de données Google Cloud SQL à l'aide du service JDBC d'Apps Script :

Ces méthodes sont expliquées ci-dessous. Les deux sont valides, mais la seconde méthode nécessite d'ajouter à la liste d'autorisation un ensemble de plages d'adresses IP pour l'accès à votre base de données.

Cette méthode crée une connexion à une instance MySQL Google Cloud SQL à l'aide de la méthode Jdbc.getCloudSqlConnection(url). L'URL de la base de données se présente sous la forme jdbc:google:mysql://subname, où subname est le nom de connexion de l'instance MySQL indiqué sur la page Présentation de l'instance Cloud SQL dans la console Google Cloud Platform.

Pour vous connecter à Cloud SQL SQL Server, consultez la page Jdbc.getConnection(url).

Avec Jdbc.getConnection(url)

Pour pouvoir utiliser cette méthode, vous devez ajouter à la liste d'autorisation certaines plages d'adresses IP CIDR afin que les serveurs Apps Script puissent se connecter à votre base de données. Avant d'exécuter votre script, procédez comme suit:

  1. Dans votre instance Google Cloud SQL, autorisez les plages d'adresses IP une par une à partir de cette source de données.

  2. Copiez l'URL attribuée à votre base de données. Elle devrait se présenter sous la forme jdbc:mysql:subname.

Une fois que vous avez ajouté ces plages d'adresses IP à la liste d'autorisation, vous pouvez créer des connexions à votre instance Google Cloud SQL à l'aide de l'une des méthodes Jdbc.getConnection(url) et de l'URL que vous avez copiée ci-dessus.

Autres bases de données

Si vous possédez déjà votre propre base de données MySQL, Microsoft SQL Server ou Oracle, vous pouvez vous y connecter via le service JDBC d'Apps Script.

Créer d'autres connexions à la base de données

Pour créer une connexion à la base de données à l'aide du service JDBC, vous devez ajouter à la liste d'autorisation certaines plages d'adresses IP dans les paramètres de votre base de données, afin de permettre à Apps Script d'y accéder. Voici les plages d'adresses que vous devez répertorier.

Une fois ces listes d'autorisation en place, vous pouvez créer une connexion à la base de données à l'aide de l'une des méthodes Jdbc.getConnection(url) et de l'URL de votre base de données.

Exemple de code

L'exemple de code ci-dessous suppose que vous vous connectez à une base de données Google Cloud SQL et crée des connexions à la base de données à l'aide de la méthode Jdbc.getCloudSqlConnection(url). Pour les autres bases de données, vous devez utiliser la méthode Jdbc.getConnection(url).

Pour en savoir plus sur les méthodes JDBC, consultez la documentation Java pour JDBC.

Créer une base de données, un utilisateur et une table

La plupart des développeurs utilisent l'outil de ligne de commande MySQL pour créer des bases de données, des utilisateurs et des tables. Toutefois, il est possible d'effectuer la même opération dans Apps Script, comme indiqué ci-dessous. Il est recommandé de créer au moins un autre utilisateur pour que votre script n'ait pas toujours besoin de se connecter à la base de données en tant que root.

service/jdbc.fr
/**
 * 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);
  }
}

Écrire dans la base de données

Les exemples ci-dessous montrent comment écrire un seul enregistrement dans la base de données, ainsi qu'un lot de 500 enregistrements. Les opérations par lot sont essentielles pour les opérations groupées.

Notez également l'utilisation d'instructions paramétrées, dans lesquelles les variables sont désignées par ?. Pour empêcher les injections SQL, vous devez échapper toutes les données fournies par l'utilisateur à l'aide d'instructions paramétrées.

service/jdbc.fr
/**
 * 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);
  }
}

Lire depuis la base de données

Cet exemple montre comment lire un grand nombre d'enregistrements dans la base de données, en boucle sur l'ensemble de résultats si nécessaire.

service/jdbc.fr
/**
 * 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);
  }
}

Fermer les connexions

Les connexions JDBC se ferment automatiquement à la fin de l'exécution d'un script. (N'oubliez pas qu'un seul appel google.script.run compte comme une exécution complète, même si la page du service HTML à l'origine de l'appel reste ouverte.)

Toutefois, si vous savez que votre connexion, votre instruction ou votre résultat ont été définis avant la fin du script, il est recommandé de les fermer manuellement en appelant JdbcConnection.close(), JdbcStatement.close() ou JdbcResultSet.close().

L'affichage d'une alerte ou d'une boîte de dialogue d'invite met également fin à toutes les connexions JDBC ouvertes. En revanche, ce n'est pas le cas des autres éléments d'interface utilisateur tels que les menus ou les boîtes de dialogue et les barres latérales personnalisés.