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.
