Using Cloud SQL for MySQL

This page shows how to connect to a Cloud SQL for MySQL Second Generation instance from an App Engine application, and how to read and write to Cloud SQL. Cloud SQL is a SQL database that lives in Google's cloud.

To learn more about Cloud SQL, see the Cloud SQL documentation. For information on Cloud SQL pricing and limits, see the Cloud SQL Pricing page. App Engine applications are also subject to the App Engine quotas.

Before you begin

  1. Create or select a GCP project in the GCP Console and then ensure that project includes an App Engine application and billing is enabled:
    Go to App Engine

    The Dashboard opens if an App Engine application already exists in your project and billing is enabled. Otherwise, follow the prompts for choosing a region and enabling billing.

  2. Enable the Cloud SQL API.

    Enable the API

  3. To deploy a Java app to App Engine, you must first setup your environment, see Using Apache Maven and the App Engine Plugin for details.

Configuring the Cloud SQL instance

To create and configure a Cloud SQL instance:

  1. Create a Cloud SQL Second Generation instance.
  2. If you haven't already, set the password for the default user on your Cloud SQL instance:
    gcloud sql users set-password root --host=% --instance [INSTANCE_NAME] --password [PASSWORD]
  3. If you don't want to use the default user to connect, create a user.
  4. Record the connection name for the instance:
    gcloud sql instances describe [INSTANCE_NAME]

    For example:

    connectionName: project1:us-central1:instance1

    You can also find this value in the Instance details page of the Google Cloud Platform Console.

  5. In this example, create a database on your Cloud SQL instance named sqldemo.
    gcloud sql databases create sqldemo --instance=[INSTANCE_NAME]
    For more information on creating and managing databases, see the Cloud SQL documentation.

Setting connection strings and adding a library

Use the Cloud SQL JDBC Socket Factory socket library to connect to your Cloud SQL instance for local testing and in deployment.

  1. Update pom.xml with your connection name, user name, and password:

        INSTANCE_CONNECTION_NAME from Cloud Console > SQL > Instance Details > Properties
        or `gcloud sql instances describe <instance> | grep connectionName`
    <!-- ...

  2. Add the required resource filtering to pom.xml:


  3. Create a file called that contains the following line:


    This file relies on the resource filtering configured in pom.xml.

  4. Add a JDBC library and the Cloud SQL JDBC Socket Factory to your application by adding the dependency to pom.xml:

    <dependency>                        <!-- -->

Running the sample code

The following sample writes visit information to Cloud SQL and then reads and returns the last ten visits:
@WebServlet(name = "cloudsql", value = "")
public class CloudSqlServlet extends HttpServlet {
  Connection conn;

  public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException,
      ServletException {
    final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( visit_id INT NOT NULL "
        + "AUTO_INCREMENT, user_ip VARCHAR(46) NOT NULL, timestamp DATETIME NOT NULL, "
        + "PRIMARY KEY (visit_id) )";
    final String createVisitSql = "INSERT INTO visits (user_ip, timestamp) VALUES (?, ?)";
    final String selectSql = "SELECT user_ip, timestamp FROM visits ORDER BY timestamp DESC "
        + "LIMIT 10";

    String path = req.getRequestURI();
    if (path.startsWith("/favicon.ico")) {
      return; // ignore the request for favicon.ico

    PrintWriter out = resp.getWriter();

    // store only the first two octets of a users ip address
    String userIp = req.getRemoteAddr();
    InetAddress address = InetAddress.getByName(userIp);
    if (address instanceof Inet6Address) {
      // nest indexOf calls to find the second occurrence of a character in a string
      // an alternative is to use Apache Commons Lang: StringUtils.ordinalIndexOf()
      userIp = userIp.substring(0, userIp.indexOf(":", userIp.indexOf(":") + 1)) + ":*:*:*:*:*:*";
    } else if (address instanceof Inet4Address) {
      userIp = userIp.substring(0, userIp.indexOf(".", userIp.indexOf(".") + 1)) + ".*.*";

    Stopwatch stopwatch = Stopwatch.createStarted();
    try (PreparedStatement statementCreateVisit = conn.prepareStatement(createVisitSql)) {
      statementCreateVisit.setString(1, userIp);
      statementCreateVisit.setTimestamp(2, new Timestamp(new Date().getTime()));

      try (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) {
        out.print("Last 10 visits:\n");
        while ( {
          String savedIp = rs.getString("user_ip");
          String timeStamp = rs.getString("timestamp");
          out.print("Time: " + timeStamp + " Addr: " + savedIp + "\n");
        out.println("Elapsed: " + stopwatch.elapsed(TimeUnit.MILLISECONDS));
    } catch (SQLException e) {
      throw new ServletException("SQL error", e);

  public void init() throws ServletException {
    try {
      String url;

      Properties properties = new Properties();
      try {
        url = properties.getProperty("sqlUrl");
      } catch (IOException e) {
        log("no property", e);  // Servlet Init should never fail.

      log("connecting to: " + url);
      try {
        conn = DriverManager.getConnection(url);
      } catch (ClassNotFoundException e) {
        throw new ServletException("Error loading JDBC Driver", e);
      } catch (SQLException e) {
        throw new ServletException("Unable to connect to PostGre", e);

    } finally {
      // Nothing really to do here.

Testing and deploying

  1. To test your application locally:

    mvn clean jetty:run
  2. After local testing, deploy your app to App Engine:

    mvn clean appengine:deploy

  3. To launch your browser and view the app at http://[YOUR_PROJECT_ID], run the following command:

    gcloud app browse

Running Cloud SQL and App Engine in separate projects

If your App Engine application and Cloud SQL instance are in different Google Cloud Platform projects, you must use a service account to allow your App Engine application access to Cloud SQL.

This service account represents your App Engine application and is created by default when you create a Google Cloud Platform project.

  1. If your App Engine application is in the same project as your Cloud SQL instance, you can skip this section and go to Setting connection strings and adding a library. Otherwise, proceed to the next step.
  2. Identify the service account associated with your App Engine application. The default App Engine service account is named [PROJECT-ID]

    You can verify the App Engine service account on the IAM Permissions page. Ensure that you select the project for your App Engine application, not your Cloud SQL instance.

    Go to the IAM Permissions page

  3. Go to the IAM & Admin Projects page in the Google Cloud Platform Console.

    Go to the IAM & Admin Projects page

  4. Select the project that contains the Cloud SQL instance.
  5. Search for the service account name.
  6. If the service account is already there, and it has a role that includes the cloudsql.instances.connect permission, you can proceed to Setting connection strings and adding a library.

    The Cloud SQL Client, Cloud SQL Editor and Cloud SQL Admin roles all provide the necessary permission, as do the legacy Editor and Owner project roles.

  7. Otherwise, add the service account by clicking Add.
  8. In the Add members dialog, provide the name of the service account and select a role that include the cloudsql.instances.connect permission (any Cloud SQL predefined role other than Viewer will work).

    Alternatively, you can use the primitive Editor role by selecting Project > Editor, but the Editor role includes permissions across Google Cloud Platform.

    If you do not see these roles, your Google Cloud Platform user might not have the resourcemanager.projects.setIamPolicy permission. You can check your permissions by going to the IAM page in the Google Cloud Platform Console and searching for your user id.

  9. Click Add.

    You should now see the service account listed with the specified role.

หน้านี้มีประโยชน์ไหม โปรดแสดงความคิดเห็น