Connect Microsoft SQL Server to Java EE JPA and JAXB with MOXy technologies using Microsoft’s JDBC Driver 4.0.

A t-sql script to create the three database tables used in this post is available on DropBox. If you like DropBox, please use this link to sign up for a free 2 GB account. It will help me post more files to DropBox for future posts.

Recently, I read a very good five-part blog series, Creating a RESTful Web Service, by Blaise Doughan. The series of posts demonstrate the effective use of JPA, JAXB with MOXy (JPA Entities to XML), EJB, and JAX-RS. Although Blaise’s series was written in 2010, the information it has is still quite relevant and insightful. I was interested in working through the sample project the series is based on. However, I wanted to use Microsoft’s SQL Server instead of Oracle as the data source. In this post, I detail changes I made to the series’ example project to work with SQL Server, using Microsoft’s JDBC Driver SQL Server.

Blaise’s blog, Java XML & JSON Binding (Object-to-XML and object-to-JSON mapping using JAXB and EclipseLink MOXy), is located at blog.bdoughan.com. According to his blog, Blaise is the Team lead for the TopLink / EclipseLink JAXB & SDO implementations, and the Oracle representative on those specifications. His blog is overflowing with a wealth of information on a number of Java EE technologies, including Java Persistence Architecture (JPA), Java Architecture for XML Binding (JAXB), Enterprise JavaBeans (EJB), Java API for RESTful Web Services (JAX-RS), and Oracle’s GlassFish application server.

Microsoft SQL Server as Data Source

In my current position, I work regularly with Microsoft’s relational database, SQL Server 2008 R2. However, Blaise’s blog series example uses Oracle Database XE. Using the Java EE technology stack, but switching the data source to SQL Server was relatively easy using Microsoft JDBC Driver 4.0 for SQL Server. The 4.0 version of the driver was just released in March of 2012. It includes the ability to interface with Microsoft’s new SQL Server 2012, and is also backwards compatible to SQL Server 2005. Of course there are alternative third-party SQL Server drivers, including those from DataDirect, i-net software, and so forth. I chose to use Microsoft’s own JDBC driver.

Before connecting to SQL Server using Microsoft’s JDBC driver, you need to create a SQL Server database, mirroring the database schema in Blaise’s blog post. You can easily translate the Oracle, native PL/SQL TABLE CREATE scripts into T-SQL. Once you’ve set up the three database tables and related relationships, your database schema should resemble the database diagram displayed below.

Customer Service Database Diagram

The only change I made was to designate the ID primary key column in the CUSTOMER table as an auto-incrementing IDENTITY column. This allows inserting of new customers in the CUSTOMER table without having to manually manage unique IDs. You will note some extra annotations in the Customer entity class if you make the identity column change.

Configuring Microsoft’s JDBC Driver with NetBeans

Microsoft’s JDBC driver downloadable install package has two JAR class libraries, one supports JDBC 3.0 for use with JRE version 5, and another supports JDBC 4.0 for JRE 6. I used the later JAR file, sqljdbc4.jar, to build database connections based on my project’s designated JRE.

I’m currently using NetBeans IDE 7.1.1. To create a database connection in NetBeans, on the Services tab, right-click on Databases, and select New Connection… If this is your first time using the Microsoft driver, under Locate Driver, Driver drop down list, select New Driver… In the New JDBC Driver window, select one of the driver’s two JAR files (see explanation above). There is only one Driver Class, chosen by default. You can change the name of the driver as it appears in driver list, or leave NetBeans’ default descriptive connection naming convention format:

jdbc:sqlserver://[SERVER_NAME]\[INSTANCE_NAME];databaseName=CustomerService [[USER_NAME] on [SCHEMA_NAME]]

Adding a New JDBC Driver in NetBeans

After setting up the new JDBC driver, select Next to set up the details of the specific database instance and database you wish to connect to for your project. Following Blaise’s blog, connect to the Customer Service database created earlier. I chose to connect to the database by inputting my database server, instance, and database names. Alternately, you could connect using TCP/IP (assuming it’s enabled), by inputting your SQL Server’s network name or IP address, and the port you have configured for SQL Server connections. Your setup will be specific to your environment, but should look like the below.

Creating a New Database Connection in NetBeans

One limitation of Microsoft’s JDBC driver is that it requires you to select a specific database schema, as part of the last step in the connection creation process. Each specific database connection will only display database objects from the selected schema. In a production environment, where you may have multiple schemas, this can add complexity to managing connections.

Selecting a Default Database Schema

When finished, you will see your newly created Microsoft SQL Server driver listed under Drivers. You will also see your newly created database connection, using the Microsoft JDBC driver, listed under Databases. From this point on, you can follow Blaise’s series, substituting the Oracle database connection with the new SQL Server database connection in the Persistence Unit’s JDBC Connection drop down menu.

List of Database Connections in NetBeans

If you decide to move beyond the scope of Blaise’s series’ example project, you may want the ability to call the methods and properties of the SQL Server driver, directly in your code. To do so, add the driver’s JAR file to your project by right-clicking on the Libraries folder of your project on the Projects tab, and select Add JAR/Folder… Again, I added the same JAR file, sqljdbc4.jar, for use with JRE 6. Then, import the driver’s class library packages as necessary, such as com.microsoft.sqlserver.jdbc.SQLServerDataSource, used to build a SQL Server data source.

Configuring Microsoft’s JDBC Driver with GlassFish

To host the example web service in the blog series, you also need to install Microsoft’s JDBC driver on your application server; in the case of Blaise’s series, Oracle’s GlassFish Server. To install the driver on GlassFish 3.1.2, right-click on the GlassFish server instance in the Servers tab in NetBeans and select View Domain Admin Console. GlassFish’s administration console should appear in your web browser, assuming GlassFish is installed and running. You can follow the blog’s instructions to set up the JDBC Connection Pool and JDBC Resource, selecting MicrosoftSqlServer from the Database Driver Vendor drop down menu. If this option is not available, make sure you installed the driver’s JAR file in GlassFish’s lib folder, as explained in the first blog of Blaise’s series, and try restarting GlassFish in NetBeans. Be sure to close the Admin Console in the web browser first.

Additional Capabilities

Microsoft’s latest JDBC Driver 4.0 provides the ability to easily connect to SQL Server with Java. Included with the driver installation package, is excellent documentation and both Java and JavaScript code examples. Using the documentation and supplied examples, many of the more advanced capabilities of SQL Server are readily accessible to Java developers. These advanced features including application security, transactions, working with stored procedures, and working with advanced data-types such as XML. I strongly urge you to read more of Blaise’s blog posts to learn more about advanced Java EE topics, as well as Microsoft’s documentation to learn about JDBC with SQL Server.