Primeiros passos: Cloud SQL

Este guia amplia o exemplo de código usado em Como processar dados enviados pelo usuário com o armazenamento e recuperação de dados com o uso do Google Cloud SQL.

O Cloud SQL é uma opção de armazenamento disponível com o App Engine que pode armazenar dados de texto relacional e ser facilmente integrada a aplicativos. Compare o Cloud SQL, o Cloud Datastore e o Cloud Storage e escolha aquele que atenda aos requisitos do seu aplicativo.

Nesta amostra, oferecemos uma série de guias e mostramos como armazenar, atualizar e excluir dados de postagem do blog no Cloud SQL.

Antes de começar

Configure o ambiente de desenvolvimento e crie o projeto do App Engine.

Como criar uma instância do Cloud SQL e conectar-se ao banco de dados

Você precisará criar uma instância do Cloud SQL e configurar uma conexão com ela a partir do aplicativo do App Engine. Veja instruções sobre como se conectar ao Cloud SQL em Conectando-se ao App Engine.

Criar tabelas

Crie um objeto Connection no método init() do servlet para processar a conexão com a instância do 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.
  }
}

O método init() configura uma conexão com o Cloud SQL e cria as tabelas content e user se elas não existirem. Após o método init(), o aplicativo estará pronto para exibir e armazenar novos dados.

No snippet, as instruções SQL de criação de tabela são armazenadas em variáveis String, que são executadas no init() do servlet por meio da chamada para o método executeUpdate. As tabelas não serão criadas se elas já existirem.

As duas tabelas criadas no snippet são chamadas de posts e users: posts contém os detalhes de cada postagem do blog, enquanto users contém informações sobre o autor, conforme mostrado aqui:

Tabela: postagens

Campo	Tipo
post_id	INT (incremento automático, chave primária)
author_id	INT
timestamp	DATETIME
title	VARCHAR (256)
body	VARCHAR (1337)

Tabela: usuários

Campo	Tipo
user_id	INT (incremento automático, chave primária)
user_fullname	VARCHAR (64)

Como recuperar dados iniciais para mostrar em um formulário

Um caso de uso comum é pré-preencher um formulário com dados armazenados no banco de dados, para uso em seleções de usuários. Por exemplo:

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

No snippet de código acima, o servlet consulta o banco de dados do Cloud SQL para recuperar uma lista de IDs de usuários e nomes de autores. Eles são armazenados como tuplas (id, full name) em um mapa de hash. Em seguida, o servlet encaminha o usuário e o mapa de hash para /form.jsp, que processa o mapa de hash dos nomes dos autores, conforme mostrado na próxima seção.

Interações de banco de dados de suporte em um formulário

O snippet a seguir usa JavaServer Pages (JSP) para exibir ao usuário os dados iniciais do mapa de hash do nome do autor transmitido do servlet e usa esses dados em uma lista de seleção. O formulário também permite ao usuário criar e atualizar dados 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>

No snippet acima, o formulário é preenchido quando a página é carregada com o mapa hash dos nomes dos autores transmitidos ​​do servlet. O formulário usa as operações when e otherwise da JavaServer Pages Standard Tag Library (JSTL), fornece a lógica if..else e os loops forEach por meio do mapa de hash transmitido do servlet.

A página JSP no snippet acima contém um formulário para criar novas postagens de blog e atualizar postagens existentes. Observe que o formulário pode enviar os dados para os gerenciadores em /create ou /update, conforme o usuário está criando ou atualizando uma postagem do blog.

Para mais informações sobre como usar formulários, consulte Como processar dados POST.

Como armazenar registros

O snippet a seguir mostra como criar um registro com base em dados fornecidos pelo usuário no formulário e armazená-lo no banco de dados. O exemplo mostra uma instrução INSERT SQL compilada a partir dos dados enviados no formulário de criação de postagem do blog descrito na seção 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);
  }
}

O snippet de código usa a entrada do usuário e a executa por meio de jSoup para limpá-lo. Usar o jSoup e o PreparedStatement reduz a possibilidade de ataques de injeção de XSS e SQL.

A variável createPostSql contém a consulta INSERT com ? como marcadores para valores que serão atribuídos usando o método PreparedStatement.set().

Observe a ordem dos campos da tabela conforme eles são referenciados nos métodos do conjunto PreparedStatement. Por exemplo, o author_id é um campo do tipo INT. Portanto, setInt() precisa ser usado para definir o author_id.

Como recuperar registros

O snippet a seguir mostra um método doGet() do servlet que busca as linhas da tabela de postagens do blog e as 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);
  }
}

Os resultados da instrução SELECT são colocados em um ResultSet, que é iterado com o uso do método ResultSet.get(). Observe o getString do método ResultSet.get() que corresponde ao esquema de tabela definido anteriormente.

Para este exemplo, cada postagem tem um link [Update] e [Delete], que é usado para iniciar atualizações e exclusões de postagens, respectivamente. Para ofuscar o ID da postagem, o identificador é codificado em Base64.

Como atualizar registros

O snippet a seguir mostra como atualizar um 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);
  }
}

Nesse snippet, quando o usuário clica no link [Atualizar] em uma postagem no blog, ele exibe o formulário JSP usado para criar uma nova postagem, mas agora está pré-preenchido com o título e conteúdo da postagem existente. O nome do autor não é exibido no exemplo porque ele não muda.

As operações de atualização e de criação de uma postagem são semelhantes, exceto que a consulta UPDATE SQL é usada em vez de INSERT.

Depois de executar executeUpdate(), o usuário é redirecionado para uma página de confirmação no snippet.

Como excluir registros

A exclusão de uma linha, uma postagem do blog neste exemplo, requer a remoção de uma linha da tabela de destino, que é a tabela content do exemplo. Cada registro é identificado pelo ID, que é o valor post_id no exemplo de código. Use esse ID como filtro na consulta DELETE:

Depois de executar executeUpdate(), o usuário é redirecionado para uma página de confirmação.

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

}

Depois de decodificar o ID da postagem, o snippet excluirá uma única postagem da tabela posts.

Como implantar no App Engine

Implante o app no App Engine usando o Maven.

Acesse o diretório raiz do projeto e digite:

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

Substitua PROJECT_ID pelo ID do projeto do Google Cloud. Se o arquivo pom.xml já especificar o ID do projeto, não será necessário incluir a propriedade -Dapp.deploy.projectId no comando executado.

Depois que o Maven implantar o aplicativo, digite o comando a seguir para abrir uma guia do navegador da Web automaticamente em seu novo aplicativo:

gcloud app browse

A seguir

O Cloud SQL é útil para armazenar dados baseados em texto. No entanto, se você quiser armazenar rich media, como imagens, precisará considerar o uso do Cloud Storage.

Para saber mais sobre o uso de filas de tarefas para executar tarefas assíncronas, siga um exemplo de uso da API Images e redimensione as imagens carregadas neste guia.