JDBC voor databases vanuit Apps Script

JDBC-service inleiding

De JDBC-service laat je vanuit Apps Script verbinding maken met externe relationele databases: MySQL, PostgreSQL, Microsoft SQL Server, Oracle en Google Cloud SQL. Dit is nuttig wanneer je data wilt ophalen of schrijven naar een bedrijfsdatabase zonder externe middleware.

Twee aandachtspunten voordat je begint:

• De service kan alleen verbinden met poort 1025 of hoger. Draait je database op een lagere poort, dan werkt de verbinding niet.
• Apps Script vereist TLS 1.2 of hoger. Oudere TLS-versies (1.0 en 1.1) zijn uitgeschakeld.

function testVerbinding() {
  const verbinding = Jdbc.getConnection(
    'jdbc:mysql://db.bedrijf.nl:3306/klanten',
    'gebruikersnaam',
    'wachtwoord'
  );

  Logger.log('Verbinding geslaagd');
  verbinding.close();
}

Veilige credential-opslag

function slaCredentialsOp() {
  const props = PropertiesService.getScriptProperties();
  props.setProperties({
    DB_URL: 'jdbc:mysql://db.bedrijf.nl:3306/klanten',
    DB_USER: 'appgebruiker',
    DB_PASS: 'VeiligWachtwoord123!',
  });
}

function getVerbinding() {
  const props = PropertiesService.getScriptProperties();
  return Jdbc.getConnection(
    props.getProperty('DB_URL'),
    props.getProperty('DB_USER'),
    props.getProperty('DB_PASS')
  );
}

SELECT-queries uitvoeren

Gebruik altijd een PreparedStatement met getypeerde setters in plaats van string-concatenatie. Dat beschermt je tegen SQL-injectie.

function leesKlanten() {
  const conn = getVerbinding();

  try {
    const stmt = conn.prepareStatement(
      'SELECT id, naam, email, stad FROM klanten WHERE actief = ? ORDER BY naam LIMIT ?'
    );
    stmt.setBoolean(1, true);
    stmt.setInt(2, 100);

    const rs = stmt.executeQuery();
    const resultaten = [];

    while (rs.next()) {
      resultaten.push({
        id: rs.getInt(1),
        naam: rs.getString(2),
        email: rs.getString(3),
        stad: rs.getString(4),
      });
    }

    rs.close();
    stmt.close();

    Logger.log(`${resultaten.length} klanten opgehaald`);
    return resultaten;
  } finally {
    conn.close();
  }
}

INSERT en UPDATE met transacties

Door setAutoCommit(false) te gebruiken voer je meerdere schrijfacties uit als een geheel. Mislukt er een, dan draai je alles terug met rollback().

function voegKlantToe(naam, email, stad) {
  const conn = getVerbinding();
  conn.setAutoCommit(false);

  try {
    const stmt = conn.prepareStatement(
      'INSERT INTO klanten (naam, email, stad, aangemaakt_op) VALUES (?, ?, ?, NOW())'
    );
    stmt.setString(1, naam);
    stmt.setString(2, email);
    stmt.setString(3, stad);
    stmt.execute();

    const logStmt = conn.prepareStatement(
      'INSERT INTO audit_log (actie, details, tijdstip) VALUES (?, ?, NOW())'
    );
    logStmt.setString(1, 'KLANT_AANGEMAAKT');
    logStmt.setString(2, `Klant ${naam} (${email}) aangemaakt`);
    logStmt.execute();

    conn.commit();
    Logger.log('Klant toegevoegd en audit-log bijgewerkt');
  } catch(e) {
    conn.rollback();
    Logger.log('Fout, rollback uitgevoerd: ' + e.message);
    throw e;
  } finally {
    conn.close();
  }
}

Bulk-insert vanuit Sheets

Voor grotere volumes verzamel je statements met addBatch() en stuur je ze in een keer weg met executeBatch(). Dat is veel sneller dan rij voor rij uitvoeren.

function syncSheetNaarDatabase() {
  const blad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imports');
  const data = blad.getDataRange().getValues();

  const conn = getVerbinding();
  conn.setAutoCommit(false);

  try {
    const stmt = conn.prepareStatement(
      'INSERT INTO producten (naam, prijs, categorie) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE prijs = VALUES(prijs)'
    );

    let succes = 0;
    let fouten = 0;

    for (let i = 1; i < data.length; i++) {
      try {
        stmt.setString(1, data[i][0]);
        stmt.setFloat(2, parseFloat(data[i][1]));
        stmt.setString(3, data[i][2]);
        stmt.addBatch();
        succes++;
      } catch(e) {
        fouten++;
        Logger.log(`Fout rij ${i}: ${e.message}`);
      }
    }

    stmt.executeBatch();
    conn.commit();
    Logger.log(`${succes} rijen gesynchroniseerd, ${fouten} fouten`);
  } catch(e) {
    conn.rollback();
    throw e;
  } finally {
    conn.close();
  }
}

Cloud SQL verbinding

Voor Google Cloud SQL gebruik je Jdbc.getCloudSqlConnection(). Die methode heeft geen IP-whitelisting nodig, omdat de verbinding binnen de Google-infrastructuur blijft. De URL heeft de vorm jdbc:google:mysql://project:regio:instance/database.

function cloudSqlVerbinding() {
  const cloudSqlUrl = 'jdbc:google:mysql://mijn-project:europe-west4:mijn-instance/database';
  const conn = Jdbc.getCloudSqlConnection(cloudSqlUrl, 'gebruiker', 'wachtwoord');

  const stmt = conn.createStatement();
  const rs = stmt.executeQuery('SELECT COUNT(*) FROM orders');

  if (rs.next()) {
    Logger.log(`Aantal orders: ${rs.getInt(1)}`);
  }

  rs.close();
  conn.close();
}

JDBC maakt van Apps Script een krachtige brug tussen Google Workspace en bestaande bedrijfsdatabases. Door transacties te gebruiken en credentials veilig op te slaan bouw je betrouwbare data-synchronisatie.