Microservices Architecture on Google App Engine

Getting Began: Cloud SQL

This information extends the code pattern utilized in Dealing with consumer submitted
information by storing
and retrieving information utilizing Google Cloud SQL.

Cloud SQL is one storage choice accessible with App Engine that may be
simply built-in into apps and retailer relational textual content information. Examine
Cloud SQL, Cloud Datastore, and Cloud Storage
and select the one which meets your app’s necessities.

This pattern builds upon a collection of guides
and reveals the best way to retailer, replace and delete weblog publish information in Cloud SQL.

Earlier than you start

Configure your growth atmosphere and create your App Engine venture.

Making a Cloud SQL occasion and connecting to the database

You will want to create a Cloud SQL occasion and arrange a connection to
it out of your App Engine app. For directions on connecting to
Cloud SQL, see Connecting to App Engine.

Creating tables

You have to create a
Connection
object within the servlet init() methodology to deal with the connection to the Cloud SQL
occasion:

Connection conn; // Cloud SQL connection

// Cloud SQL desk creation instructions
remaining 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) )";

remaining 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 {
  attempt {
    String url = System.getProperty("cloudsql");

    attempt {
      conn = DriverManager.getConnection(url);

      // Create the tables in order that the SELECT question does not throw an exception
      // if the consumer visits the web page earlier than any posts have been added

      conn.createStatement().executeUpdate(createContentTableSql); // create content material desk
      conn.createStatement().executeUpdate(createUserTableSql); // create consumer desk

      // Create a take a look at consumer
      conn.createStatement().executeUpdate(createTestUserSql);
    } catch (SQLException e) {
      throw new ServletException("Unable to connect to SQL server", e);
    }

  } lastly {
    // Nothing actually to do right here.
  }
}

The init() methodology units up a connection to Cloud SQL after which creates the
content material and consumer tables if they don’t exist. After the init() methodology, the
app is able to serve and retailer new information.

Within the snippet, the desk creation SQL statements are saved in String
variables, that are executed inside the servlet’s init() by the decision to
executeUpdate methodology. Discover this may not create these tables in the event that they already
exist.

The 2 tables created within the snippet are named posts and customers: posts
holds the specifics of every weblog publish, whereas customers comprises data on the
creator, as proven right here:

READ  Flask vs Django: Perceive Whether or not You Want a Hammer or a Toolbox | by SteelKiwi Inc.

Desk: posts

Area Sort
post_id INT (auto increment, main key)
author_id INT
timestamp DATETIME
title VARCHAR (256)
physique VARCHAR (1337)

Desk: customers

Area Sort
user_id INT (auto increment, main key)
user_fullname VARCHAR (64)

Retrieving preliminary information to point out in a type

A standard use case is to pre-populate a type with information saved within the database,
to be used in consumer picks. For instance:

Connection conn;

remaining String getUserId = "SELECT user_id, user_fullname FROM users";
Map customers = new HashMap();

@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {

  // Discover the consumer ID from the complete identify
  attempt (ResultSet rs = conn.prepareStatement(getUserId).executeQuery()) {
    whereas (rs.subsequent()) {
      customers.put(rs.getInt("user_id"), rs.getString("user_fullname"));
    }

    req.setAttribute("users", customers);
    req.getRequestDispatcher("/form.jsp").ahead(req, resp);

  } catch (SQLException e) {
    throw new ServletException("SQL error", e);
  }
}

Within the code snippet above, the servlet queries the Cloud SQL database to
retrieve a listing of consumer IDs and creator names. These are saved as (id, full
identify)
tuples in a hash map. The servlet then forwards the consumer and hash map to
/type.jsp, which processes the hash map of creator names as proven within the subsequent
part.

Supporting database interactions in a type

The next snippet makes use of JavaServer Pages (JSP)
to show to the consumer the preliminary information from the creator identify hash map handed in
from the servlet, and makes use of that information in a variety record. The shape additionally lets the
consumer create and replace present information.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>

Create a brand new weblog publish

Within the snippet above, the shape is populated when the web page masses with the hash
map of creator names handed from the servlet. The shape makes use of JavaServer Pages
Commonplace Tag Library (JSTL) when and in any other case operations gives
if..else logic and forEach loops by the hash map handed from the
servlet.

The JSP web page within the snippet above comprises a type for creating new weblog posts
and updating present posts. Discover that the shape can ship the info to handlers
at both /create or /replace relying on whether or not the consumer is creating or
updating a weblog publish.

For extra data on the best way to use types, see Dealing with POST
information.

Storing information

The next snippet reveals the best way to construct a brand new document from information equipped by the
consumer within the type, and retailer it within the database. The instance reveals a SQL INSERT
assertion constructed out from the info submitted within the weblog publish creation type
described within the earlier part:

// Submit creation question
remaining 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 would like and run it by jSoup
  Map blogContent =
      req.getParameterMap()
          .entrySet()
          .stream()
          .filter(a -> a.getKey().startsWith("blogContent_"))
          .gather(
              Collectors.toMap(
                  p -> p.getKey(), p -> Jsoup.clear(p.getValue()[0], Whitelist.fundamental())));

  // Construct the SQL command to insert the weblog publish into the database
  attempt (PreparedStatement statementCreatePost = conn.prepareStatement(createPostSql)) {
    // set the creator to the consumer ID from the consumer desk
    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.shut(); // shut the connection to the Cloud SQL server

    // Ship the consumer to the affirmation web page with personalised affirmation textual content
    String affirmation = "Post with title " + blogContent.get("blogContent_title") + " created.";

    req.setAttribute("confirmation", affirmation);
    req.getRequestDispatcher("/confirm.jsp").ahead(req, resp);

  } catch (SQLException e) {
    throw new ServletException("SQL error when creating post", e);
  }
}

The code snippet makes use of takes the consumer enter and runs it by
jSoup to sanitize it. Utilizing jSoup and PreparedStatement
mitigate the potential for XSS and SQL injection assaults.

The createPostSql variable comprises the INSERT question with ? as
placeholders for values that shall be assigned utilizing PreparedStatement.set()
methodology.

Be aware the order of the desk fields as these are referenced in
PreparedStatement set strategies. For instance, the author_id is a discipline of kind
INT, so setInt() have to be used to set the author_id.

Retrieving information

The next snippet reveals a servlet’s doGet() methodology that fetches the rows
from the desk of weblog posts and prints them out.

// Preformatted HTML
String headers =
    "

Welcome to the App Engine Blog

Add a new post

"; String blogPostDisplayFormat = "

%s

Posted at: %s by %s [update] | [delete]

%s

"; @Override public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // Retrieve weblog posts from Cloud SQL database and show them PrintWriter out = resp.getWriter(); out.println(headers); // Print HTML headers attempt (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) { Map> storedPosts = new HashMap<>(); whereas (rs.subsequent()) { Map blogPostContents = new HashMap<>(); // Retailer the particulars for a weblog 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")); // Retailer the publish in a map with key of the postId storedPosts.put(rs.getInt("posts.post_id"), blogPostContents); } // Iterate the map and show every document's contents on display storedPosts.forEach( (okay, v) -> { // Encode the ID right into a websafe string String encodedID = Base64.getUrlEncoder().encodeToString(String.valueOf(okay).getBytes()); // Construct 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 outcomes from the SELECT assertion is put right into a ResultSet, which is
iterated upon utilizing the ResultSet.get() methodology. Be aware the ResultSet.get()
methodology getString that correspond to the desk schema outlined earlier.

For this instance, every publish has an [Update] and a [Delete] hyperlink, which is
used to provoke publish updates and deletions respectively. To obfuscate the
publish’s ID, the identifier is encoded in Base64.

Updating information

The next snippet reveals the best way to replace an present document:

remaining 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 would like and run it by jSoup
  Map blogContent =
      req.getParameterMap()
          .entrySet()
          .stream()
          .filter(a -> a.getKey().startsWith("blogContent_"))
          .gather(
              Collectors.toMap(
                  p -> p.getKey(), p -> Jsoup.clear(p.getValue()[0], Whitelist.fundamental())));

  // Construct up the PreparedStatement
  attempt (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 replace question

    conn.shut();

    // Affirmation string
    remaining String affirmation = "Blog post " + blogContent.get("blogContent_id") + " has been updated";

    req.setAttribute("confirmation", affirmation);
    req.getRequestDispatcher("/confirm.jsp").ahead(req, resp);

  } catch (SQLException e) {
    throw new ServletException("SQL error", e);
  }
}

On this snippet, when the consumer clicks the [Update] hyperlink on a weblog publish, it
shows the JSP type used to create a brand new publish however now that is pre-populated
with the prevailing publish’s title and content material. The creator’s identify shouldn’t be displayed
within the pattern as a result of it will not change.

Updating a publish is just like making a publish, besides that the SQL UPDATE
question is used as a substitute of INSERT.

After working executeUpdate(), the consumer is redirected to a affirmation web page
within the snippet.

Deleting information

Deleting a row, a weblog publish on this instance, requires the removing of a row from
the goal desk, which is the content material desk within the instance. Every document is
recognized by its ID, which is the post_id worth within the pattern code. You employ
this ID because the filter within the DELETE question:

After working executeUpdate(), the consumer is redirected to a affirmation web page.

remaining String deleteSql = "DELETE FROM posts WHERE post_id = ?";

@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {

  Map userData = req.getParameterMap();

  String[] postId = userData.get("id");
  String decodedId = new String(Base64.getUrlDecoder().decode(postId[0])); // Decode the websafe ID


  attempt (PreparedStatement statementDeletePost = conn.prepareStatement(deleteSql)) {
    statementDeletePost.setString(1, postId[0]);
    statementDeletePost.executeUpdate();

    remaining String affirmation = "Post ID " + postId[0] + " has been deleted.";

    req.setAttribute("confirmation", affirmation);
    req.getRequestDispatcher("/confirm.jsp").ahead(req, resp);

  } catch (SQLException e) {
    throw new ServletException("SQL error", e);
  }

}

After decoding the publish’s ID, the snippet will delete a single publish from the
posts desk.

Deploying to App Engine

You’ll be able to deploy your app to App Engine utilizing Maven.

Go to the basis listing of your venture and kind:

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

Exchange PROJECT_ID with the ID of your Cloud venture. If
your pom.xml file already
specifies your
venture ID, you need not embrace the -Dapp.deploy.projectId property within the
command you run.

After Maven deploys your app, open an internet browser tab robotically
at your new app by typing:

gcloud app browse

What’s subsequent

Cloud SQL is helpful for storing text-based information; nevertheless, if you wish to retailer
wealthy media similar to photos you must contemplate
utilizing Cloud Storage.

Subsequent, study utilizing activity queues
to carry out asynchronous duties by following an instance of utilizing the Photos API to
resize the pictures uploaded on this information.

Leave a Reply

Your email address will not be published. Required fields are marked *