Memulai: Cloud SQL

Panduan ini memperluas contoh kode yang digunakan dalam Menangani data yang dikirim pengguna dengan menyimpan dan mengambil data menggunakan Google Cloud SQL.

Cloud SQL adalah salah satu opsi penyimpanan yang tersedia dengan App Engine, yang dapat diintegrasikan dengan mudah ke dalam aplikasi dan menyimpan data teks relasional. Bandingkan Cloud SQL, Cloud Datastore, dan Cloud Storage, lalu pilih salah satu yang memenuhi persyaratan aplikasi Anda.

Contoh ini dibuat berdasarkan serangkaian panduan dan menunjukkan cara menyimpan, memperbarui, dan menghapus data postingan blog di Cloud SQL.

Sebelum memulai

Mengonfigurasi lingkungan pengembangan dan membuat project App Engine.

Membuat instance Cloud SQL dan menghubungkan ke database

Anda harus membuat instance Cloud SQL dan menyiapkan koneksi ke instance tersebut dari aplikasi App Engine. Untuk mendapatkan petunjuk tentang cara menghubungkan ke Cloud SQL, lihat Menghubungkan ke App Engine.

Membuat tabel

Anda harus membuat objek Connection di metode init() servlet untuk menangani koneksi ke instance 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.
  }
}

Metode init() menyiapkan koneksi ke Cloud SQL, lalu membuat tabel content dan user jika tidak ada. Setelah metode init(), aplikasi siap untuk menyajikan dan menyimpan data baru.

Dalam cuplikan tersebut, pernyataan SQL pembuatan tabel disimpan dalam variabel String, yang dieksekusi dalam init() servlet melalui panggilan ke metode executeUpdate. Perhatikan bahwa ini tidak akan membuat tabel tersebut jika tabel itu sudah ada.

Dua tabel yang dibuat dalam cuplikan diberi nama posts dan users: posts menyimpan informasi spesifik dari setiap postingan blog, sedangkan users berisi informasi terkait penulis, seperti yang ditampilkan di sini:

Tabel: postingan

Kolom	Jenis
post_id	INT (penambahan otomatis, kunci utama)
author_id	INT
timestamp	DATETIME
title	VARCHAR (256)
body	VARCHAR (1337)

Tabel: pengguna

Kolom	Jenis
user_id	INT (penambahan otomatis, kunci utama)
user_fullname	VARCHAR (64)

Mengambil data awal untuk ditampilkan dalam formulir

Kasus penggunaan yang umum adalah mengisi formulir secara otomatis dengan data yang disimpan dalam database, untuk digunakan dalam pilihan pengguna. Contoh:

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

Dalam cuplikan kode di atas, servlet melakukan kueri ke database Cloud SQL untuk mengambil daftar ID pengguna dan nama penulis. Keduanya disimpan sebagai tuple (id, full name) dalam peta hash. Servlet kemudian meneruskan pengguna dan peta hash ke /form.jsp, yang memproses peta hash nama penulis seperti yang ditunjukkan di bagian berikutnya.

Mendukung interaksi database dalam formulir

Cuplikan berikut menggunakan JavaServer Pages (JSP) untuk menampilkan data awal dari peta hash nama penulis yang diteruskan dari servlet, dan menggunakan data tersebut dalam daftar pilihan kepada pengguna singkat ini. Dengan formulir tersebut, pengguna juga dapat membuat dan memperbarui data yang ada.

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

Dalam cuplikan di atas, formulir diisi saat halaman dimuat dengan peta hash nama penulis yang diteruskan dari servlet. Formulir ini menggunakan JavaServer Pages Standard Tag Library (JSTL)when dan otherwise operasi menyediakan if..else logika danforEach loop melalui peta hash yang diteruskan dari servlet.

Halaman JSP dalam cuplikan di atas berisi formulir untuk membuat postingan blog baru dan memperbarui postingan yang sudah ada. Perhatikan bahwa formulir dapat mengirim data ke pengendali di /create atau /update, bergantung pada apakah pengguna membuat atau memperbarui postingan blog.

Untuk informasi selengkapnya tentang cara menggunakan formulir, lihat Menangani data POST.

Menyimpan catatan

Cuplikan berikut menunjukkan cara membuat data baru dari data yang disediakan oleh pengguna dalam formulir, dan menyimpannya dalam database. Contoh ini menunjukkan pernyataan INSERT SQL yang dibuat dari data yang dikirimkan dalam formulir pembuatan postingan blog yang dijelaskan di bagian sebelumnya:

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

Cuplikan kode yang digunakan mengambil input pengguna dan menjalankannya melalui jSoup untuk membersihkannya. Menggunakan jSoup dan PreparedStatement mengurangi kemungkinan serangan injeksi XSS dan SQL.

Variabel createPostSql berisi kueri INSERT dengan ? sebagai placeholder untuk nilai yang akan ditetapkan menggunakan metode PreparedStatement.set().

Perhatikan urutan kolom tabel karena ini dirujuk dalam metode set PreparedStatement. Misalnya, author_id adalah kolom berjenis INT, sehingga setInt() harus digunakan untuk menetapkan author_id.

Mengambil kumpulan data

Cuplikan berikut menunjukkan metode doGet() servlet yang mengambil baris dari tabel postingan blog dan mencetaknya.

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

Hasil dari pernyataan SELECT dimasukkan ke dalam ResultSet, yang diiterasi setelah menggunakan metode ResultSet.get(). Perhatikan metode ResultSet.get() getString yang sesuai dengan skema tabel yang ditentukan sebelumnya.

Untuk contoh ini, setiap postingan memiliki link [Update] dan [Delete], yang masing-masing digunakan untuk memulai pembaruan dan penghapusan postingan. Untuk meng-obfuscate ID postingan, ID tersebut dienkode dalam Base64.

Memperbarui kumpulan data

Cuplikan berikut menunjukkan cara memperbarui data yang ada:

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

Dalam cuplikan ini, saat pengguna mengklik link [Update] pada postingan blog, formulir JSP yang digunakan untuk membuat postingan baru akan ditampilkan. Namun, kini formulir ini telah diisi sebelumnya dengan judul dan isi postingan yang ada. Nama penulis tidak ditampilkan dalam sampel karena tidak akan berubah.

Memperbarui postingan mirip dengan membuat postingan, hanya saja kueri UPDATE SQL digunakan sebagai ganti INSERT.

Setelah menjalankan executeUpdate(), pengguna akan dialihkan ke halaman konfirmasi dalam cuplikan.

Menghapus kumpulan data

Penghapusan baris, postingan blog dalam contoh ini, mengharuskan penghapusan baris dari tabel target, yaitu tabel content dalam contoh. Setiap data diidentifikasi melalui ID-nya, yang merupakan nilai post_id dalam kode contoh. Anda menggunakan ID ini sebagai filter dalam kueri DELETE:

Setelah menjalankan executeUpdate(), pengguna akan dialihkan ke halaman konfirmasi.

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

}

Setelah mendekode ID postingan, cuplikan akan menghapus satu postingan dari tabel posts.

Men-deploy ke App Engine

Anda dapat men-deploy aplikasi ke App Engine menggunakan Maven.

Buka direktori root project Anda dan ketik:

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

Ganti PROJECT_ID dengan ID project Google Cloud Anda. Jika file pom.xml sudah menentukan ID project, Anda tidak perlu menyertakan properti -Dapp.deploy.projectId dalam perintah yang dijalankan.

Setelah Maven men-deploy aplikasi Anda, buka tab browser web secara otomatis di aplikasi baru dengan mengetik:

gcloud app browse

Langkah berikutnya

Cloud SQL berguna untuk menyimpan data berbasis teks; namun, jika ingin menyimpan multimedia seperti gambar, Anda harus mempertimbangkan untuk menggunakan Cloud Storage.

Selanjutnya, pelajari penggunaan task queue untuk melakukan tugas asinkron dengan mengikuti contoh penggunaan Images API untuk mengubah ukuran gambar yang diupload dalam panduan ini.