Getting Started: Cloud SQL

This guide extends the code sample used in Handling user submitted data by storing and retrieving data using Google Cloud SQL.

Cloud SQL is one storage option available with App Engine that can be easily integrated into apps and store relational text data. Compare Cloud SQL, Cloud Datastore, and Cloud Storage and choose the one that meets your app's requirements.

This sample builds upon a series of guides and shows how to store, update and delete blog post data in Cloud SQL.

Before you begin

Configure your development environment and create your App Engine project.

Creating a Cloud SQL instance and connecting to the database

You will need to create a Cloud SQL instance and set up a connection to it from your App Engine app. For instructions on connecting to Cloud SQL, see Connecting to App Engine.

Creating tables

You must create a Connection object in the servlet init() method to handle the connection to the Cloud SQL instance:

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.
  }
}

The init() method sets up a connection to Cloud SQL and then creates the content and user tables if they do not exist. After the init() method, the app is ready to serve and store new data.

In the snippet, the table creation SQL statements are stored in String variables, which are executed within the servlet's init() through the call to executeUpdate method. Notice this won't create those tables if they already exist.

The two tables created in the snippet are named posts and users: posts holds the specifics of each blog post, while users contains information on the author, as shown here:

Table: posts

Field	Type
post_id	INT (auto increment, primary key)
author_id	INT
timestamp	DATETIME
title	VARCHAR (256)
body	VARCHAR (1337)

Table: users

Field	Type
user_id	INT (auto increment, primary key)
user_fullname	VARCHAR (64)

Retrieving initial data to show in a form

A common use case is to pre-populate a form with data stored in the database, for use in user selections. For example:

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

In the code snippet above, the servlet queries the Cloud SQL database to retrieve a list of user IDs and author names. These are stored as (id, full name) tuples in a hash map. The servlet then forwards the user and hash map to /form.jsp, which processes the hash map of author names as shown in the next section.

Supporting database interactions in a form

The following snippet uses JavaServer Pages (JSP) to display to the user the initial data from the author name hash map passed in from the servlet, and uses that data in a selection list. The form also lets the user create and update existing data.

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

In the snippet above, the form is populated when the page loads with the hash map of author names passed from the servlet. The form uses JavaServer Pages Standard Tag Library (JSTL) when and otherwise operations provides if..else logic and forEach loops through the hash map passed from the servlet.

The JSP page in the snippet above contains a form for creating new blog posts and updating existing posts. Notice that the form can send the data to handlers at either /create or /update depending on whether the user is creating or updating a blog post.

For more information on how to use forms, see Handling POST data.

Storing records

The following snippet shows how to build a new record from data supplied by the user in the form, and store it in the database. The example shows a SQL INSERT statement built out from the data submitted in the blog post creation form described in the previous section:

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

The code snippet uses takes the user input and runs it through jSoup to sanitize it. Using jSoup and PreparedStatement mitigate the possibility of XSS and SQL injection attacks.

The createPostSql variable contains the INSERT query with ? as placeholders for values that will be assigned using PreparedStatement.set() method.

Note the order of the table fields as these are referenced in PreparedStatement set methods. For example, the author_id is a field of type INT, so setInt() must be used to set the author_id.

Retrieving records

The following snippet shows a servlet's doGet() method that fetches the rows from the table of blog posts and prints them out.

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

The results from the SELECT statement is put into a ResultSet, which is iterated upon using the ResultSet.get() method. Note the ResultSet.get() method getString that correspond to the table schema defined earlier.

For this example, each post has an [Update] and a [Delete] link, which is used to initiate post updates and deletions respectively. To obfuscate the post's ID, the identifier is encoded in Base64.

Updating records

The following snippet shows how to update an existing record:

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

In this snippet, when the user clicks the [Update] link on a blog post, it displays the JSP form used to create a new post but now this is pre-populated with the existing post's title and content. The author's name is not displayed in the sample because it won't change.

Updating a post is similar to creating a post, except that the SQL UPDATE query is used instead of INSERT.

After running executeUpdate(), the user is redirected to a confirmation page in the snippet.

Deleting records

Deleting a row, a blog post in this example, requires the removal of a row from the target table, which is the content table in the example. Each record is identified by its ID, which is the post_id value in the sample code. You use this ID as the filter in the DELETE query:

After running executeUpdate(), the user is redirected to a confirmation page.

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

}

After decoding the post's ID, the snippet will delete a single post from the posts table.

Deploying to App Engine

You can deploy your app to App Engine using Maven.

Go to the root directory of your project and type:

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

Replace PROJECT_ID with the ID of your Google Cloud project. If your pom.xml file already specifies your project ID, you don't need to include the -Dapp.deploy.projectId property in the command you run.

After Maven deploys your app, open a web browser tab automatically at your new app by typing:

gcloud app browse

What's next

Cloud SQL is useful for storing text-based data; however, if you want to store rich media such as images you should consider using Cloud Storage.

Next, learn about using task queues to perform asynchronous tasks by following an example of using the Images API to resize the images uploaded in this guide.