Introdução: Cloud SQL

Este guia expande o exemplo de código usado em Processamento de dados enviados pelos utilizadores através do armazenamento e da obtenção de dados com o Google Cloud SQL.

O Cloud SQL é uma opção de armazenamento disponível com o App Engine que pode ser facilmente integrada em apps e armazenar dados de texto relacionais. Compare o Cloud SQL, o Cloud Datastore e o Cloud Storage e escolha o que cumpre os requisitos da sua app.

Este exemplo baseia-se numa série de guias e mostra como armazenar, atualizar e eliminar dados de publicações de blogue no Cloud SQL.

Antes de começar

Configure o ambiente de programação e crie o projeto do App Engine.

Criar uma instância do Cloud SQL e estabelecer ligação à base de dados

Tem de criar uma instância do Cloud SQL e configurar uma ligação à mesma a partir da sua app do App Engine. Para obter instruções sobre como estabelecer ligação ao Cloud SQL, consulte o artigo Estabelecer ligação ao App Engine.

Criar tabelas

Tem de criar um objeto Connection no método init() do servlet para processar a ligação à 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 ligação ao Cloud SQL e, em seguida, cria as tabelas content e user, se não existirem. Após o método init(), a app está pronta para publicar e armazenar novos dados.

No fragmento, as declarações SQL de criação de tabelas são armazenadas em String variáveis, que são executadas no init() do servlet através da chamada ao método executeUpdate. Tenha em atenção que isto não cria essas tabelas se já existirem.

As duas tabelas criadas no fragmento são denominadas posts e users: posts contém os detalhes de cada publicação no blogue, enquanto users contém informações sobre o autor, conforme mostrado aqui:

Tabela: publicações

Campo	Tipo
post_id	INT (incremento automático, chave principal)
author_id	INT
timestamp	DATA/HORA
título	VARCHAR (256)
body	VARCHAR (1337)

Tabela: utilizadores

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

Obter dados iniciais para apresentar num formulário

Um exemplo de utilização comum é pré-preencher um formulário com dados armazenados na base de dados para utilização em seleções de utilizadores. 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 fragmento de código acima, o servlet consulta a base de dados do Cloud SQL para obter uma lista de IDs de utilizadores e nomes de autores. Estes são armazenados como tuplos (id, full name) num mapa de hash. Em seguida, o servlet encaminha o utilizador e o mapa de hash para /form.jsp, que processa o mapa de hash dos nomes dos autores, conforme mostrado na secção seguinte.

Suporte de interações com bases de dados num formulário

O fragmento seguinte usa JavaServer Pages (JSP) para apresentar ao utilizador os dados iniciais do mapa hash do nome do autor transmitido do servlet e usa esses dados numa lista de seleção. O formulário também permite ao utilizador 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 fragmento 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 a biblioteca de etiquetas padrão (JSTL) when e as operações otherwise fornecem lógica if..else e forEach ciclos através do mapa hash transmitido do servlet.

A página JSP no fragmento acima contém um formulário para criar novas publicações no blogue e atualizar as publicações existentes. Tenha em atenção que o formulário pode enviar os dados para processadores em /create ou /update, consoante o utilizador esteja a criar ou a atualizar uma publicação no blogue.

Para mais informações sobre como usar formulários, consulte o artigo Processamento de dados POST.

Armazenamento de registos

O fragmento seguinte mostra como criar um novo registo a partir dos dados fornecidos pelo utilizador no formulário e armazená-lo na base de dados. O exemplo mostra uma declaração SQL INSERT criada a partir dos dados enviados no formulário de criação de publicações no blogue descrito na secçã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 fragmento do código usa a entrada do utilizador e executa-a através do jSoup para a limpar. Usar o jSoup e PreparedStatement mitigar a possibilidade de ataques de injeção SQL e XSS.

A variável createPostSql contém a consulta INSERT com ? como marcadores de posição para valores que vão ser atribuídos através do método PreparedStatement.set().

Tenha em atenção a ordem dos campos da tabela, uma vez que são referenciados nos métodos de definição PreparedStatement. Por exemplo, author_id é um campo do tipo INT, pelo que tem de usar setInt() para definir author_id.

A obter registos

O fragmento seguinte mostra o método doGet() de um servlet que obtém as linhas da tabela de publicações do blogue 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 declaração SELECT são colocados num ResultSet, que é iterado através do método ResultSet.get(). Tenha em atenção o ResultSet.get()métodogetString que corresponde ao esquema da tabela definido anteriormente.

Neste exemplo, cada publicação tem um link [Update] e um link [Delete], que são usados para iniciar atualizações e eliminações de publicações, respetivamente. Para ocultar o ID da publicação, o identificador é codificado em Base64.

A atualizar registos

O fragmento seguinte mostra como atualizar um registo 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);
  }
}

Neste fragmento, quando o utilizador clica no link [Atualizar] numa publicação no blogue, é apresentado o formulário JSP usado para criar uma nova publicação, mas agora este está pré-preenchido com o título e o conteúdo da publicação existente. O nome do autor não é apresentado na amostra porque não vai ser alterado.

A atualização de uma publicação é semelhante à criação de uma publicação, exceto que a consulta SQL UPDATE é usada em vez de INSERT.

Após a execução de executeUpdate(), o utilizador é redirecionado para uma página de confirmação no fragmento.

Eliminar registos

A eliminação de uma linha, uma publicação no blogue neste exemplo, requer a remoção de uma linha da tabela de destino, que é a tabela content no exemplo. Cada registo é identificado pelo respetivo ID, que é o valor post_id no código de exemplo. Use este ID como filtro na consulta DELETE:

Após a execução de executeUpdate(), o utilizador é 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);
  }

}

Após descodificar o ID da publicação, o fragmento elimina uma única publicação da tabela posts.

Implementação no App Engine

Pode implementar a sua app no App Engine através do Maven.

Aceda ao diretório raiz do seu projeto e escreva:

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

Substitua PROJECT_ID pelo ID do seu Google Cloud projeto. Se o seu ficheiro pom.xml já especificar o seu ID do projeto, não precisa de incluir a propriedade -Dapp.deploy.projectId no comando que executar.

Depois de o Maven implementar a sua app, é aberto automaticamente um separador do navegador de Internet na nova app. Para tal, escreva:

gcloud app browse

O que se segue?

O Cloud SQL é útil para armazenar dados baseados em texto. No entanto, se quiser armazenar conteúdo multimédia avançado, como imagens, deve considerar usar o Cloud Storage.

Em seguida, saiba como usar filas de tarefas para realizar tarefas assíncronas seguindo um exemplo de utilização da API Images para redimensionar as imagens carregadas neste guia.