Per iniziare: Cloud SQL

Questa guida estende l'esempio di codice utilizzato nella sezione Gestione dei dati inviati dagli utenti archiviando e recuperando i dati tramite Google Cloud SQL.

Cloud SQL è un'opzione di archiviazione disponibile con App Engine che può essere facilmente integrata nelle app e archiviare dati di testo relazionali. Confronta Cloud SQL, Cloud Datastore e Cloud Storage e scegli quello che soddisfa i requisiti della tua app.

Questo esempio si basa su una serie di guide e mostra come archiviare, aggiornare ed eliminare i dati dei post del blog in Cloud SQL.

Prima di iniziare

Configura l'ambiente di sviluppo e crea il tuo progetto App Engine.

Creazione di un'istanza Cloud SQL e connessione al database

Dovrai creare un'istanza di Cloud SQL e configurare una connessione dall'app App Engine. Per istruzioni sulla connessione a Cloud SQL, consulta Connessione ad App Engine.

Creazione di tabelle

Devi creare un oggetto Connection nel metodo servlet init() per gestire la connessione all'istanza 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.
  }
}

Il metodo init() configura una connessione a Cloud SQL e crea le tabelle content e user se non esistono. Dopo il metodo init(), l'app è pronta per pubblicare e archiviare nuovi dati.

Nello snippet, le istruzioni SQL di creazione della tabella vengono memorizzate in variabili String, che vengono eseguite all'interno del tag init() del servlet tramite la chiamata al metodo executeUpdate. Noterai che le tabelle non verranno create se esistono già.

Le due tabelle create nello snippet sono denominate posts e users: posts contiene le specifiche di ogni post del blog, mentre users contiene informazioni sull'autore, come mostrato qui:

Tabella: post

Campo Tipo
post_id INT (incremento automatico, chiave primaria)
author_id INT
timestamp DATA/ORA
title VARCHAR (256)
corpo VARCHAR (1337)

Tabella: utenti

Campo Tipo
user_id INT (incremento automatico, chiave primaria)
nome_utente_completo VARCHAR (64) (Testo)

Recupero dei dati iniziali da visualizzare in un modulo

Un caso d'uso comune è la precompilazione di un modulo con dati archiviati nel database, da utilizzare per la selezione degli utenti. Ad esempio:

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

Nello snippet di codice riportato sopra, il servlet interroga il database Cloud SQL per recuperare un elenco di ID utente e nomi degli autori. Questi dati vengono memorizzati come tuple (id, full name) in una mappa hash. Il servlet inoltra l'utente e la mappa hash a /form.jsp, che elabora la mappa hash dei nomi degli autori come mostrato nella sezione successiva.

Supporto delle interazioni del database in un modulo

Il seguente snippet utilizza JavaServer Pages (JSP) per visualizzare all'utente i dati iniziali della mappa hash del nome dell'autore trasmessi dal servlet e utilizza tali dati in un elenco di selezione. Il modulo consente inoltre all'utente di creare e aggiornare i dati esistenti.

<%@ 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>

Nello snippet precedente, il modulo viene compilato quando la pagina viene caricata con la mappa hash dei nomi degli autori trasmessi dal servlet. Il modulo utilizza le operazioni whenST (Java Tag Library) standard di JavaServer Pages (JSTL) e otherwise fornisce la logica if..else e i loop forEach tramite la mappa hash trasmessa dal servlet.

La pagina JSP nello snippet sopra contiene un modulo per la creazione di nuovi post del blog e l'aggiornamento di post esistenti. Tieni presente che il modulo può inviare dati ai gestori su /create o /update, a seconda che l'utente stia creando o aggiornando un post del blog.

Per ulteriori informazioni su come utilizzare i moduli, consulta la sezione sulla gestione dei dati POST.

Archiviazione dei record

Il seguente snippet mostra come creare un nuovo record dai dati forniti dall'utente nel modulo e archiviarlo nel database. L'esempio mostra un'istruzione SQL INSERT creata dai dati inviati nel modulo di creazione di post del blog descritto nella sezione precedente:

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

Lo snippet di codice utilizzato prende l'input dell'utente e lo esegue con jSoup per sanitare. L'utilizzo di jSoup e PreparedStatement riduce la possibilità di attacchi di inserimento di XSS e SQL.

La variabile createPostSql contiene la query INSERT con ? come segnaposto per i valori che verranno assegnati utilizzando il metodo PreparedStatement.set().

Osserva l'ordine dei campi della tabella a cui viene fatto riferimento nei metodi impostati di PreparedStatement. Ad esempio, author_id è un campo di tipo INT, quindi è necessario utilizzare setInt() per impostare author_id.

Recupero dei record in corso...

Il seguente snippet mostra un metodo doGet() di servlet che recupera le righe dalla tabella dei post del blog e le stampa.

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

I risultati dell'istruzione SELECT vengono inseriti in un ResultSet, che viene eseguito utilizzando il metodo ResultSet.get(). Nota che il ResultSet.get()metodo getString che corrisponde allo schema della tabella definito in precedenza.

In questo esempio, ogni post ha un link [Update] e un link [Delete], che viene utilizzato per avviare rispettivamente gli aggiornamenti e le eliminazioni dei post. Per offuscare l'ID del post, l'identificatore è codificato in Base64.

Aggiornamento dei record

Il seguente snippet mostra come aggiornare un record esistente:

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

In questo snippet, quando l'utente fa clic sul link [Aggiorna] in un post del blog, viene visualizzato il modulo JSP utilizzato per creare un nuovo post, ma ora questo campo viene precompilato con il titolo e i contenuti esistenti del post. Il nome dell'autore non viene visualizzato nell'anteprima poiché non cambierà.

L'aggiornamento di un post è simile a quello di creazione di un post, tranne per il fatto che viene utilizzata la query SQL UPDATE invece di INSERT.

Dopo aver eseguito executeUpdate(), l'utente viene reindirizzato a una pagina di conferma nello snippet.

Eliminazione dei record

L'eliminazione di una riga, un post del blog in questo esempio, richiede la rimozione di una riga dalla tabella di destinazione, che è la tabella content nell'esempio. Ogni record è identificato dal suo ID, che è il valore post_id nel codice campione. Utilizzerai questo ID come filtro nella query DELETE:

Dopo aver eseguito executeUpdate(), l'utente viene reindirizzato a una pagina di conferma.

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

}

Dopo aver decodificato l'ID del post, lo snippet eliminerà un singolo post dalla tabella posts.

Deployment in App Engine

Puoi eseguire il deployment dell'app in App Engine mediante Maven.

Vai alla directory principale del tuo progetto e digita:

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

Sostituisci PROJECT_ID con l'ID del progetto Cloud. Se il file pom.xml specifica il tuo ID progetto, non devi includere la proprietà -Dapp.deploy.projectId nel comando che esegui.

Dopo che Maven ha eseguito il deployment della tua app, apri automaticamente una scheda del browser web nella nuova app digitando:

gcloud app browse

Passaggi successivi

Cloud SQL è utile per archiviare dati basati su testo. Tuttavia, se vuoi archiviare elementi multimediali come le immagini, ti consigliamo di utilizzare Cloud Storage.

Successivamente, scopri come utilizzare le code di attività per eseguire attività asincrone seguendo un esempio di utilizzo dell'API Images per ridimensionare le immagini caricate in questa guida.