CloudSQL for the busy Java Developer

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.

Advertisements

How to build a successful Java User Group (JUG)

JUG’s are an increasingly important part of the larger Java community (see recent JCP elections) and yet only a fraction of Java developers attend regular meetings (as we found out at JavaOne).

Before I start I probably should say that while I don’t have hard data to back this up, I’ve seen a large number of JUG’s be created in the past 2-3 years, more than 10 years after Java was first introduced, accounting for maybe as much as half of today’s active JUGs. In France alone, there is more than a dozen active JUGs that were created, all in the last 3-4 years. There doesn’t seem to be a shortage of interest for Java, quite the opposite.

I don’t run a JUG nor am I really active in one but I’ve been visiting many of them in the past few years (they tend to be the format that I like best), I’ve seen a good number of them take off, some crash, and I interact with several JUG leaders on a regular basis, so I thought I’d share the ingredients that I believe are key to a successful JUG.

First, you need to have a venue. It may sound obvious but the lack of a regular (hopefully free-of-charge) room for your meetings is often the reason for JUG activity going down or simply disappearing. Universities seems to be the best solution after company meeting rooms.

Second, you need sponsors. While this can cover for Pizza & Beer (or Wine & Cheese, you decide), maybe more importantly this should help you cover for your speakers expenses (travel + hotel). While those speakers employed by large companies should be able to expense their entire travel, you don’t want to miss out on the independent consultants. Another option is to have formal JUG members paying a yearly fee. This works well once you have a well established set of events, including maybe a yearly (international) conference.

Third, last, and not least – you need good content. This is content that addresses your JUG’s interests and it doesn’t have to be always about bleeding edge technology of the latest and greatest JVM language. Some folks take JUG’s as a training, others mostly as a get-together, and probably also a good chunk as a way to stay on top of what’s new in the Java ecosystem. You need to balance different topics but my recommendation would be to have a small set of folks decide on the agenda. Voting or large groups of folks deciding don’t usually work too well.

While all of the above probably applies for any technology user group, JUG’s are a bit special because they are loosely federated by mailing lists and interactions are facilitated by Sun initially and now by Oracle. If you are starting a JUG, you probably want to read about it on this all-in-one page, be listed on java-user-groups and start chatting with fellow JUG leaders, they are a great bunch and will most certainly have tips for you.

I should of course not forget to mention that you’ll need energy and passion to keep a JUG running. Lot’s of it!

JavaOne Russia – Moscow on April 12-13th 2011

The next JavaOne conference is scheduled for in little over than a month (April 12-13, 2011) in Moscow, Russia, planning is well on its way and I’m happy to report that I’ll be presenting a number of sessions there.


JavaOne Keynote speakers are none other than Steve Harris (Senior VP of Application Server Development) and Henrik Stahl (Senior Director of Product Management).

The preliminary schedule is here (expect a few minor changes). As you can see there is plenty of Java EE and GlassFish content.

Встречаемся на JavaOne !

Packaging libraries with EARs

Hong’s recent packaging response on the GlassFish Forum reminded me of other similar problems faced by GlassFish users.

When packaging applications in an EAR file, the library JARs need to be placed in the archive lib directory (jars at the archive root level are not added to the classpath and thus available from other EAR artifacts..

The library jars placed in the "/lib" directory of the EAR (the directory name can be overridden in application.xml) will be visible to all sub-modules (JARs, WARs, and RARs) with no further configuration. No need to add Class-Path entries in the EAR manifest.

In the sub-modules, you can use Class-Path entries to explicitly reference library JAR files you may need.

Of course you can also “install” those libraries in the GlassFish "/lib" directory but you may run into other problems since they would be visible and shared from all applications.

GlassFish also offers a asadmin deploy --libraries foo.jar option (something a good number of people end up using when looking for an equivalent to “shared libraries” offered by other containers).

Then of course, if you’re like most server-side Java developer you could probably put your application on a diet and remove a few of those JARs to use what’s already in the platform.