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 apporter 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 des frais peuvent s'appliquer à Cloud SQL en fonction de votre utilisation.

Pour créer une instance Google Cloud SQL, suivez les étapes décrites 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 décrites ci-dessous. Les deux sont valides, mais la deuxième méthode vous oblige à autoriser un ensemble de plages d'adresses IP pour 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 est le nom de connexion de l'instance MySQL listé sur la page Présentation de l'instance Cloud SQL dans la console Google Cloud.

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

À l'aide 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 d'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 de base de données

Pour créer une connexion de base de données à l'aide du service JDBC Apps Script, vous devez autoriser les plages d'adresses IP de cette source de données dans les paramètres de votre 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 de 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 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, vous pouvez faire la même chose dans Apps Script, comme indiqué ci-dessous. Il est conseillé de créer au moins un autre utilisateur afin que votre script ne soit pas toujours obligé de 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 par lots.

Notez également l'utilisation d'instructions paramétrées, dans lesquelles les variables sont désigné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 à partir de la base de données, en effectuant une boucle sur le jeu 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. (N'oubliez pas qu'un seul appel google.script.run compte comme une exécution complète, même si la page de service HTML qui a effectué l'appel reste ouverte.)

Toutefois, si vous savez que vous avez terminé avec une connexion, une instruction ou un ensemble de résultats avant la fin du script, il est conseillé 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 à toutes les connexions JDBC ouvertes. Toutefois, les autres éléments d'interface utilisateur qui s'affichent, tels que les menus ou les boîtes de dialogue personnalisés, ainsi que les barres latérales avec du contenu personnalisé, ne le font pas.

Google, Google Workspace, ainsi que les marques et logos associés sont des marques appartenant à Google LLC. Tous les autres noms de sociétés et de produits sont des marques des sociétés auxquelles ils sont associés.