CloudSQL, Google’s fully-managed and highly-available MySQL-based relational database service, can be accessed directly by Java IDE’s or used as a target for on-premise running Java application servers, and of course it can be seamlessly used from AppEngine Java applications. Here’s how.

Pre-requisites

This paper assumes you have a CloudSQL database instance configured and (ideally) populated. You should have authorized your local machine using OAuth and the command-line tool and have the CloudSQL JDBC driver handy (it’s in the AppEngine SDK in lib/impl). If you need help on any of this, consider reading this Getting Started paper.

Here are the values used here :

Cloud SQL instance name : scott-tiger:scott

Database Name : jpetstore

JDBC Driver Class Name : com.google.cloud.sql.Driver

JDBC URL : jdbc:google:rdbms://scott-tiger:scott/jpetstore

By default the CloudSQL instance can be accessed with user root and an empty password.

This paper uses NetBeans (7.x) as the IDE and GlassFish (ships with NetBeans) as the local Java Application Server but everything should be easily adaptable for other tools such as Eclipse and other runtimes (tomcat, JBoss, etc).

NetBeans & CloudSQL

The NetBeans IDE offers a JDBC database explorer feature which you can use to access your CloudSQL database instance. In the NetBeans Services tab, chose Databases > Drivers and create a new driver configuration by pointing to the google_sql.jar archive and using com.google.cloud.sql.Driver as the JDBC driver name (should be auto-detected). Right-click this newly created JDBC driver and select “Connect With…” to create a new connection. Provide the username, the password and the full JDBC URL (jdbc:google:rdbms://scott-tiger:scott/jpetstore in my case) and test the connection.

You should now be able to navigate the database schema, view table content, manipulate data, and execute any SQL statement.

WebApp Project

We’ll now create a web application using JPA entities manipulating data from the Cloud SQL instance discussed above. We’ll deploy this application first to GlassFish, then to App Engine.

Within NetBeans, create a (Maven) Web Application project with GlassFish as the default target. Right-click on the project to select the “New Entity Classes from Database” wizard. Create a new data source using the JDBC connection defined in the previous step. Select the tables you want to create JPA entities for and do not check the “Create Persistence Unit” option (we’ll get back to this later). This generates standard JPA 2.0 @Entity-annotated classes for every table selected from CloudSQL.

Here’s a proper persistence.xml that will work with CloudSQL. Notice how this JPA persistence unit uses RESOURCE_LOCAL and not a JTA data source :

Deploying this simple application to the GlassFish application server shouldn’t require any other changes. Obviously with this architecture, the performance is not ideal given the server is not exactly close to the data. Nevertheless, this demonstrates the standalone capabilities of CloudSQL

Ship it all to the cloud!

A better approach is to probably to use CloudSQL from a Google AppEngine-hosted application where all sorts of optimisations will quick in. To do so, only limited changes to the standard application described above are required.

The first simple change is to add the AppEngine-specific deployment descriptor appengine-web.xml :

Finally, you’ll need to bundle JPA / EclipseLink and BeanValidation / Hibernate Validator by making them runtime-scope dependencies. The Servlet and AppEngine SDK API artifacts should use the default scoping. Your mileage may vary when it comes to the implementation versions. Here is my complete set of Maven dependencies :