Getting Started: Cloud SQL
Stay organized with collections
Save and categorize content based on your preferences.
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:
Connectionconn;// Cloud SQL connection
// Cloud SQL table creation commands
finalStringcreateContentTableSql=
"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) )";
finalStringcreateUserTableSql=
"CREATE TABLE IF NOT EXISTS users ( user_id INT NOT NULL "
+"AUTO_INCREMENT, user_fullname VARCHAR(64) NOT NULL, "
+"PRIMARY KEY (user_id) )";
@Override
publicvoidinit()throwsServletException{
try{
Stringurl=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(SQLExceptione){
thrownewServletException("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:
Connectionconn;
finalStringgetUserId="SELECT user_id, user_fullname FROM users";
Map<Integer,String>users=newHashMap<Integer,String>();
@Override
publicvoiddoGet(HttpServletRequestreq,HttpServletResponseresp)
throwsServletException,IOException{
// Find the user ID from the full name
try(ResultSetrs=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(SQLExceptione){
thrownewServletException("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.
<%@tagliburi="http://java.sun.com/jsp/jstl/core"prefix="c"%>
<%@tagliburi="http://java.sun.com/jsp/jstl/functions"prefix="fn"%>
<div>
<c:choose>
<c:whentest="${id == null}">
<h2>Createanewblogpost</h2>
<formmethod="POST"action="/create">
</c:when>
<c:otherwise>
<h2><c:outvalue="${pagetitle}"/></h2>
<formmethod="POST"action="/update">
<inputtype="hidden"name="blogContent_id"value="${id}">
</c:otherwise>
</c:choose>
<div>
<labelfor="title">Title</label>
<inputtype="text"name="blogContent_title"id="title"size="40"value="${title}"/>
</div>
<div>
<labelfor="author">Author</label>
<selectname="blogContent_id">
<c:forEachitems="${users}"var="user">
<optionvalue="${user.key}">${user.value}</option>
</c:forEach>
</select>
<inputtype="text"name="blogContent_author"id="author"size="40"value="${author}"/>
</div>
<div>
<labelfor="description">Postcontent</label>
<textareaname="blogContent_description"id="description"rows="10"cols="50">${body}</textarea>
</div>
<buttontype="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
finalStringcreatePostSql=
"INSERT INTO posts (author_id, timestamp, title, body) VALUES (?, ?, ?, ?)";
@Override
publicvoiddoPost(HttpServletRequestreq,HttpServletResponseresp)
throwsServletException,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(PreparedStatementstatementCreatePost=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,newTimestamp(newDate().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
Stringconfirmation="Post with title "+blogContent.get("blogContent_title")+" created.";
req.setAttribute("confirmation",confirmation);
req.getRequestDispatcher("/confirm.jsp").forward(req,resp);
}catch(SQLExceptione){
thrownewServletException("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
Stringheaders=
"<!DOCTYPE html><meta charset=\"utf-8\"><h1>Welcome to the App Engine Blog</h1><h3><a href=\"blogpost\">Add a new post</a></h3>";
StringblogPostDisplayFormat=
"<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
publicvoiddoGet(HttpServletRequestreq,HttpServletResponseresp)
throwsServletException,IOException{
// Retrieve blog posts from Cloud SQL database and display them
PrintWriterout=resp.getWriter();
out.println(headers);// Print HTML headers
try(ResultSetrs=conn.prepareStatement(selectSql).executeQuery()){
Map<Integer,Map<String,String>>storedPosts=newHashMap<>();
while(rs.next()){
Map<String,String>blogPostContents=newHashMap<>();
// 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
StringencodedID=Base64.getUrlEncoder().encodeToString(String.valueOf(k).getBytes());
// Build up string with values from Cloud SQL
StringrecordOutput=
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(SQLExceptione){
thrownewServletException("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:
finalStringupdateSql="UPDATE posts SET title = ?, body = ? WHERE post_id = ?";
@Override
publicvoiddoPost(HttpServletRequestreq,HttpServletResponseresp)
throwsServletException,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(PreparedStatementstatementUpdatePost=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
finalStringconfirmation="Blog post "+blogContent.get("blogContent_id")+" has been updated";
req.setAttribute("confirmation",confirmation);
req.getRequestDispatcher("/confirm.jsp").forward(req,resp);
}catch(SQLExceptione){
thrownewServletException("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.
finalStringdeleteSql="DELETE FROM posts WHERE post_id = ?";
@Override
publicvoiddoGet(HttpServletRequestreq,HttpServletResponseresp)
throwsServletException,IOException{
Map<String,String[]>userData=req.getParameterMap();
String[]postId=userData.get("id");
StringdecodedId=newString(Base64.getUrlDecoder().decode(postId[0]));// Decode the websafe ID
try(PreparedStatementstatementDeletePost=conn.prepareStatement(deleteSql)){
statementDeletePost.setString(1,postId[0]);
statementDeletePost.executeUpdate();
finalStringconfirmation="Post ID "+postId[0]+" has been deleted.";
req.setAttribute("confirmation",confirmation);
req.getRequestDispatcher("/confirm.jsp").forward(req,resp);
}catch(SQLExceptione){
thrownewServletException("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:
gcloudappbrowse
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.