CloudSQL for the busy Java Developer

July 12, 2012 § 1 Comment

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 :

  1. Cloud SQL instance name : scott-tiger:scott
  2. Database Name : jpetstore
  3. JDBC Driver Class Name : com.google.cloud.sql.Driver
  4. 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 :

<persistence version="2.0">
  <persistence-unit name="CloudSQLPU" transaction-type="RESOURCE_LOCAL">
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:google:rdbms://scott-tiger:scott/jpetstore"/>
      <property name="javax.persistence.jdbc.user" value="user"/>
      <property name="javax.persistence.jdbc.password" value="pw"/>
      <property name="javax.persistence.jdbc.driver" value="com.google.cloud.sql.Driver"/>
    </properties>
  </persistence-unit>
</persistence>

Once this is setup, you can get a hold of this persistence unit in the servlet created by default the typical way you would in an servlet :

EntityManagerFactory emf = 
        Persistence.createEntityManagerFactory("CloudSQLPU");
EntityManager em = emf.createEntityManager();

A simple use to exercise the data could be to list all the names stored in the Category table (using its JPA entity representation) :

CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
cq.select(cq.from(Category.class));
List<Category> categories = em.createQuery(cq).getResultList();

for (Category category : categories) {
    out.println(category.getName() + "<br/>");
}

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 :

<?xml version="1.0" encoding="UTF-8"?>
<appengine-web-app xmlns="http://appengine.google.com/ns/1.0"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <application>MyCloudSQLApp</application>
    <version>1</version>
    <threadsafe>true</threadsafe>
</appengine-web-app>

You’ll also need to change the name of the JDBC driver in persistence.xml (a more elegant solution could be to use Maven profiles to keep the project fully portable) :

- <property name="javax.persistence.jdbc.driver"
-          value="com.google.cloud.sql.Driver"/>
+ <property name="javax.persistence.jdbc.driver"
+         value="com.google.appengine.api.rdbms.AppEngineDriver"/>;

The JDBC URL remains the same.

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 :

<dependencies>
        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>eclipselink</artifactId>
            <version>2.3.2</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>javax.persistence</artifactId>
            <version>2.0.3</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>javax.validation</groupId>
            <artifactId>validation-api</artifactId>
            <version>1.0.0.GA</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>4.2.0.Final</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
        </dependency>
        <dependency>
            <groupId>com.google.appengine</groupId>
            <artifactId>appengine-api-1.0-sdk</artifactId>
            <version>1.7.0</version>
        </dependency>        
    </dependencies>

Make sure the AppEngine application is authorized to access the CloudSQL instance (use the API Console for that). Once this is all done, simply deploy the application to AppEngine :

$ appcfg.sh update target/MyCloudSQLApp-1.0.0-SNAPSHOT

You’ll find the CloudSQL developer documentation here.

About these ads

Tagged: , , , , , ,

§ One Response to CloudSQL for the busy Java Developer

What’s this?

You are currently reading CloudSQL for the busy Java Developer at Bistro! 2.0.

meta

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: