Primeros pasos con Cloud SQL

Esta guía amplía la muestra del código usada en Manejar datos enviados por el usuario almacenando y recuperando datos con Google Cloud SQL.

Cloud SQL es la única opción de almacenamiento disponible con App Engine que se puede integrar de manera sencilla en las aplicaciones y almacenar datos de texto. Compara Cloud Datastore, Cloud SQL y Cloud Storage y elige la que mejor se adapte a los requisitos de tu app.

Esta muestra se basa en una serie de guías y muestra cómo almacenar, actualizar y borrar datos de entradas de blog en Cloud SQL.

Antes de comenzar

Configura tu entorno de desarrollo y crea tu proyecto de App Engine.

Crea una instancia de Cloud SQL y conéctate a la base de datos

Deberás crear una instancia de Cloud SQL y establecer una conexión con ella desde tu app de App Engine. Si deseas obtener instrucciones para conectarte a Cloud SQL, consulta la sección sobre cómo conéctarte a App Engine.

Crea tablas

Debes crear un objeto Connection en el método init() del servlet para manejar la conexión a la instancia de 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.
  }
}

El método init() establece una conexión con Cloud SQL y, luego, crea las tablas content y user si no existen. Después de establecer el método init(), la app está lista para entregar y almacenar datos nuevos.

En el fragmento, las instrucciones de SQL de creación de tablas se almacenan en variables de String, que se ejecutan dentro del init() del servlet a través de una llamada al método executeUpdate. Ten en cuenta que no se crearán las tablas si estas ya existen.

Las dos tablas creadas en el fragmento se llaman posts y users: posts contiene los detalles de cada entrada de blog, mientras que users contiene información sobre el autor, como se muestra aquí:

Tabla: publicaciones

Campo Tipo
post_id INT (incremento automático, clave primaria)
author_id INT
timestamp DATETIME
title VARCHAR (256)
body VARCHAR (1337)

Tabla: usuarios

Campo Tipo
user_id INT (incremento automático, clave primaria)
user_fullname VARCHAR (64)

Cómo recuperar datos iniciales para mostrar en un formulario

Un caso práctico común es propagar previamente un formulario con datos almacenados en la base de datos, para usarlo en selecciones de usuarios. Por ejemplo:

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

En el fragmento de código anterior, el servlet consulta la base de datos de Cloud SQL para recuperar una lista de ID de usuarios y nombres de autores. Se almacenan como tuplas (id, full name) en un mapa hash. Luego, el servlet reenvía el usuario y el mapa hash a /form.jsp, que procesa el mapa hash de los nombres de los autores como se muestra en la siguiente sección.

Compatibiliza interacciones de bases de datos en un formulario

El siguiente fragmento usa JavaServer Pages (JSP) para mostrar al usuario los datos iniciales del mapa hash del nombre del autor que se transfirió desde el servlet, y utiliza esos datos en una lista de selección. El formulario también permite al usuario crear y actualizar datos existentes.

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

En el fragmento anterior, el formulario se propaga cuando la página se carga con el mapa hash de los nombres de los autores transferidos desde el servlet. El formulario usa las operaciones when y otherwise de la Biblioteca de etiquetas estándar de JavaServer Pages (JSTL), proporciona lógica if..else y realiza bucles forEach a través del mapa hash transferido desde el servlet.

La página JSP en el fragmento anterior contiene un formulario para crear entradas de blog nuevas y actualizar las entradas existentes. Ten en cuenta que el formulario puede enviar los datos a los controladores en /create o /update según si el usuario está creando o actualizando una entrada de blog.

Para obtener más información sobre cómo usar formularios, consulta la sección sobre cómo manejar datos POST.

Cómo almacenar registros

En el siguiente fragmento, se muestra cómo compilar un registro nuevo a partir de los datos proporcionados por el usuario en el formulario y almacenarlo en la base de datos. El ejemplo muestra una instrucción INSERT de SQL compilada a partir de los datos enviados en el formulario de creación de la entrada de blog descrita en la sección anterior:

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

El fragmento de código que se usa toma la entrada del usuario y la ejecuta a través de jSoup para limpiarla. Usar jSoup y PreparedStatement disminuye la posibilidad de ataques de inyección de XSS y SQL.

La variable createPostSql contiene la consulta INSERT con ? como marcadores de posición para valores que se asignarán con el método PreparedStatement.set().

Ten en cuenta el orden de los campos de tabla como se hace referencia a ellos en los métodos de conjunto PreparedStatement. Por ejemplo, author_id es un campo de tipo INT, por lo que se debe usar setInt() para configurar author_id.

Recupera registros

El siguiente fragmento muestra el método doGet() de un servlet que recupera las filas de la tabla de entradas de blog y las imprime.

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

Los resultados de la instrucción SELECT se colocan en un ResultSet, que se itera cuando se usa el método ResultSet.get(). Ten en cuenta la getString del método ResultSet.get() que corresponde al esquema de la tabla definido anteriormente.

Para este ejemplo, cada entrada tiene un vínculo [Update] y un vínculo [Delete], que se usan a fin de iniciar actualizaciones y eliminaciones de entradas, respectivamente. Para ofuscar el ID de la entrada, el identificador se codifica en Base64.

Actualiza registros

El siguiente fragmento muestra cómo actualizar un registro existente:

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

En este fragmento, cuando el usuario hace clic en el vínculo [Actualizar] en una entrada de blog, este muestra el formulario JSP utilizado para crear una entrada nueva, pero que ya está previamente propagado con el título y el contenido de la entrada existente. El nombre del autor no se muestra en la muestra porque no cambiará.

Actualizar una entrada es similar a crear una, con la salvedad de que se usa la consulta de SQL UPDATE en lugar de INSERT.

Después de ejecutar executeUpdate(), se redirecciona al usuario a la página de confirmación en el fragmento.

Borra registros

Borrar una fila, una entrada de blog en este ejemplo, requiere la eliminación de una fila de la tabla de destino, que es la tabla content en el ejemplo. Cada registro se identifica por su ID, que es el valor post_id en el código de muestra. Tú usas este ID como filtro en la consulta DELETE:

Después de ejecutar executeUpdate(), se redirecciona al usuario a la página de confirmación.

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

}

Después de decodificar el ID de la entrada, el fragmento borrará una sola entrada de la tabla posts.

Implementa en App Engine

Puedes implementar tu aplicación en App Engine con Maven.

Ve al directorio raíz de tu proyecto y escribe lo siguiente:

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

Reemplaza PROJECT_ID por el ID del proyecto de Google Cloud. Si tu archivo pom.xml ya especifica tu ID del proyecto, no necesitas incluir la propiedad -Dapp.deploy.projectId en el comando que ejecutas.

Luego de que Maven implemente tu aplicación, escribe lo siguiente para abrir una pestaña del navegador web de forma automática en tu nueva aplicación:

gcloud app browse

Qué sigue

Cloud SQL es útil para almacenar datos basados en texto; sin embargo, si deseas almacenar imágenes, deberías considerar usar Cloud Storage.

A continuación, aprende sobre el uso de listas de tareas en cola a fin de realizar tareas asíncronas siguiendo un ejemplo del uso de la API de Images para cambiar el tamaño de las imágenes subidas en esta guía.

Anterior
Maneja los datos de formularios HTML
Siguiente
Ejecuta código en forma asíncrona