Google Cloud SQL Integration with Google App Engine

One of App Engine’s most requested features has been a simple way to develop traditional database-driven applications. In response to the feedback, google announced the Google Cloud SQL. There are several advantages after using Google Cloud SQL over other database. Google Cloud SQL brings many benefits to the App Engine community:

• No maintenance or administration
• High reliability and availability
• Familiar MySQL database environment with JDBC support (for Java-based App Engine applications) and DB-API support (for Python-based App Engine applications).
• Comprehensive user interface for administering databases.
• Integrated with Google App Engine and other Google services
• Simple and powerful integration with Google App Engine.

Google Cloud SQL allows you to create, configure, and use MySQL databases that live in Google's cloud. It is a fully-managed service that maintains, manages, and administers your databases. It is primarily intended for programmatic use within applications. It has an interactive UI, which is helpful for learning about the product, getting started using it, investigating the schema, and submitting trial queries.

To setup Google Cloud Storage in your GAE application using JDBC, follow the below mentioned 3 steps:

·         Create a Google Cloud SQL Instance from.

·         Configure the Google Cloud SQL Instance from

·         Connect the Google Cloud SQL Instance

Cloud SQL Data Creation and Ingestion

Google Cloud SQL lets you import existing databases or create them from scratch. You can perform the usual SQL commands to create and drop database tables, and to create, update, and delete rows and data as follows:

·         interactively from the online SQL prompt

·         from the command line with the google_sql tool

·         from App Engine applications

·         programmatically from other applications using JDBC

·         from Apps Script scripts

·         using third-party tools, such as the Squirrel SQL Client

To create and configure an SQL instance you can follow instructions from here and here.

Connect the Google Cloud Instance

To connect to the Google cloud instance, follow the below mentioned guidelines:

·         Connect and Post to Your Database

/**

    * Method to retrieve the database connection

    *

    * @return

    */    

   public Connection getConnection() {

         String url = null;

         Connection connection = null;

         try {

               if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Production) {

                     logger.info("Trying to establish connection");

                     Class.forName("com.google.cloud.sql.jdbc.Driver");

                     url = "jdbc:google:rdbms://reservosdevbatch:rvdevbatchdb/email_campaign?user=root";

               } else {

                     // Local MySQL instance to use during development.

                     Class.forName("com.mysql.jdbc.Driver");

                     url = "jdbc:mysql://localhost:3306/email_campaign?user=root&password=root";

               }

               connection = (Connection) DriverManager.getConnection(url);

         } catch (Exception e) {

               logger.info("Error while connecting");

               logger.info(e.getLocalizedMessage());

         }

         return connection;

   }

·         Add the MySQL Connector Jar

Manually put mysql-connector-java-5.1.22-bin.jar in your app engine sdk folder: file:///opt/appengine-java-sdk-1.6.0/lib/impl/

·         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>

Happy Clouding!!

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