Java Access to SQL Azure via the JDBC Driver for SQL Server

I’ve written a couple of posts (here and here) about Java and the JDBC Driver for SQL Server with the promise of eventually writing about how to get a Java application running on the Windows Azure platform. In this post, I’ll deliver on that promise. Specifically, I’ll show you two things: 1) how to connect to a SQL Azure Database from a Java application running locally, and 2) how to connect to a SQL Azure database from an application running in Windows Azure. You should consider these as two ordered steps in moving an application from running locally against SQL Server to running in Windows Azure against SQL Azure. In both steps, connection to SQL Azure relies on the JDBC Driver for SQL Server and SQL Azure.

The instructions below assume that you already have a Windows Azure subscription. If you don’t already have one, you can create one here: http://www.microsoft.com/windowsazure/offers/. (You’ll need a Windows Live ID to sign up.) I chose the Free Trial Introductory Special, which allows me to get started for free as long as keep my usage limited. (This is a limited offer. For complete pricing details, see http://www.microsoft.com/windowsazure/pricing/.) After you purchase your subscription, you will have to activate it before you can begin using it (activation instructions will be provided in an email after signing up).

Connecting to SQL Azure from an application running locally

I’m going to assume you already have an application running locally and that it uses the JDBC Driver for SQL Server. If that isn’t the case, then you can start from scratch by following the steps in this post: Getting Started with the SQL Server JDBC Driver. Once you have an application running locally, then the process for running that application with a SQL Azure back-end requires two steps:

1. Migrate your database to SQL Azure. This only takes a couple of minutes (depending on the size of your database) with the SQL Azure Migration Wizard - follow the steps in the Creating a SQL Azure Server and Creating a SQL Azure Database sections of this post.

2. Change the database connection string in your application. Once you have moved your local database to SQL Azure, you only have to change the connection string in your application to use SQL Azure as your data store. In my case (using the Northwind database), this meant changing this…

String connectionUrl = "jdbc:sqlserver://serverName\\sqlexpress;"
                                    + "database=Northwind;"
                                    + "user=UserName;"
                                    + "password=Password";

…to this…

String connectionUrl = "jdbc:sqlserver://xxxxxxxxxx.database.windows.net;"
                                    + "database=Northwind;"
                                    + "user=UserName@xxxxxxxxxx;"
                                    + "password=Password";

(where xxxxxxxxxx is your SQL Azure server ID).

Connecting to SQL Azure from an application running in Windows Azure

The heading for this section might be a bit misleading. Once you have a locally running application that is using SQL Azure, then all you have to do is move your application to Windows Azure. The connecting part is easy (see above), but moving your Java application to Windows Azure takes a bit more work. Fortunately, Ben Lobaugh has written a great post that that shows how to use the Windows Azure Starter Kit for Java to get a Java application (a JSP application, actually) running in Windows Azure: Deploying a Java application to Windows Azure with Command-Line Ant. (If you are using Eclipse, see Ben’s related post: Deploying a Java application to Windows Azure with Eclipse.) I won’t repeat his work here, but I will call out the steps I took in modifying his instructions to deploy a simple JSP page that connects to SQL Azure.

1. Add the JDBC Driver for SQL Server to the Java archive. One step in Ben’s tutorial (see the Select the Java Runtime Environment section) requires that you create a .zip file from your local Java installation and add it to your Java/Azure application. Most likely, your local Java installation references the JDBC driver by setting the classpath environment variable. When you create a .zip file from your java installation, the JDBC driver will not be included and the classpath variable will not be set in the Azure environment. I found the easiest way around this was to simply add the sqljdbc4.jar file (probably located in  C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc_3.0\enu) to the \lib\ext directory of my local Java installation before creating the .zip file.

Note: You can put the JDBC driver in a separate directory, include it when you create the .zip folder, and set the classpath environment variable in the startup.bat script. But, I found the above approach to be easier.

2. Modify the JSP page. Instead of the code Ben suggests for the HelloWorld.jsp file (see the Prepare your Java Application section), use code from your locally running application. In my case, I just used the code from this post after changing the connection string and making a couple minor JSP-specific changes:

<%@ page language="java"
    contentType="text/html; charset = ISO-8859-1"
    import = "java.sql.*"
%>

<html>
<head>
<title>SQL Azure via JDBC</title>
</head>
<body>
<h1>Northwind Customers</h1>
<%
try{
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    String connectionUrl = "jdbc:sqlserver://xxxxxxxxxx.database.windows.net;"
                            + "database=Northwind;"
                            + "user=UserName@xxxxxxxxxx;"
                            + "password=Password";
    Connection con = DriverManager.getConnection(connectionUrl);
    out.print("Connected.<br/>");
    String SQL = "SELECT CustomerID, ContactName FROM Customers";
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(SQL);

    while (rs.next()) {
        out.print(rs.getString(1) + ": " + rs.getString(2) + "<br/>");
    }
}catch(Exception e){
        out.print("Error message: "+ e.getMessage());
}
%>
</body>
</html>

That’s it!. To summarize the steps…

1. Migrate your database to SQL Azure with the SQL Azure Migration Wizard.
2. Change the database connection in your locally running application.
3. Use the Windows Azure Starter Kit for Java to move your application to Windows Azure. (You’ll need to follow instructions in this post and instructions above.)

Thanks.

-Brian