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. Creating a 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.

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

Creating a Cloud SQL Instance

A CloudSQL instance is equivalent to a server. A single CloudSQL instance can contain multiple databases. Follow these steps to create a Google Cloud SQL instance:

1. Sign into the Google Cloud Console.
2. Create a new Cloud Console project, or open an existing project.
3. From within a Cloud Console project, select Cloud SQL to open the CloudSQL control panel for that project.
4. Click New Instance to create a new CloudSQL instance in your project, and configure your size, billing and replication options.
   • More information on CloudSQL billing options and instance sizes
   • More information on CloudSQL replication options
   You'll notice that the App Engine application associated with your current project is already authorized to access this new instance. For more information on app authorization see the Access Control topic in the Cloud SQL docs.

That's it! You can now connect to your Google Cloud SQL instance from within your app, or any of these other methods.

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 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 the Google App Engine administration console under the Applications column.

Note: An AppEngine application must be in the same region (either EU or US) as a Google Cloud SQL instance to be authorized to access that Google Cloud SQL instance.

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.
   Note: In order to improve performance, the Google Cloud SQL instance will be kept as close as possible to the first AppEngine application on the list, so this should be the application whose performance is most important.
   
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 titled Authorized 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. Inside the guestbook database create a table called entries with columns for the guest name, the message content, and a random ID, using the following statement:

   CREATE TABLE guestbook.entries (
     entryID INT NOT NULL AUTO_INCREMENT,
     guestName VARCHAR(255),
     content VARCHAR(255),
     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:

The easiest way to create skeleton for a Java App Engine application is to use the Google Plugin for Eclipse. The following steps assumes an application that was created following the Creating a New Web Application instructions.

1. Connect and Post to Your Database
2. Create Your Webform
3. Map Your Servlet
4. Enable MySQL Connector/J

Connect and post to your database

Replace doGet() in GuestbookServlet with the following code, replacing your-instance-name with your Google Cloud SQL instance name and your-project-id with the literal project ID. This code performs the following actions:

• Initiates the connection by calling getConnection()
• Collects the contents from a web form and posting them to the server
• Redirects the user to a file called guestbook.jsp (we will create it in a later step)

public class GuestbookServlet extends HttpServlet {
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    String url = null;
    try {
      if (SystemProperty.environment.value() ==
          SystemProperty.Environment.Value.Production) {
        // Load the class that provides the new "jdbc:google:mysql://" prefix.
        Class.forName("com.mysql.jdbc.GoogleDriver");
        url = "jdbc:google:mysql://your-project-id:your-instance-name/guestbook?user=root";
      } else {
        // Local MySQL instance to use during development.
        Class.forName("com.mysql.jdbc.Driver");
        url = "jdbc:mysql://127.0.0.1:3306/guestbook?user=root";
      }
    } catch (Exception e) {
      e.printStackTrace();
      return;
    }

    PrintWriter out = resp.getWriter();
    try {
      Connection conn = DriverManager.getConnection(url);
      try {
        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 = conn.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>");
          }
        }
      } finally {
        conn.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    resp.setHeader("Refresh", "3; url=/guestbook.jsp");
  }
}

Although the above example connects to the Google Cloud SQL instance as the root user without any password, you can also connect to the instance indicating one:

Connection conn = DriverManager.getConnection(
    "jdbc:google:mysql://your-project-id:your-instance-name/database",
    "user", "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.utils.SystemProperty" %>

<html>
  <body>

<%
String url = null;
if (SystemProperty.environment.value() ==
    SystemProperty.Environment.Value.Production) {
  // Load the class that provides the new "jdbc:google:mysql://" prefix.
  Class.forName("com.mysql.jdbc.GoogleDriver");
  url = "jdbc:google:mysql://your-project-id:your-instance-name/guestbook?user=root";
} else {
  // Local MySQL instance to use during development.
  Class.forName("com.mysql.jdbc.Driver");
  url = "jdbc:mysql://127.0.0.1:3306/guestbook?user=root";
}

Connection conn = DriverManager.getConnection(url);
ResultSet rs = conn.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>
<%
}
conn.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>

Enable MySQL Connector/J

The MySQL Connector/J is available in Google App Engine but it's not included in an app unless the app explicitly asks for it. To do that, add a <use-google-connector-j> element to the app's appengine-web.xml file as shown in the following example:

<?xml version="1.0" encoding="utf-8"?>
<appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
  ...
  <use-google-connector-j>true</use-google-connector-j>
</appengine-web-app>

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

Using a local MySQL instance during development

The above example shows how to connect to a Cloud SQL instance when the code runs in App Engine and how to connect to a local MySQL server when the code runs in the Java Development Server. We encourage this pattern to minimize confusion and also maximize flexibility.

Size and access limits

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

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.

App Engine-specific quotas and access limits are discussed on the Quotas page.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.