Premiers pas avec Cloud SQL

Ce guide complète l'exemple de code utilisé sur la page consacrée à la gestion des données envoyées par les utilisateurs en stockant et en récupérant des données à l'aide de Google Cloud SQL.

Cloud SQL est une option de stockage disponible avec App Engine qui s'intègre facilement aux applications et qui peut stocker des données de texte relationnelles. Comparez Cloud SQL, Cloud Datastore et Cloud Storage pour choisir la solution qui répond aux besoins de votre application.

Cet exemple s'appuie sur une série de guides et montre comment stocker, mettre à jour et supprimer des données d'articles de blog dans Cloud SQL.

Avant de commencer

Configurez votre environnement de développement et créez un projet App Engine.

Créer une instance Cloud SQL et se connecter à la base de données

Vous devez créer une instance Cloud SQL et configurer une connexion à partir de votre application App Engine. Pour obtenir des instructions sur la connexion à Cloud SQL, consultez la section Se connecter à App Engine.

Créer des tables

Vous devez créer un objet Connection dans la méthode init() du servlet pour gérer la connexion à l'instance Cloud SQL :

Connection conn; // Cloud SQL connection

// Cloud SQL table creation commands
final String createContentTableSql =
    "CREATE TABLE IF NOT EXISTS posts ( post_id INT NOT NULL "
        + "AUTO_INCREMENT, author_id INT NOT NULL, timestamp DATETIME NOT NULL, "
        + "title VARCHAR(256) NOT NULL, "
        + "body VARCHAR(1337) NOT NULL, PRIMARY KEY (post_id) )";

final String createUserTableSql =
    "CREATE TABLE IF NOT EXISTS users ( user_id INT NOT NULL "
        + "AUTO_INCREMENT, user_fullname VARCHAR(64) NOT NULL, "
        + "PRIMARY KEY (user_id) )";

@Override
public void init() throws ServletException {
  try {
    String url = System.getProperty("cloudsql");

    try {
      conn = DriverManager.getConnection(url);

      // Create the tables so that the SELECT query doesn't throw an exception
      // if the user visits the page before any posts have been added

      conn.createStatement().executeUpdate(createContentTableSql); // create content table
      conn.createStatement().executeUpdate(createUserTableSql); // create user table

      // Create a test user
      conn.createStatement().executeUpdate(createTestUserSql);
    } catch (SQLException e) {
      throw new ServletException("Unable to connect to SQL server", e);
    }

  } finally {
    // Nothing really to do here.
  }
}

La méthode init() configure une connexion à Cloud SQL, puis crée les tables content et user si elles n'existent pas. Après la méthode init(), l'application est prête à être diffusée et peut stocker de nouvelles données.

Dans l'extrait, les instructions SQL de création de tables sont stockées dans des variables String, qui sont exécutées dans la méthode init() du servlet via l'appel à la méthode executeUpdate. Notez que les tables ne seront pas créées si elles existent déjà.

Les deux tables créées dans l'extrait sont nommées posts et users : posts contient les spécificités de chaque article de blog, tandis que users contient des informations sur l'auteur, comme illustré ci-dessous :

Table : posts

Champ	Type
post_id	INT (incrémentation automatique, clé primaire)
author_id	INT
timestamp	DATETIME
title	VARCHAR (256)
body	VARCHAR (1337)

Table : users

Champ	Type
user_id	INT (incrémentation automatique, clé primaire)
user_fullname	VARCHAR (64)

Récupérer les données initiales pour les afficher dans un formulaire

Un cas d'utilisation courant consiste à pré-remplir un formulaire avec des données stockées dans la base de données, afin de s'en servir dans les sélections d'utilisateurs. Exemple :

Connection conn;

final String getUserId = "SELECT user_id, user_fullname FROM users";
Map<Integer, String> users = new HashMap<Integer, String>();

@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {

  // Find the user ID from the full name
  try (ResultSet rs = conn.prepareStatement(getUserId).executeQuery()) {
    while (rs.next()) {
      users.put(rs.getInt("user_id"), rs.getString("user_fullname"));
    }

    req.setAttribute("users", users);
    req.getRequestDispatcher("/form.jsp").forward(req, resp);

  } catch (SQLException e) {
    throw new ServletException("SQL error", e);
  }
}

Dans l'extrait de code ci-dessus, le servlet interroge la base de données Cloud SQL pour récupérer une liste d'ID utilisateur et de noms d'auteurs. Ceux-ci sont stockés sous forme de tuples (id, full name) dans une table de hachage. Le servlet transmet ensuite l'utilisateur et la table de hachage à /form.jsp, qui traite la table de hachage des noms d'auteur comme indiqué dans la section suivante.

Assurer la compatibilité des interactions de base de données dans un formulaire

L'extrait suivant se sert de la technologie JSP (JavaServer Pages) pour présenter à l'utilisateur les données initiales de la table de hachage des noms d'auteurs transmise à partir du servlet, et exploite ces données dans une liste de sélection. Le formulaire permet également à l'utilisateur de créer et de mettre à jour des données.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
<div>
  <c:choose>
    <c:when test="${id == null}">
      <h2>Create a new blog post</h2>
      <form method="POST" action="/create">
    </c:when>
    <c:otherwise>
      <h2><c:out value="${pagetitle}" /></h2>
      <form method="POST" action="/update">
      <input type="hidden" name="blogContent_id" value="${id}">
    </c:otherwise>
  </c:choose>

    <div>
      <label for="title">Title</label>
      <input type="text" name="blogContent_title" id="title" size="40" value="${title}" />
    </div>

    <div>
      <label for="author">Author</label>
      <select name="blogContent_id">
        <c:forEach items="${users}" var="user">
        <option value="${user.key}">${user.value}</option>
        </c:forEach>
      </select>
      <input type="text" name="blogContent_author" id="author" size="40" value="${author}" />
    </div>

    <div>
      <label for="description">Post content</label>
      <textarea name="blogContent_description" id="description" rows="10" cols="50">${body}</textarea>
    </div>

    <button type="submit">Save</button>
  </form>
</div>

Dans l'extrait de code ci-dessus, le formulaire est rempli au chargement de la page, à l'aide de la table de hachage des noms d'auteurs transmise à partir du servlet. Le formulaire utilise les opérations when et otherwise de la bibliothèque de balises standards des pages JavaServer (JSTL). Il fournit une logique if..else et des boucles forEach via la table de hachage transmise par le servlet.

Dans l'extrait ci-dessus, la page JSP contient un formulaire permettant de créer des articles de blog et de mettre à jour les articles existants. Notez que le formulaire peut envoyer les données aux gestionnaires via /create ou /update, selon que l'utilisateur crée ou met à jour un article de blog.

Pour en savoir plus sur l'utilisation des formulaires, consultez la page Gérer des données POST.

Stocker les enregistrements

L'extrait suivant montre comment créer un enregistrement à partir des données fournies par l'utilisateur dans le formulaire et comment les stocker dans la base de données. Dans cet exemple, nous avons une instruction SQL INSERT générée à partir des données qui ont été envoyées dans le formulaire de création d'article de blog décrit dans la section précédente :

// Post creation query
final String createPostSql =
    "INSERT INTO posts (author_id, timestamp, title, body) VALUES (?, ?, ?, ?)";

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {

  // Create a map of the httpParameters that we want and run it through jSoup
  Map<String, String> blogContent =
      req.getParameterMap()
          .entrySet()
          .stream()
          .filter(a -> a.getKey().startsWith("blogContent_"))
          .collect(
              Collectors.toMap(
                  p -> p.getKey(), p -> Jsoup.clean(p.getValue()[0], Whitelist.basic())));

  // Build the SQL command to insert the blog post into the database
  try (PreparedStatement statementCreatePost = conn.prepareStatement(createPostSql)) {
    // set the author to the user ID from the user table
    statementCreatePost.setInt(1, Integer.parseInt(blogContent.get("blogContent_id")));
    statementCreatePost.setTimestamp(2, new Timestamp(new Date().getTime()));
    statementCreatePost.setString(3, blogContent.get("blogContent_title"));
    statementCreatePost.setString(4, blogContent.get("blogContent_description"));
    statementCreatePost.executeUpdate();

    conn.close(); // close the connection to the Cloud SQL server

    // Send the user to the confirmation page with personalised confirmation text
    String confirmation = "Post with title " + blogContent.get("blogContent_title") + " created.";

    req.setAttribute("confirmation", confirmation);
    req.getRequestDispatcher("/confirm.jsp").forward(req, resp);

  } catch (SQLException e) {
    throw new ServletException("SQL error when creating post", e);
  }
}

L'extrait de code se sert de l'entrée de l'utilisateur et l'exécute via jSoup pour la nettoyer. L'utilisation de jSoup et de PreparedStatement atténue le risque d'attaques par injection XSS et SQL.

La variable createPostSql contient la requête INSERT avec ? comme espaces réservés pour les valeurs qui seront attribuées à l'aide de la méthode PreparedStatement.set().

Notez l'ordre des champs de la table, car ils sont référencés dans les méthodes de l'ensemble PreparedStatement. Par exemple, author_id est un champ de type INT, donc setInt() doit être utilisé pour définir author_id.

Récupérer les enregistrements

L'extrait suivant montre la méthode doGet() d'un servlet, qui consiste à récupérer les lignes de la table des articles de blog et à les imprimer.

// Preformatted HTML
String headers =
    "<!DOCTYPE html><meta charset=\"utf-8\"><h1>Welcome to the App Engine Blog</h1><h3><a href=\"blogpost\">Add a new post</a></h3>";
String blogPostDisplayFormat =
    "<h2> %s </h2> Posted at: %s by %s [<a href=\"/update?id=%s\">update</a>] | [<a href=\"/delete?id=%s\">delete</a>]<br><br> %s <br><br>";

@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {
  // Retrieve blog posts from Cloud SQL database and display them

  PrintWriter out = resp.getWriter();

  out.println(headers); // Print HTML headers

  try (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) {
    Map<Integer, Map<String, String>> storedPosts = new HashMap<>();

    while (rs.next()) {
      Map<String, String> blogPostContents = new HashMap<>();

      // Store the particulars for a blog in a map
      blogPostContents.put("author", rs.getString("users.user_fullname"));
      blogPostContents.put("title", rs.getString("posts.title"));
      blogPostContents.put("body", rs.getString("posts.body"));
      blogPostContents.put("publishTime", rs.getString("posts.timestamp"));

      // Store the post in a map with key of the postId
      storedPosts.put(rs.getInt("posts.post_id"), blogPostContents);
    }

    // Iterate the map and display each record's contents on screen
    storedPosts.forEach(
        (k, v) -> {
          // Encode the ID into a websafe string
          String encodedID = Base64.getUrlEncoder().encodeToString(String.valueOf(k).getBytes());

          // Build up string with values from Cloud SQL
          String recordOutput =
              String.format(blogPostDisplayFormat, v.get("title"), v.get("publishTime"),
                  v.get("author"), encodedID, encodedID, v.get("body"));

          out.println(recordOutput); // print out the HTML
        });

  } catch (SQLException e) {
    throw new ServletException("SQL error", e);
  }
}

Les résultats de l'instruction SELECT sont placés dans un élément ResultSet, qui est itéré lors de l'utilisation de la méthode ResultSet.get(). Notez l'objet getString de la méthode ResultSet.get() correspondant au schéma de table défini précédemment.

Pour cet exemple, chaque article comporte un lien [Update] et un lien [Delete], qui permettent, respectivement, de lancer les mises à jour et les suppressions d'articles. Pour obscurcir l'ID de l'article, celui-ci est encodé au format Base64.

Mettre à jour les enregistrements

L'extrait suivant montre comment mettre à jour un enregistrement existant :

final String updateSql = "UPDATE posts SET title = ?, body = ? WHERE post_id = ?";

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {

  // Create a map of the httpParameters that we want and run it through jSoup
  Map<String, String> blogContent =
      req.getParameterMap()
          .entrySet()
          .stream()
          .filter(a -> a.getKey().startsWith("blogContent_"))
          .collect(
              Collectors.toMap(
                  p -> p.getKey(), p -> Jsoup.clean(p.getValue()[0], Whitelist.basic())));

  // Build up the PreparedStatement
  try (PreparedStatement statementUpdatePost = conn.prepareStatement(updateSql)) {
    statementUpdatePost.setString(1, blogContent.get("blogContent_title"));
    statementUpdatePost.setString(2, blogContent.get("blogContent_description"));
    statementUpdatePost.setString(3, blogContent.get("blogContent_id"));
    statementUpdatePost.executeUpdate(); // Execute update query

    conn.close();

    // Confirmation string
    final String confirmation = "Blog post " + blogContent.get("blogContent_id") + " has been updated";

    req.setAttribute("confirmation", confirmation);
    req.getRequestDispatcher("/confirm.jsp").forward(req, resp);

  } catch (SQLException e) {
    throw new ServletException("SQL error", e);
  }
}

Dans cet extrait, lorsque l'utilisateur clique sur le lien [Mettre à jour] d'un article de blog, le formulaire JSP permettant de créer un article s'affiche. Cependant, ce formulaire est désormais pré-rempli avec le titre et le contenu de l'article existant. Le nom de l'auteur ne s'affiche pas dans l'exemple, car il ne change pas.

La mise à jour et la création d'un article sont deux processus semblables, si ce n'est que la requête SQL INSERT est remplacée par UPDATE pour la mise à jour.

Après avoir exécuté executeUpdate(), l'utilisateur est redirigé vers une page de confirmation dans l'extrait.

Supprimer les enregistrements

La suppression d'une ligne (d'un article de blog ici) nécessite de supprimer une ligne de la table cible, qui est la table content dans l'exemple. Chaque enregistrement est identifié par son ID, qui correspond à la valeur post_id dans l'exemple de code. Vous devez utiliser cet ID comme filtre dans la requête DELETE :

Après l'exécution de executeUpdate(), l'utilisateur est redirigé vers une page de confirmation.

final String deleteSql = "DELETE FROM posts WHERE post_id = ?";

@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {

  Map<String, String[]> userData = req.getParameterMap();

  String[] postId = userData.get("id");
  String decodedId = new String(Base64.getUrlDecoder().decode(postId[0])); // Decode the websafe ID

  try (PreparedStatement statementDeletePost = conn.prepareStatement(deleteSql)) {
    statementDeletePost.setString(1, postId[0]);
    statementDeletePost.executeUpdate();

    final String confirmation = "Post ID " + postId[0] + " has been deleted.";

    req.setAttribute("confirmation", confirmation);
    req.getRequestDispatcher("/confirm.jsp").forward(req, resp);

  } catch (SQLException e) {
    throw new ServletException("SQL error", e);
  }

}

Après avoir décodé l'ID de l'article, l'extrait supprimera un seul article de la table posts.

Déployer sur App Engine

Vous pouvez déployer votre application sur App Engine à l'aide de Maven.

Accédez au répertoire racine de votre projet, puis saisissez la commande suivante :

mvn package appengine:deploy -Dapp.deploy.projectId=PROJECT_ID

Remplacez PROJECT_ID par l'ID de votre projet Cloud. Si votre ID de projet est déjà inclus dans le fichier pom.xml, vous n'avez pas besoin d'inclure la propriété -Dapp.deploy.projectId dans la commande que vous exécutez.

Lorsque Maven a déployé votre application, saisissez la commande ci-dessous pour ouvrir un onglet de navigateur Web automatiquement dans votre nouvelle application :

gcloud app browse

Étape suivante

Cloud SQL est une solution pratique pour stocker des données textuelles. Toutefois, si vous souhaitez stocker du contenu multimédia riche, tel que des images, nous vous recommandons d'utiliser Cloud Storage.

Ensuite, découvrez comment utiliser les files d'attente de tâches pour exécuter des tâches asynchrones en suivant un exemple d'utilisation de l'API Images afin de redimensionner les images transférées dans ce guide.