使用入门：Cloud SQL

本指南通过使用 Google Cloud SQL 存储和检索数据，扩展处理用户提交的数据中使用的代码示例。

Cloud SQL 是一种适用于 App Engine 的存储方案，可轻松集成到应用，也可用于存储关系型文本数据。比较 Cloud SQL、Cloud Datastore 和 Cloud Storage，选择符合您应用要求的方案。

本示例基于一系列指南构建，展示了如何在 Cloud SQL 中存储、更新和删除博文数据。

准备工作

配置开发环境并创建 App Engine 项目。

创建 Cloud SQL 实例并连接到数据库

您需要创建一个 Cloud SQL 实例并通过 App Engine 应用建立与它的连接。如需了解如何连接到 Cloud SQL，请参阅连接到 App Engine。

创建表

您必须在 servlet init() 方法中创建一个 Connection 对象来处理与 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.
  }
}

init() 方法会建立与 Cloud SQL 的连接，然后创建 content 和 user 表（如果这两个表不存在）。完成 init() 方法后，应用随时可以传送和存储新数据。

在代码段中，表创建 SQL 语句存储在 String 变量中，这些变量通过调用 executeUpdate 方法在 servlet 的 init() 中执行。请注意，如果这些表已存在，则不会重新创建。

该代码段中创建的两个表名为 posts 和 users：posts 存储各篇博文的详细信息，而 users 包含关于作者的信息，如下所示：

表：posts

字段	类型
post_id	INT（自动增加，主键）
author_id	INT
timestamp	DATETIME
title	VARCHAR (256)
body	VARCHAR (1337)

表：users

字段	类型
user_id	INT（自动增加，主键）
user_fullname	VARCHAR (64)

检索初始数据以显示在表单中

常见用例是使用数据库中存储的数据来预先填充表单，便于供用户选择。例如：

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

在上方代码段中，servlet 查询 Cloud SQL 数据库以检索用户 ID 和作者姓名列表。这些信息作为 (id, full name) 元组存储在哈希映射中。servlet 随后将用户和哈希映射转发到 /form.jsp，以处理作者姓名的哈希映射，具体如下一部分所示。

支持表单中的数据库交互

以下代码段使用 JavaServer Pages (JSP) 向用户显示从 servlet 传递的作者姓名哈希映射中的初始数据，并在选择列表中使用该数据。用户还可以通过该表单创建和更新现有数据。

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

在上述代码段中，当页面加载从 servlet 传递的作者姓名哈希映射时，表单将进行填充。该表单使用 JavaServer Pages 标准标记库 (JSTL) when 和 otherwise 运算符，通过从 servlet 传递的哈希映射提供 if..else 逻辑与 forEach 循环。

上述代码段中的 JSP 页面包含一个用于创建新博文和更新现有博文的表单。请注意，表单可将数据发送至 /create 或 /update 的处理程序，具体取决于用户是创建还是更新博文。

如需详细了解如何使用表单，请参阅处理博文数据。

存储记录

以下代码段展示了如何根据用户在表单中提供的数据构建新记录，并将其存储在数据库中。此示例中所示的 SQL INSERT 语句是根据上一部分所述的博文创建表单所提交的数据构建的。

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

该代码段选取用户输入的内容，并通过 jSoup 运行对其进行清理。使用 jSoup 和 PreparedStatement 可降低 XSS 与 SQL 注入式攻击的可能性。

对于将使用 PreparedStatement.set() 方法分配的值，createPostSql 变量包含 INSERT 查询，并以 ? 作为占位符。

请注意表字段的顺序，因为 PreparedStatement set 方法中会引用这些字段。例如，author_id 是 INT 类型的字段，因此必须使用 setInt() 来设置 author_id。

检索记录

以下代码段展示了 servlet 的 doGet() 方法，该方法从博文的表中提取行，并将其打印出来。

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

SELECT 语句的结果包含在 ResultSet 中，将使用 ResultSet.get() 方法对该结果集进行迭代。请注意与之前定义的表架构相对应的 ResultSet.get() 方法 getString。

在此示例中，每个博文带有 [Update] 和 [Delete] 链接，分别用于启动博文更新和删除。为混淆博文的 ID，标识符以 Base64 编码。

更新记录

以下代码段展示了如何更新现有记录：

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

在此代码段中，当用户点击博文中的 [更新] 链接时，系统会显示 JSP 表单，此表单用于创建新博文，但如今使用现有博文的标题和内容进行预先填充。示例中未显示作者的姓名，因为姓名不会改变。

更新博文与创建博文类似，只不过使用的是 SQL UPDATE 查询而非 INSERT。

运行 executeUpdate() 后，用户重定向至代码段中的确认页面。

删除记录

删除行（在本示例中为博文）需要从目标表中移除行，在本示例中为 content 表。每个记录由其 ID 标识，在示例代码中为 post_id 值。在 DELETE 查询中，您使用此 ID 作为过滤条件：

运行 executeUpdate() 后，用户重定向至确认页面。

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

}

对博文的 ID 进行解码后，代码段将从 posts 表中删除单个博文。

部署到 App Engine

您可以使用 Maven 将应用部署到 App Engine。

转到项目的根目录并输入以下命令：

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

将 PROJECT_ID 替换为您的 Google Cloud 项目的 ID。 如果您的 pom.xml 文件已经指定了您的项目 ID，则您无需在运行的命令中添加 -Dapp.deploy.projectId 属性。

Maven 完成应用部署后，输入以下命令可在新应用中自动打开网络浏览器标签：

gcloud app browse

后续步骤

Cloud SQL 对于存储基于文本的数据非常有用；但是，如果要存储图片等富媒体，则应考虑使用 Cloud Storage。

接下来，通过学习使用 Images API 调整本指南中上传的图片大小的示例，您将了解如何使用任务队列执行异步任务。