入門: Cloud SQL

このガイドでは、ユーザーが送信したデータの処理で使ったサンプルコードを変更して、Google Cloud SQL でデータの保存と取得を行います。

Cloud SQL は App Engine で利用できるストレージ オプションの 1 つで、簡単にアプリに統合したり、テキストデータを保存したりできます。Cloud SQL、Cloud Datastore、Cloud Storage を比較して、アプリの要件に最適なオプションを選択してください。

このサンプルは一連のガイドを通じて作成されています。ここでは、サンプルを使用して、Cloud SQL でブログ投稿データの保存、更新、削除を行う方法を学習します。

始める前に

開発環境を構成し、App Engine プロジェクトを作成します

Cloud SQL インスタンスとデータベースを作成する

Cloud SQL インスタンスとデータベースを作成するには:

  1. 第 2 世代の Cloud SQL インスタンスを作成して、ルートユーザーを構成します。ルートユーザーで接続することも可能ですが、ユーザーを作成することをおすすめします。
  2. Cloud SDK を使用して、アプリケーション コード内で接続文字列として使用する Cloud SQL インスタンスの接続名を取得します。
    gcloud sql instances describe [INSTANCE_NAME]
    connectionName に返された値を記録します。この値は Google Cloud Platform Console のインスタンスの詳細ページでも確認できます。たとえば、Cloud SDK の出力は次のようになります。
    gcloud sql instances describe [INSTANCE_NAME]
      connectionName: project1:us-central1:instance1
  3. Cloud SQL インスタンスにデータベースを作成します。この例では、データベースの名前は content です。
    gcloud sql databases create content --instance=[INSTANCE_NAME]

    詳細については、データベースの作成をご覧ください。

データベース接続を設定する

アプリケーションのシステム プロファイルに、Cloud SQL インスタンス接続名、データベース、ユーザー、パスワードを設定するには:

  1. プロジェクトの pom.xml ファイルで最上位の <properties> セクションに、Cloud SQL インスタンス接続名、データベース、ユーザー、パスワードを追加します。

    <properties>
      [...]
      <INSTANCE_CONNECTION_NAME>[INSTANCE_CONNECTION_NAME]</INSTANCE_CONNECTION_NAME>
      <user>[USERNAME]</user>
      <password>[PASSWORD]</password>
      <database>[DATABASE-NAME]</database>
    </properties>
    
  2. アプリがデータにアクセスできるように、appengine-web.xml に Cloud SQL 接続プロパティを設定します。

    <appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
      <threadsafe>true</threadsafe>
      <runtime>java8</runtime>
      <use-google-connector-j>true</use-google-connector-j>
    </appengine-web-app>
    

Cloud SQL に接続してテーブルを作成する

Cloud SQL インスタンスへの接続を処理するには、サーブレットの init() メソッドで Connection オブジェクトを作成する必要があります。

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 メソッドの呼び出しによりサーブレット内の init() で実行されます。テーブルが存在する場合、これらのテーブルは作成されません。

スニペッドで作成される 2 つのテーブルには contentuser という名前が付きます。content には、ブログ投稿記事の詳細が格納され、user には作成者に関する情報が格納されます。

テーブル: content

項目
post_id INT(自動増分、プライマリー キー)
author_id INT
timestamp DATETIME
title VARCHAR (256)
body VARCHAR (1337)

テーブル: user

項目
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.get529ggInt("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);
  }
}

上のコード部分では、サーブレットが Cloud SQL データベースにクエリを送信し、ユーザー ID と作者名のリストを取得します。これらは、(id, full name) タプルとしてハッシュマップに保存されます。サーブレットがユーザーとハッシュマップを /form.jsp に転送し、ここで作者名のハッシュマップが処理されます(次のセクションをご覧ください)。

フォームで可能なデータベース操作

次のスニペットでは、JavaServer Pages(JSP)を使用して、サーブレットから渡されたハッシュマップから作者名の初期データを取得し、ユーザーに表示します。このフォームでは、既存データの作成や更新も可能です。

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

上のスニペットでは、サーブレットから渡された作者名のハッシュマップを使用してページが読み込まれ、フォームが更新されます。このフォームでは、JavaServer Pages Standard Tag Library(JSTL)の whenotherwise オペレーションを使用し、サーブレットから渡されたハッシュマップに 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 型のため、author_id を設定する場合には setInt() を使用する必要があります。

レコードを取得する

次のスニペットでは、サーブレットの 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 フォームが表示されますが、今回は既存の投稿のタイトルとコンテンツが挿入されています。作者名は変更されないため、このサンプルでは表示されません。

投稿の更新は、投稿の作成と似ていますが、INSERT ではなく SQL UPDATE クエリを使用します。

executeUpdate() の実行後、スニペットの確認ページに移動します。

レコードを削除する

この例のブログ投稿では、行を削除するときに、対象テーブル(この例では content)から行を削除する削除する必要があります。レコードは ID で識別されます。サンプルコードでは post_id の値が 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 テーブルから 1 つの投稿が削除されます。

App Engine にデプロイする

Maven を使用して App Engine にアプリをデプロイします。

プロジェクトのルート ディレクトリに移動し、次のように入力します。

mvn appengine:deploy

Maven によってアプリがデプロイされた後、新しいアプリで次のように入力すると、ウェブブラウザのタブが自動的に開きます。

gcloud app browse

次のステップ

Cloud SQL は、テキストベースのデータを保存するのに便利です。ただし、画像などのリッチメディアを保存する場合は、Cloud Storage の使用を検討する必要があります。

Cloud Storage の使用

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

Java の App Engine スタンダード環境