시작하기: 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에 연결을 참조하세요.

테이블 만들기

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 변수에 저장됩니다. 이 변수는 서블릿의 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);
  }
}

위 코드 스니펫에서 서블릿은 사용자 ID 및 작성자 이름 목록을 검색하기 위해 Cloud SQL 데이터베이스를 쿼리합니다. 이러한 정보는 해시 맵에 (id, full name) 튜플로 저장됩니다. 그런 다음 서블릿은 사용자 및 해시 맵을 /form.jsp로 전달하고, 이는 다음 섹션에 나와 있는 것처럼 작성자 이름 해시 맵을 처리합니다.

양식에서 데이터베이스 상호작용 지원

다음 스니펫은 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>

위 스니펫에서는 페이지가 로드될 때 서블릿에서 전달된 작성자 이름 해시 맵으로 양식이 채워집니다. 이 양식은 JSTL(자바 서버 페이지 표준 태그 라이브러리) when 및 otherwise 작업을 사용하고 서블릿에서 전달된 해시 맵을 통해 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를 설정해야 합니다.

레코드 검색

다음 스니펫에서는 블로그 게시물 테이블에서 행을 가져와 출력하는 서블릿의 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);
  }
}

이 스니펫에서 사용자가 블로그 게시물의 [Update] 링크를 클릭하면 새 게시물을 만드는 데 사용되는 JSP 양식이 표시됩니다. 하지만 이제는 이 양식이 기존 게시물의 제목 및 콘텐츠로 미리 채워집니다. 작성자 이름은 변경되지 않기 때문에 샘플에 표시되지 않습니다.

게시물의 업데이트 작업은 INSERT 대신 SQL UPDATE 쿼리를 사용한다는 것을 제외하고는 생성 작업과 비슷합니다.

executeUpdate()를 실행한 후에 사용자는 스니펫에서 확인 페이지로 리디렉션됩니다.

레코드 삭제

행(이 예시에서는 블로그 게시물)을 삭제하려면 대상 테이블(이 예시에서는 content 테이블)에서 행을 삭제해야 합니다. 각 레코드는 ID(샘플 코드에서는 post_id 값)로 식별됩니다. 이 ID를 DELETE 쿼리에서 필터로 사용하세요.

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를 사용하여 이 가이드에 업로드된 이미지 크기를 조정하는 예에 따라 태스크 큐를 사용하여 비동기 작업을 수행하는 방법을 알아보세요.