開始使用：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 變數中，然後在 Servlet 的 init() 內透過呼叫 executeUpdate 方法來執行。請注意，如果這些資料表已存在，這項操作就不會建立這些資料表。

系統會將在程式碼片段中建立的這兩個資料表分別命名為 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 Standard Tag Library (JSTL) 的 when 和 otherwise 運算，並透過從 Servlet 傳入的雜湊對應提供 if..else 邏輯和 forEach 迴圈。

上方程式碼片段裡的 JSP 頁面包含一份用於建立新網誌文章及更新現有文章的表單。請注意，這份表單會根據使用者目前正在建立或是更新網誌文章，將資料分別傳送至 /create 或 /update 的處理常式。

如要進一步瞭解如何使用表單，請參閱處理 POST 資料。

儲存記錄

下列程式碼片段顯示了如何依據使用者在表單中提供的資料建立新的記錄，並儲存至資料庫。這個範例中呈現的 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 資料隱碼攻擊的可能性。

createPostSql 變數包含 INSERT 查詢，其中 ? 是用於指派 PreparedStatement.set() 方法值的預留位置。

請注意資料表欄位的順序，因為 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，該 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);
  }
}

在這個程式碼片段中，每當使用者點選網誌文章中的 [Update] (更新) 連結時，系統就會顯示用於建立新文章的 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 調整您在本指南中上傳的圖片大小，學習使用工作佇列執行非同步工作。

上一頁
處理 HTML 表單資料
下一頁
非同步執行程式碼