Using Google Cloud SQL with App Engine Java SDK

This document describes how to use Google Cloud SQL instances with the App Engine Java SDK.

1. Create a Google Cloud SQL Instance
2. Build a Starter Application and Database
3. Connect to Your Database
4. Using a Local MySQL Instance During Development
5. Size and Access Limits

To learn more about Google Cloud SQL, see the Google Cloud SQL documentation.

Support for Google Cloud SQL in App Engine is experimental, innovative, and rapidly improving. Because it is such a new feature, App Engine's support for Cloud SQL may change over time based on product needs and feedback from users. We will inform the community when it is no longer experimental.

If you haven't already created a Google Cloud SQL instance, the first thing you need to do is create one.

Create a Google Cloud SQL instance

1. Visit the Google APIs Console.
2. Select the project for which you want to enable Google Cloud SQL in the Project dropdown, or create a new project.
3. Click Services from the left-hand menu. Find Google Cloud SQL and turn on the service. You will have to enable billing for your project before creating any instances.
4. Click Google Cloud SQL from the left-hand menu.
5. Click New instance
6. Enter a name for the instance consisting only of lowercase letters and/or numbers without any spaces. Note: You cannot re-use an instance name for up to two months after deleting that instance. This name will be automatically combined with your project name, and any domain-specific names as shown here:
   • Non-domain: my_project:instance1
   • Domain-specific: my_domain:my_project:instance1
7. Choose a size for your instance, and a billing plan.
8. If you have App Engine applications that will use your instance, add them in the Authorized applications section. You can add or remove applications from this list at any time.
9. Click Create.

That's it! You have created your Google Cloud SQL instance.

MySQL Case Sensitivity

When you are creating or using databases and tables, keep in mind that all identifiers in Google Cloud SQL are case-sensitive. This means that all tables and databases are stored with the same name and case that was specified at creation time. When you try to access your databases and tables, make sure that you are using the exact database or table name.

For example, if you create a database named PersonsDatabase, you will not be able to reference the database using any other variation of that name, such as personsDatabase or personsdatabase. For more information about identifier case sensitivity, see the MySQL documentation.

Build a Starter Application and Database

The easiest way to build an App Engine application that accesses Google Cloud SQL is to create a starter application then modify it. This section leads you through the steps of building an application that displays a web form that lets users read and write entries to a guestbook database. The sample application demonstrates how to read and write to a Google Cloud SQL instance.

Step 1: Create Your App Engine Sample Application

Follow the instructions for the Creating a Project chapter of the App Engine Java Getting Started guide to create a simple App Engine application.

Step 2: Grant Your App Engine Application Access to the Google Cloud SQL Instance

You can grant individual Google App Engine applications access to a Google Cloud SQL instance. One application can be granted access to multiple instances, and multiple applications can be granted access to a particular instance. To grant access to a Google App Engine application, you need its application ID which can be found at theGoogle App Engine administration console under the Applications column.

To grant an App Engine application access to a Google Cloud SQL instance:

1. From the Google Cloud SQL pane of the APIs Console, find the instance that you want to grant access to and click the name of your instance.
2. Click Google Cloud SQL in the left hand navigation to display the Google Cloud SQL overview, then click the name of the relevant instance.
3. Click the Instance settings button on the instance's dashboard.
4. In the Instance settings window that appears, enter your Google App Engine application ID under the Authorized applications section. You can grant access to multiple applications, by entering them one at a time.
5. Click Update instance to apply your changes.

After you have added authorized applications to your Google Cloud SQL instance, you can view a list of these applications in the instance's Dashboard, in the section titledAuthorized Applications.

Step 3: Create Your Database and Table

Using either the SQL prompt or the command line tool:

1. Create a new database called guestbook using the following SQL statement:

   CREATE DATABASE guestbook;

2. Select your new database:
   • If you're using the command line tool, select the new database using the following statement:

     USE guestbook;

   • If you're using the SQL prompt, select the new database from the drop down menu.
3. Create a table called entries with columns for the guest name, the message content, and a random ID, using the following statement:

   CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255), entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID)

After you have set up a bare-bones application, you can modify it and deploy it.

Connect to Your Database

To connect your application to a Google Cloud SQL database:

1. Register the JDBC driver
2. Connect and Post to Your Database
3. Create Your Webform
4. Map Your Servlet

Developing with Eclipse

You can use the Google Plugin for Eclipse to configure your Google Cloud SQL instances, and create and deploy App Engine applications that can use Google Cloud SQL instances.

Register the JDBC Driver

Before you can write any Java application with Google Cloud SQL, you need to provide the correct driver to connect to the service. The Google Plugin for Eclipse provides the correct JDBC driver for this purpose. To use the driver, add import com.google.appengine.api.rdbms.AppEngineDriver to your class. For the sample application, copy and paste the following to override the imports in your GuestbookServlet. Note the bolded line where the App Engine driver is imported:

import com.google.appengine.api.rdbms.AppEngineDriver;
import java.io.IOException;
import java.sql.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

Connect and Post to Your Database

Override doGet() in GuestbookServlet with the following code, replacing instance_name with your Google Cloud SQL instance name. This code is performing the following actions:

• Initiating the connection by calling getConnection(), passing in your instance name
• Collecting the contents from a web form and posting them to the server
• Redirecting the user to a file called guestbook.jsp (which we will create later in this document)

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
  throws IOException {
   
  PrintWriter out = resp.getWriter();
  Connection c = null;
    try {
      DriverManager.registerDriver(new AppEngineDriver());
      c = DriverManager.getConnection("jdbc:google:rdbms://instance_name/guestbook");
      String fname = req.getParameter("fname");
      String content = req.getParameter("content");
      if (fname == "" || content == "") {
        out.println("<html><head></head><body>You are missing either a message or a name! Try again! Redirecting in 3 seconds...</body></html>");
      } else {
      String statement ="INSERT INTO entries (guestName, content) VALUES( ? , ? )";
      PreparedStatement stmt = c.prepareStatement(statement);
      stmt.setString(1, fname);
      stmt.setString(2, content);
      int success = 2;
      success = stmt.executeUpdate();
      if(success == 1) {
        out.println("<html><head></head><body>Success! Redirecting in 3 seconds...</body></html>");
      } else if (success == 0) {
        out.println("<html><head></head><body>Failure! Please try again! Redirecting in 3 seconds...</body></html>");
      }
     }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (c != null) 
          try {
            c.close();
            } catch (SQLException ignore) {
         }
      } resp.setHeader("Refresh","3; url=/guestbook.jsp");
  }

Although the above example connects to the Google Cloud SQL instance as the root user, you can also connect to the instance as a specific database user:

Connection c = DriverManager.getConnection("jdbc:google:rdbms://instance_name/database", "user", "password");

database

[Optional] The database to connect to.

user

[Optional] The database user to connect as.

password

[Optional] The user's password.

For information about creating MySQL users, see Adding Users in the MySQL documentation.

Create Your Webform

Next, we'll create the front-facing part of the sample application, which list the entries of your entries table and provides a simple form to post new entries.

In your war/ directory, create a new file called guestbook.jsp with the following code, replacing instance_name with your instance name:

<%@ page contentType="text/html;charset=UTF-8" language="java" %><%@ page import="java.util.List" %><%@ page import="java.sql.*" %><%@ page import="com.google.appengine.api.rdbms.AppEngineDriver" %>
<html>
  <body>
<%
Connection c = null;
c = DriverManager.getConnection("jdbc:google:rdbms://instance_name/guestbook");
ResultSet rs = c.createStatement().executeQuery("SELECT guestName, content, entryID FROM entries"); %>
<table style="border: 1px solid black">
<tbody>
<tr>
<th width="35%" style="background-color: #CCFFCC; margin: 5px">Name</th>
<th style="background-color: #CCFFCC; margin: 5px">Message</th>
<th style="background-color: #CCFFCC; margin: 5px">ID</th>
</tr> <%
while (rs.next()){
    String guestName = rs.getString("guestName");
    String content = rs.getString("content");
    int id = rs.getInt("entryID"); %>
<tr>
<td><%= guestName %></td>
<td><%= content %></td>
<td><%= id %></td>
</tr>
<% }
c.close(); %>
</tbody>
</table>
<br />
No more messages!<p><strong>Sign the guestbook!</strong></p>
<form action="/sign" method="post">
    <div>First Name: <input type="text" name="fname"></input></div>
    <div>Message:
    <br /><textarea name="content" rows="3" cols="60"></textarea>
    </div>
    <div><input type="submit" value="Post Greeting" /></div>
    <input type="hidden" name="guestbookName" />
  </form>
  </body>
</html>

Map Your Servlet

Finally, override your web.xml with the following code to map your servlet correctly:

<?xml version="1.0" encoding="utf-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
   <servlet>
        <servlet-name>sign</servlet-name>
        <servlet-class>guestbook.GuestbookServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>sign</servlet-name>
        <url-pattern>/sign</url-pattern>
    </servlet-mapping>
    <welcome-file-list>
        <welcome-file>guestbook.jsp</welcome-file>
    </welcome-file-list>
</web-app>

That's it! Now you can deploy your application and try it out!

Using a Local MySQL Instance During Development

The Java Development Server in the Google App Engine SDK can use a locally-installed MySQL server instance to closely mirror the Google Cloud SQL environment during development.

The Java Development Server's JDBC driver proxies JDBC calls to a local database server. You do not need to change your JDBC driver class or connection URL in your code for your application to run in both the Java Development Server and a production environment (e.g. when you deploy your application to Google App Engine.)

The following instructions explain how to run your application locally, using the username and password properties. You can also include other properties that can make your local MySQL instance behave more like a Google Cloud SQL instance. These properties can be added to the rdbms.url argument:

useInformationSchema=true
useUnicode=true
characterEncoding=UTF8
useServerPrepStmts=true

The following instructions describe how to use Java Development server with Eclipse and without Eclipse:

1. With Eclipse
2. Without Eclipse

With Eclipse

See the Google Plugin for Eclipse documentation for instructions how to develop Google Cloud SQL in Java using Eclipse.

Without Eclipse

Add the following parameters to your call to dev_appserver:

--jvm_flag=-Drdbms.server=local \
--jvm_flag=-Drdbms.driver=com.mysql.jdbc.Driver \
--jvm_flag=-Drdbms.url=jdbc:mysql://localhost:3306/yourdatabase?user=username&password=password

From the command line:

• On Mac OS X or Linux:

  appengine-java-sdk/bin/dev_appserver.sh \
   --jvm_flag=-Drdbms.server=local \
   --jvm_flag=-Drdbms.driver=com.mysql.jdbc.Driver \
   --jvm_flag=-Drdbms.url=jdbc:mysql://localhost:3306/yourdatabase?user=username&password=password \
   [options] war-location

• On Windows:

  appengine-java-sdk\bin\dev_appserver.cmd ^
   --jvm_flag=-Drdbms.server=local ^
   --jvm_flag=-Drdbms.driver=com.mysql.jdbc.Driver ^
   --jvm_flag=-Drdbms.url=jdbc:mysql://localhost:3306/yourdatabase?user=username&password=password ^
   [options] war-location

To stop the server, press Control-C.

Size and Access Limits

The following size and QPS limits apply to Google Cloud SQL:

Resource	Limit
Maximum Request Size	16 MB
Maximum Response Size	16 MB

Google App Engine Limits

Google App Engine applications are also subject to additional Google App Engine quotas and limits. Requests from Google App Engine applications to Google Cloud SQL are subject to the following time limits:

• All database requests must finish within the HTTP request timer, around 60 seconds.
• Offline requests like cron tasks have a time limit of 10 minutes.
• Backend requests to Google Cloud SQL have a time limit of 10 minutes.