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 votre projet App Engine.

Créer une instance et une base de données Cloud SQL

Pour créer une instance et une base de données Cloud SQL, procédez comme suit :

  1. Créez une instance Cloud SQL de deuxième génération, puis configurez l'utilisateur racine. Vous pouvez vous connecter via l'utilisateur racine, mais Google vous recommande de créer un utilisateur.
  2. À l'aide du SDK Cloud, obtenez le nom de connexion de l'instance Cloud SQL à utiliser en tant que chaîne de connexion dans le code de votre application :
    gcloud sql instances describe [INSTANCE_NAME]
    Enregistrez la valeur renvoyée pour connectionName. Vous pouvez également trouver cette valeur sur la page "Détails de l'instance" de la console Google Cloud Platform. Par exemple, voici ce que vous obtenez dans le résultat du SDK Cloud :
    gcloud sql instances describe [INSTANCE_NAME]
      connectionName: project1:us-central1:instance1
  3. Créez une base de données dans votre instance Cloud SQL. Dans cet exemple, la base de données est nommée content.
    gcloud sql databases create content --instance=[INSTANCE_NAME]

    Pour en savoir plus, consultez la section Créer une base de données.

Configurer une connexion à une base de données

Pour définir les propriétés système de l'application pour le nom de connexion de l'instance, la base de données, l'utilisateur et le mot de passe Cloud SQL, procédez comme suit :

  1. Ajoutez le nom de connexion de l'instance, la base de données, l'utilisateur et le mot de passe Cloud SQL dans la section <properties> de premier niveau du fichier pom.xml de votre projet.

    <properties>
      [...]
      <INSTANCE_CONNECTION_NAME>[INSTANCE_CONNECTION_NAME]</INSTANCE_CONNECTION_NAME>
      <user>[USERNAME]</user>
      <password>[PASSWORD]</password>
      <database>[DATABASE-NAME]</database>
    </properties>
    
  2. Définissez les propriétés de connexion Cloud SQL dans appengine-web.xml afin que l'application puisse accéder aux données.

    <appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
      <threadsafe>true</threadsafe>
      <runtime>java8</runtime>
      <use-google-connector-j>true</use-google-connector-j>
    </appengine-web-app>
    

Se connecter à Cloud SQL et 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() établit la 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 table sont stockées dans des variables String, qui sont exécutées dans la méthode init() du servlet via l'appel de 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 : la table posts contient les spécificités de chaque article de blog, tandis que la table users inclut 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.get529ggInt("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'auteurs, 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 JavaServer Pages Standard Tag Library (JSTL). Il fournit également la logique if..else et les boucles forEach via la table de hachage transmise depuis 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 à l'aide de /create ou de /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. Par conséquent, setInt() doit servir à définir l'élément 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 l'exécution de 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 supprime un seul article de la table posts.

Déployer l'application 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 appengine:deploy

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.

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Environnement standard App Engine pour Java 8