JDBC

Apps Script peut se connecter à des bases de données externes via le service JDBC, un wrapper autour de la technologie Java Database Connectivity standard. Le service JDBC est compatible avec les bases de données Google Cloud SQL pour 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 qui résident dans le cloud de Google. Notez que Cloud SQL peut entraîner des frais en fonction de votre utilisation.

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 avec la seconde méthode, vous devez autoriser un ensemble de plages d'adresses IP à accéder à 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 correspond au nom de connexion de l'instance MySQL indiqué sur la page Présentation de l'instance Cloud SQL dans la console Google Cloud.

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

Utilisation de Jdbc.getConnection(url)

Pour utiliser cette méthode, vous devez autoriser certaines plages d'adresses IP CIDR (Classless Inter-Domain Routing) 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 doit se présenter sous la forme jdbc:mysql:subname.

Une fois que vous avez autorisé ces plages d'adresses IP, 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 disposez déjà de 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 d'Apps Script, vous devez autoriser les plages d'adresses IP de cette source de données dans vos paramètres de base de données.

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) afin de créer des connexions à la base de données.

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 ne doive pas toujours se connecter à la base de données en tant que 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);
  }
}

É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. Le traitement par lot est essentiel pour les opérations groupées.

Notez également l'utilisation d'instructions paramétrées, dans lesquelles les variables sont indiquées par ?. Pour éviter les attaques par injection SQL, vous devez utiliser des instructions paramétrées pour échapper toutes les données fournies par l'utilisateur.

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

Lire à partir de la base de données

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

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

Fermer les connexions

Les connexions JDBC se ferment automatiquement à la fin de l'exécution d'un script. Gardez à l'esprit qu'un seul appel google.script.run est comptabilisé comme une exécution complète, même si la page du service HTML qui a effectué l'appel reste ouverte.

Néanmoins, si vous avez fini avec une connexion, une instruction ou un ensemble de résultats 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 boîte de dialogue d'alerte ou d'invite met également fin aux connexions JDBC ouvertes. En revanche, ce n'est pas le cas des autres éléments d'interface utilisateur, tels que les menus ou boîtes de dialogue personnalisés, et les barres latérales avec du contenu personnalisé.

Google, Google Workspace, ainsi que les marques et les logos associés sont des marques de Google LLC. Tous les autres noms d'entreprises et de produits sont des marques des entreprises auxquelles ils sont associés.