Posts Tagged NetBeans

Returning JSONP from Java EE RESTful Web Services Using jQuery, Jersey, and GlassFish – Part 1 of 2

Create a Jersey-specific Java EE RESTful web service and an HTML-based client to call the service and display JSONP. Test and deploy the service and the client to different remote instances of GlassFish.

Background

According to Wikipedia, JSONP (JSON with Padding) is a complement to the base JSON (JavaScript Object Notation) data format. It provides a method to request data from a server in a different domain, something prohibited by typical web browsers because of the same origin policy.

Jersey is the open source, production quality, JAX-RS (JSR 311) Reference Implementation for building RESTful Web services on the Java platform according to jersey.java.net. Jersey is a core component of GlassFish.

What do these two things have in common? One of the key features of Jersey is its ability to return JSONP.  According to Oracle’s documentation, using Jersey, if an instance is returned by a resource method and the most acceptable media type is one of application/javascript, application/x-javascript, text/ecmascript, application/ecmascript or text/jscript then the object that is contained by the instance is serialized as JSON (if supported, using the application/json media type) and the result is wrapped around a JavaScript callback function, whose name by default is “callback”. Otherwise, the object is serialized directly according to the most acceptable media type. This means that an instance can be used to produce the media types application/json, application/xml in addition to application.

There is plenty of opinions on the Internet about the pros and cons of using JSONP over other alternatives to get around the same origin policy. Regardless of the cons, JSONP, with the help of Jersey, provides the ability to call a RESTful web service from a remote server, without a lot of additional coding or security considerations.

Objectives

Similar to GlassFish, Jersey is also tightly integrated into NetBeans. NetBeans provides the option to use Jersey-specific features when creating RESTful web services. According to documentation, NetBeans will generate a web.xml deployment descriptor and to register the RESTful services in that deployment descriptor instead of generating an application configuration class. In this post, we will create Jersey-specific RESTful web service from a database using NetBeans. The service will return JSONP in addition to JSON and XML.

In addition to creating the RESTful web service, in part 2 of this series, we will create a simple web client to display the JSONP returned by the service. There are many options available for creating clients, depending on your development platform and project requirements. We will keep it simple – no complex compiled code, just simple JavaScript using Ajax and jQuery, the well-known JavaScript library.

We will host the RESTful web service on one GlassFish domain, running on a Windows box, along with the SQL Server database. We will host the client on a second GlassFish domain, running on an Ubuntu Linux VM using Oracle’s VM VirtualBox. This is a different machine than the service was installed on. When opened by the end-user in a web browser, the client files, including the JavaScript file that calls the service, are downloaded to the end-users machine. This will simulate a typical cross-domain situation where a client application needs to consume RESTful web services from a remote source. This is not allowed by the same origin policy, but overcome by returning JSONP to the client, which wraps the JSON payload in a function call.

Demonstration

Here are the high-level steps we will walk-through in this two-part series of posts:

  1. In a new RESTful web service web application project,
    1. Create an entity class from the Adventure Works database using EclipseLink;
    2. Create a Jersey-specific RESTful web service using the entity class using Jersey and JAXB;
    3. Add a new method to service, which leverages Jersey and Jackson’s abilities to return JSONP;
    4. Deploy the RESTful web service to a remote instance of GlassFish, using Apache Ant;
    5. Test the RESTful web service using cURL.
  2. In a new RESTful web service client web application project,
    1. Create a simple HTML client using jQuery and Ajax to call the RESTful web service;
    2. Add jQuery functionality to parse and display the JSONP returned by the service;
    3. Deploy the client to a separate remote instance of GlassFish using Apache Ant;
    4. Test the client’s ability to call the service across domains and display JSONP.

To demonstrate the example in this post, I have the follow applications installed, configured, and running in my development environment:

For the database we will use the Microsoft SQL Server 2008 R2 Adventure Works database I’ve used in the past few posts. For more on the Adventure Works database, see my post, ‘Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3’. Not using SQL Server? Once you’ve created your data source, most remaining steps in this post are independent of the database you choose, be it MySQL, Oracle, Microsoft SQL Server, Derby, etc.

For a full explanation of the use of Jersey and Jackson JSON Processor, for non-Maven developers, as this post demonstrates, see this link to the Jersey 1.8 User Guide. It discusses several relevant topics to this article: Java Architecture for XML Binding (JAXB), JSON serialization, and natural JSON notation (or, convention). See this link from the User Guide, for more on natural JSON notation. Note this example does not implement natural JSON notation functionality.

Creating the RESTful Web Service

New NetBeans Web Application Project
Create a new Java Web Application project in NetBeans. Name the project. I named mine ‘JerseyRESTfulService’. The choice of GlassFish server and domain where the project will be deployed is unimportant. We will use Apache Ant to deploy the service when we finish the building the project. By default, I chose my local instance of GlassFish, for testing purposes.

01a - Create a New Web ApplicationProject in NetBeans

Create a New Web Application Project in NetBeans

01b - Create a New Web ApplicationProject in NetBeans

Name and Location of New Web Application Project

01c - Create a New Web Application Project in NetBeans

Server and Settings of New Web Application Project

01d - Create a New Web Application Project in NetBeans

Optional Frameworks to Include in New Web Application Project

01e - Create a New Web Application Project in NetBeans

View of New Web Application Project in NetBeans

Create Entity Class from Database
Right-click on the project again and select ‘New’ -> ‘Other…’. From the list of Categories, select ‘Persistence’. From the list of Persistence choices, choose ‘Entity Classes from Database’. Click Next.

02a - Create Entity Classes from the Database

Create Entity Classes from the Database

Before we can choose which database table we want from the Adventure Works database to create entity class, we must create a connection to the database – a SQL Server Data Source. Click on the Data Source drop down and select ‘New Data Source…’. Give a Java Naming and Directory Interface (JNDI) name for the data source. I called mine ‘AdventureWorks_HumanResources’. Click on the ‘Database Connection’ drop down menu, select ‘New Database Connection…’.

02b - Create Entity Classes from the Database

Select Database Tables for Entity Classes (No Data Source Exists Yet)

02c - Create Entity Classes from the Database

Create and Name a New Data Source

This starts the ‘New Connection Wizard’. The first screen, ‘Locate Driver’, is where we point NetBeans to the Microsoft JDBC Driver 4.0 for SQL Server Driver. Locate the sqljdbc4.jar file.

02d - Create Entity Classes from the Database

Add the Microsoft JDBC Driver 4.0 for SQL Server Jar File

On the next screen, ‘Customize the Connection’, input the required SQL Server information. The host is the machine your instance of SQL Server is installed on, such as ‘localhost’. The instance is the name of the SQL Server instance in which the Adventure Works database is installed, such as ‘Development’. Once you complete the form, click ‘Test Connection’. If it doesn’t succeed, check your settings, again. Keep in mind, ‘localhost’ will only work if your SQL Server instance is local to your GlassFish server instance where the service will be deployed. If it is on a separate server, make sure to use that server’s IP address or domain name.

02e - Create Entity Classes from the Database

Configure New Database Connection

As I mentioned in an earlier post, the SQL Server Data Source forces you to select a single database schema. On the ‘Choose Database Schema’ screen, select the ‘HumanResources’ schema. The database tables you will be able to reference from you entity classes are limited to just this schema, when using this data source. To reference other schemas, you will need to create more data sources.

02f - Create Entity Classes from the Database

Select Human Resources Database Schema

Back in the ‘New Entity Classes from Database’ window, you will now have the ‘AdventureWorks’ data source selected as the Data Source. After a few seconds of processing, all ‘Available Tables’ within the ‘HumanResources’ schema are displayed. Choose the ‘vEmployee(view)’. A database view is a virtual database table. Note the Entity ID message. We will need to do an extra step later on, to use the entity class built from the database view.

02g - Create Entity Classes from the Database

Choice of Database Tables and Views from Human Resources Schema

02h - Create Entity Classes from the Database

Choose the ‘vEmployee(view)’ Database View

On the next screen, ‘Entity Classes’, in the ‘New Entity Classes from Database’ window, select or create the Package to place the individual entity classes into. I chose to call mine ‘entities’.

02i-create-entity-classes-from-the-database

Select/Create the Package Location for the Entity Class

On the next screen, ‘Mapping Options’, choose ‘Fully Qualified Database Table Names’. Without this option selected, I have had problems trying to make the RESTful web services function properly. This is also the reason I chose to create the entity classes first, and then create the RESTful web services, separately. NetBeans has an option that combines these two tasks into a single step, by choosing ‘RESTful Web Services from Database’. However, the ‘Fully Qualified Database Table Names’ option is not available on the equivalent screen, using that process (at least in my version of NetBeans 7.2). I prefer the two-step approach.

02j - Create Entity Classes from the Database

Select the ‘Fully Qualified Database Table Names’ Mapping Options

Click finished. You have successfully created the SQL Server data source and entity classes.

02k - Create Entity Classes from the Database

Project View of New VEmployee Entity Class

If you recall, I mentioned a problem with the entity class we created from the database view. To avoid an error when you build and deploy your project to GlassFish, we need to make a small change to the VEmployee.java entity class. Entity classes need a unique identifier, a primary key (or, Entity ID) identified. Since this entity class was built from database view, as opposed to database table, it lacks a primary key. To fix, annotate the businessEntityID field with @Id. This indicates that businessEntityID is the primary key (Entity ID) for this class. The field, businessEntityID, must contain unique values, for this to work properly. NetBeans will make the suggested correction for you, if you allow it.

02l - Create Entity Classes from the Database

Fix the Entity Class’s Missing Primary Key (Entity ID)

02m - Create Entity Classes from the Database

Fix the Entity Class’s Missing Primary Key (Entity ID)

02n - Create Entity Classes from the Database

Entity Class With Primary Key (Entity ID)

The JPA Persistence Unit is found in the ‘persistence.xml’ file in the ‘Configuration Files’ folder. This file describes the Persistence Unit (PU). The PU serves to register the project’s persistable entity class, which are referred to by JPA as ‘managed classes’.

02o - Create Entity Classes from the Database

View of New JPA Persistence Unit

The data source we created, which will be deployed to GlassFish, is referred to as a JDBC Resource and JDBC Connection Pool. This information is stored in the ‘glassfish-resources.xml’.

02p - Create Entity Classes from the Database

View of New JDBC Resource and JDBC Connection Pool

Create RESTful Web Service
Now that have a SQL Server Data Source and our entity class, we will create the RESTful web service. Right-click on the project and select ‘New’ -> ‘Other…’ -> ‘Persistence’ -> ‘RESTful Web Services from ‘Entity Classes’. You will see the entity class we just created, from which to choose. Add the entity class.

04a - Create RESTful Web Services from Entity Classes

Create RESTful Web Services from Entity Classes

04b - Create RESTful Web Services from Entity Classes

Choose from List of Available Entity Classes

04c - Create RESTful Web Services from Entity Classes

Choose the VEmployee Entity Class

On the next screen, select or create the Resource Package to store the service class in; I called mine ‘service’. Select the ‘Use Jersey Specific Features’ option.

04d - Create RESTful Web Services from Entity Classes

Select/Create the Service’s Package Location and Select the Option to ‘Use Jersey Specific Features’

That’s it. You now have a Jersey-specific RESTful web service and the corresponding Enterprise Bean and Façade service class in the project.

04e - Create RESTful Web Services from Entity Classes

Project View of New RESTful Web Service and Associated Files

NetBeans provides an easy way to test the RESTful web services, locally. Right-click on the ‘RESTful Web Services’ project folder within the main project, and select ‘Test RESTful Web Services’. Select the first option, ‘Locally Generated Test Client’, in the ‘Configure REST Test Client’ pop-up window. NetBeans will use the locally configured GlassFish instance to deploy and test the service.

NetBeans opens a web browser window and display the RESTful URIs (Universal Resource Identifier) for the service in a tree structure. There is a parent URI, ‘entities.vemployee’. Selecting this URI will return all employees from the vEmployee database view. The ‘entities.vemployee’ URI has additional children URIs grouped under it, including ‘{id}’, ‘count’, and ‘{from/to}’, each mapped to separate methods in the service class.

Click on the ‘{id}’ URI. Choose the HTTP ‘GET()’ request method from the drop-down, enter ‘1’  for ‘id’, and click the ‘Test’ button. The service should return a status of ‘200 (OK)’, along with xml output containing information on all the Adventure Works employees. Change the MIME type to ‘application/json’. This should return the same result, formatted as JSON. Congratulation, the RESTful web services have just returned data to your browser from the SQL Server Adventure Works database, using the entity classes and data source you created.

Are they URIs or URLs? I found this excellent post that does a very good job explaining the difference between the URL (how to get there) and the URI (the resource), which is part of the URL.

04f - Create RESTful Web Services from Entity Classes

Test the RESTful Web Service Locally in NetBeans (XML  Response Shown)

04g - Create RESTful Web Services from Entity Classes

Test the RESTful Web Service Locally in NetBeans (JSON Response Shown)

Using Jersey for JSONP
GlassFish comes with the jersey-core.jar installed. In order to deliver JSONP, we also need to import and use com.sun.jersey.api.json.JSONWithPadding package from jersey-json.jar. I downloaded and installed version 1.8. You can download the jar from several locations. I chose to download it from www.java2.com. You can also download from the download.java.net Maven2 repository.

03b - Installing Jersey JSON

Add the Jersey JSON Jar File to the Project

The com.sun.jersey.api.json.JSONWithPadding package has dependencies two Jackson JSON Processor jars. You will also need to download the necessary Jackson JSON Processor jars. They are the jackson-core-asl-1.9.8.jar and jackson-mapper-asl-1.9.8.jar. At the time of this post, I downloaded the latest 1.9.8 versions from the grepcode.com Maven2 repository.

03e - Installing Jackson JSON Processor

Add the two Jackson JSON Processor Jar Files to the Project

Create New JSONP Method

NetBeans creates several default methods in the VEmployeeFacadeREST class. One of those is the findRange method. The method accepts two integer parameters, from and to. The parameter values are extracted from the URL (JAX-RS @Path annotation). The parameters are called path parameters (@PathParam). The method returns a List of VEmployee objects (List<VEmployee>). The findRange method can return two MIME types, XML and JSON (@Produces). The List<VEmployee> is serialized in either format and returned to the caller.

@GET
@Path("{from}/{to}")
@Produces({"application/xml", "application/json"})
public List<VEmployee> findRange(@PathParam("from") Integer from, @PathParam("to") Integer to) {
    return super.findRange(new int[]{from, to});
}

Neither XML nor JSON will do, we want to return JSONP. Well, using the JSONWithPadding class we can do just that. We will copy and re-write the findRange method to return JSONP. The new findRangeJsonP method looks similar to the findRange. However instead of returning a List<VEmployee>, the new method returns an instance of the JSONWithPadding class. Since List<E> extends Collection<E>, we make the same call as the first method, then cast the List<VEmployee> to Collection<VEmployee>. We then wrap the Collection in a GenericEntity<T>, which extends Object. The GenericEntity<T> represents a response entity of a generic type T. This is used to instantiate a new instance of the JSONWithPadding class, using the JSONWithPadding(Object jsonSource, String callbackName) constructor. The JSONWithPadding instance, which contains serialized JSON wrapped with the callback function, is returned to the client.

@GET
@Path("{from}/{to}/jsonp")
@Produces({"application/javascript"})
public JSONWithPadding findRangeJsonP(@PathParam("from") Integer from,
        @PathParam("to") Integer to, @QueryParam("callback") String callback) {
    Collection<VEmployee> employees = super.findRange(new int[]{from, to});
    return new JSONWithPadding(new GenericEntity<Collection<VEmployee>>(employees) {
    }, callback);
}

We have added a two new parts to the ‘from/to’ URL. First, we added ‘/jsonp’ to the end to signify the new findRangeJsonP method is to be called, instead of the original findRange method. Secondly, we added a new ‘callback’ query parameter (@QueryParam). The ‘callback’ parameter will pass in the name of the callback function, which will then be returned with the JSONP payload. The new URL format is as follows:

http://%5Byour-service's-glassfish-server-name%5D:%5Byour-service's-glassfish-domain-port%5D/JerseyRESTfulService/webresources/entities.vemployee/{from}/{to}/jsonp?callback={callback}

06a - Adding Jersey JSONP Method

Add the Following Jersey JSONP Method to the RESTful Web Service Class

06b - Adding Jersey JSONP Method

Adding the Method Requires Importing the ‘JSONWithPadding’ Library

Deployment to GlassFish
To deploy the RESTful web service to GlassFish, run the following Apache Ant target. The target first calls the clean and dist targets to build the .war file, Then, the target calls GlassFish’s asadmin deploy command. It specifies the remote GlassFish server, admin port, admin user, admin password (in the password file), secure or insecure connection, the name of the container, and the name of the .war file to be deployed. Note that the server is different for the service than it will be for the client in part 2 of the series.

<target name="glassfish-deploy-remote" depends="clean, dist"
        description="Build distribution (WAR) and deploy to GlassFish">
    <exec failonerror="true" executable="cmd" description="asadmin deploy">
        <arg value="/c" />
        <arg value="asadmin --host=[your-service's-glassfish-server-name] 
            --port=[your-service's-glassfish-domain-admin-port]
            --user=admin --passwordfile=pwdfile --secure=false
            deploy --force=true --name=JerseyRESTfulService
            --contextroot=/JerseyRESTfulServicedist\JerseyRESTfulService.war" />
    </exec>
</target>
Deploy RESTful Web Service to Remote GlassFish Server

Deploy RESTful Web Service to Remote GlassFish Server Using Apache Ant Target

In GlassFish, you should see the several new elements: 1) JerseyRESTfulService Application, 2) AdventureWorks_HumanResources JDBC Resource, 3) microsoft_sql_AdventureWorks_aw_devPool JDBC Connection Pool. These are the elements that were deployed by Ant. Also note, 4) the RESTful web service class, VEmployeeFacadeREST, is an EJB StatelessSessionBean.

08b - Deploy RESTful Web Service to Remote GlassFish Server

RESTful Web Service Deployed to Remote GlassFish Server

Test the Service with cURL
What is the easiest way to test our RESTful web service without a client? Answer, cURL, the free open-source URL tool. According to the website, “curl is a command line tool for transferring data with URL syntax, supporting DICT, FILE, FTP, FTPS, Gopher, HTTP, HTTPS, IMAP, IMAPS, LDAP, LDAPS, POP3, POP3S, RTMP, RTSP, SCP, SFTP, SMTP, SMTPS, Telnet and TFTP. curl supports SSL certificates, HTTP POST, HTTP PUT, FTP uploading, HTTP form based upload, proxies, cookies, user+password authentication (Basic, Digest, NTLM, Negotiate, kerberos…), file transfer resume, proxy tunneling and a busload of other useful tricks.

To use cURL, download and unzip the cURL package to your system’s Programs directory. Add the cURL directory path to your system’s PATH environmental variable. Better yet, create a CURL_HOME environmental variable and add that reference to the PATH variable, as I did. Adding the the cURL directory path to PATH allows you to call the cURL.exe application, directly from the command line.

07b - Test New Method with cURL

Add the cURL Directory Path to the ‘PATH’ Environmental Variable

With cURL installed, we can call the RESTful web service from the command line. To test the service’s new method, call it with the following cURL command:

curl -i -H "Accept: application/x-javascript" -X GET http://%5Byour-service's-glassfish-server-name%5D:%5Byour-service's-glassfish-domain-port%5D/JerseyRESTfulService/webresources/entities.vemployee/1/3/jsonp?callback=parseResponse

07c - Test New Method with cURL

Using cURL to Call RESTful Web Service and Return JSONP

Using cURL is great for testing the RESTful web service. However, the command line results are hard to read. I recommend copy the cURL results into NotePad++ with the JSON Viewer Plugin. Like the NotePad++ XML plugin, the JSON plugin will format the JSONP and provide a tree view of the data structure.

05c - Notepad++ JSON Viewer

Notepad++ Displaying JSONP Using the JSON Viewer Plugin

Conclusion

Congratulations! You have created and deployed a RESTful web service with a method capable of returning JSONP. In part 2 of this series, we will create a client to call the RESTful web service and display the JSONP response payload. There are many options available for creating clients, depending on your development platform and project requirements. We will keep it simple – no complex, compiled code, just simple JavaScript using Ajax and jQuery, the well-known JavaScript library.

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

21 Comments

Connecting Java EE RESTful Web Services to Microsoft SQL Server Using NetBeans and GlassFish

Connecting Java EE RESTful web services, hosted on GlassFish, to Microsoft SQL Server – a high level overview. Demonstrate the creation of a Web Application project in NetBeans, including a SQL Server data source, entity classes from a SQL database, and RESTful web services. Show how to test and deploy the project to GlassFish.

Introduction

In a previous post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, I demonstrated how Microsoft’s JDBC Driver 4.0 can connect Java-based RESTful web services to Microsoft SQL Server. In a more recent post, Calling Microsoft SQL Server Stored Procedures from a Java Application Using JDBC, I demonstrated the use of JDBC to call stored procedures from a Java application. In this post, I am going to offer a high-level, end-to-end overview on how to create and connect Java EE RESTful web services, hosted on GlassFish, to SQL Server. The goals of post are:

  • Demonstrate the use of Microsoft’s JDBC Driver 4.0 to connect a Java-based application to SQL Server 2008 R2;
  • Demonstrate the use of NetBeans to:
    • Create a SQL Server Data Source;
    • Create entity classes from the SQL Server database using the SQL Server Data Source;
    • Create RESTful web services using JAX-RS, which communicate with database, through the entity classes;
    • Compile and deploy the data source, entities, and services to GlassFish;
  • Test the RESTful Web Services locally in NetBeans, and once deployed, in GlassFish.

Setting up the Post’s Example

To demonstrate the example in this post, I have the follow applications installed, configured, and running in my development environment:

If you have any questions about installing and configuring the Adventure Works database, please refer to my post, Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3. The post takes you through creating a SQL Server 2008 R2 instance (entitled ‘Development’), installing the Adventure Works database, and creating a database user (‘aw-dev’). Also, refer to my earlier post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, for directions on installing the Microsoft JDBC driver in the lib directory of GlassFish. This is necessary before following along with this demonstration.

Note, since I have NetBeans, SQL Server, and GlassFish all installed on a single computer, the URLs in several of the screen-grabs switch between ‘localhost’ and my computer’s actual name. Both references are interchangeable.

The Demonstration

Here are the high-level steps I will walk-through in this post:

  1. Confirm the SQL Server instance, database, and user are functioning properly;
  2. Create a new Web Application project in NetBeans;
  3. Create the SQL Server data source in the project;
  4. Create entity classes from the SQL Server database;
  5. Create RESTful web services using the entity classes;
  6. Test the web services locally in NetBeans;
  7. Build and deploy the project to GlassFish;
  8. Test the web services on GlassFish.

SQL Server

Using Microsoft’s SQL Server Management Studio, Quest’s Toad for SQL, or similar IDE, confirm the ‘Development’ instance (or whatever you have named your instance) of SQL Server 2008 R2 is running. Confirm the Adventure Works database is installed in that instance. Lastly, confirm the ‘aw_dev’ user can connect to the Adventure Works database and view and interact with all the database objects. Confirming these items will elevate many problems you might otherwise encounter when creating the data source, next.

SQL Server 2008 R2 AdventureWorks Database

View of the SQL Server Instance from SSMS. Note the : 1) Server, Instance, User, 2) Database, and 3) Schema

Create New Web Application Project

Create a new Java Web Application project in NetBeans. Name the project whatever you would like; I named mine ‘JdbcSqlWebSrvTest’. Select the correct GlassFish server instance and GlassFish domain where the project will be deployed. I am deploying my project to the default ‘domain1’ domain.

Creating a New Java Web Application Project in NetBeans - 02

Choose the Web Application Project-Type in NetBeans

Creating a New Java Web Application Project in NetBeans - 03

Provide a Project Name and Location

Creating a New Java Web Application Project in NetBeans - 04

Select the Target GlassFish Server and Domain Where the Project Will be Installed

Creating a New Java Web Application Project in NetBeans - 05

Include any Frameworks You Will Use with the Project

Creating a New Java Web Application Project in NetBeans - 06

View of the New Web Application Project in NetBeans

Create SQL Server Data Source and Entity Classes from the Database

Right-click on the project again and select ‘New’ -> ‘Other…’. From the list of Categories, select ‘Persistence’. From the list of Persistence choices, choose ‘Entity Classes from Database’. Click Next.

Creating New Entity Classes from a Database - 01

Choose ‘Entity Classes from Database’

Before we can choose which database tables we want from the Adventure Works database to create entity classes, we must create a connection to the database – a SQL Server Data Source. Click on the Data Source drop down and select ‘New Data Source…’. Give a Java Naming and Directory Interface (JNDI) name for the data source. I called mine ‘AdventureWorks’. Click on the ‘Database Connection’ dropdown, select ‘New Database Connection…’.

Creating New Entity Classes from a Database - 02

Database Tables View Before Data Source is Created

Creating a New Datasource for Entity Classes from a Database - 01

Name the Data Source

This starts the ‘New Connection Wizard’. The first screen, ‘Locate Driver’, is where we point the will instruct NetBeans to use the Microsoft JDBC Driver 4.0 for SQL Server Driver. Locate the sqljdbc4.jar file.

Creating a New Datasource for Entity Classes from a Database - 02

Locate the Microsoft JDBC Driver 4.0 for SQL Server Driver .jar File

On the next screen, ‘Customize the Connection’, input the required SQL Server information. The host is the machine your instance of SQL Server is installed on, such as ‘localhost’. The instance is the name of the SQL Server instance in which the Adventure Works database is installed, such as ‘Development’. Once you complete the form, click ‘Test Connection’. If it doesn’t succeed, check your settings, again.

Creating a New Datasource for Entity Classes from a Database - 03

Provide the SQL Server Adventure Works Database Connection Information

As I mentioned in an earlier post, the SQL Server Data Source forces you to select a single database schema. On the ‘Choose Database Schema’ screen, select the ‘HumanResources’ schema. The database tables you will be able to reference from you entity classes are limited to just this schema, when using this data source. To reference other schemas, you will need to create more data sources.

Creating a New Datasource for Entity Classes from a Database - 04

Select the ‘HumanResources’ Database Schema

Creating a New Datasource for Entity Classes from a Database - 05

New Data Source Complete with JNDI Name and Database Connection Created

Back in the ‘New Entity Classes from Database’ window, you will now have the ‘AdventureWorks’ data source selected as the Data Source. After a few seconds of processing, all ‘Available Tables’ within the ‘HumanResources’ schema are displayed. Choose the four tables shown in the screen-grab, below. Actually, two are database tables and two are virtual tables, called database ‘views’. We will need to do an extra step later on, to use the two entity classes built from the database views.

Creating New Entity Classes from a Database - 03A

Retrieving all the ‘HumanResources’ Schema Database Tables and Views

Creating New Entity Classes from a Database - 03B

All the ‘HumanResources’ Schema Database Tables and Views Available

Creating New Entity Classes from a Database - 04

Selecting the Database Views Throws a Warning Regarding no Entity IDs (Primary Keys)

On the next screen, ‘Entity Classes’, in the ‘New Entity Classes from Database’ window, give a package name to place the individual entity classes into. I chose to call mine ‘entityclasses’.

Creating New Entity Classes from a Database - 05

Provide the Name of the Package Where the Entity Classes Will be Created

On the next screen, ‘Mapping Options’, choose ‘Fully Qualified Database Table Names’. Without this option selected, I have had problems trying to make the RESTful web services function properly. This is also the reason I chose to create the entity classes first, and then create the RESTful web services, separately. NetBeans has an option that combines these two tasks into a single step, by choosing ‘RESTful Web Services from Database’. However, the ‘Fully Qualified Database Table Names’ option is not available on the equivalent screen, using that process (at least in my version of NetBeans 7.2). I prefer the two-step approach.

Creating New Entity Classes from a Database - 06

Select the ‘Fully Qualified Database Table Names’ Option

Click finished. You have successfully created the SQL Server data source and entity classes.

The data source we created, which will be deployed to GlassFish, is referred to as a JDBC Resource and a JDBC Connection Pool. This JDBC information is stored in the ‘glassfish-resources.xml’ file by NetBeans.

New Glassfish JDBC Connection Pool and Resource - Source (glassfish-resources.xml)

New GlassFish JDBC Connection Pool and JDBC Resource Contained in the glassfish-resources.xml File

The JPA Persistence Unit is found in the ‘persistence.xml’ file in the ‘Configuration Files’ folder. This file describes the Persistence Unit (PU). The PU serves to register the project’s four persistable entity classes, which are referred to by JPA as managed classes.

JPA Configuration - Design View (persistence.xml)

persistence.xml Design View

JPA Configuration - Source (persistence.xml)

persistence.xml Source View

RESTful Web Services from Entity Classes

Now that have a SQL Server Data Source and our entity classes, we will create the RESTful web services. Right-click on the project and select ‘New’ -> ‘Other…’ -> ‘Persistence’ -> ‘RESTful Web Services from ‘Entity Classes’. You will see a list of four entity classes we just created, from which to choose. Add all four entity classes.

Creating New RESTful Web Service from Entity Classes - 01

Creating the New RESTful Web Service from Four Entity Classes

Creating New RESTful Web Service from Entity Classes - 02

Add the Four Entity Classes

On the next screen, give a name for Resource Package to store the service classes in; I called mine ‘service’. That’s it; you now have four RESTful web services and the corresponding Enterprise Beans and Façade service classes. The service class sits between the RESTful web service and the entity class.

Creating New RESTful Web Service from Entity Classes - 03

Provide a Package Location to Place the RESTful Web Service Classes Into

Click finished. You have successfully created the RESTful web services.

Web Application Project with Entities and Services Added - 01

View of Web Application Project with Entity Classes and RESTful Web Services Added

Web Application Project with Entities and Services Added - 02

View of Web Application Project with Entity Classes and RESTful Web Services Added

Adding a Primary Key to Entity Classes

If you recall, I mentioned a problem with the two entity classes we created from the database views. To avoid an error when you build and deploy your project to GlassFish, we need to make a small change to the VEmployee.java and VEmployeeDepartment.java entity classes. Entity classes need a unique identifier, a primary key (or, Entity ID) identified. Since these two entity classes are built from database views, as opposed to database tables, they lack a primary key. To fix this, annotate the ‘businessEntityID’ field with ‘@Id’ in each class. This indicates that ‘businessEntityID’ is the primary key (Entity ID) for this class. The field, ‘businessEntityID’, must contain unique values, for this to work properly. NetBeans will make the suggested correction for you, if you allow it.

Fix Entity Id Error in View Entity Classes - 01

NetBeans Highlights the Entity Id Error in Two View-Based Entity Classes

Fix Entity Id Error in View Entity Classes - 02

Select the ‘businessEntityID’ Field as the Primary Key

Fix Entity Id Error in View Entity Classes - 03

View of Entity Class with ‘@Id’ Annotation Added

Test RESTful Web Services Locally in NetBeans

NetBeans provides an easy way to test the RESTful web services, locally. Right-click on the ‘RESTful Web Services’ project folder within the main project, and select ‘Test RESTful Web Services’. Select the first option, ‘Locally Generated Test Client’, in the ‘Configure REST Test Client’ pop-up window.

Test RESTful Web Service in NetBeans - 01

Choose the Local Test Client Option

NetBeans will open a web browser window and displays the RESTful URI (Universal Resource Identifier) for the services in a tree structure. There are four primary URIs, corresponding to the four services. Each primary URI has additional child URIs grouped under them. Are they URIs or URLs? I found this excellent post that does a very good job explaining the difference between the URL (how to get there) and the URI (the resource), which is part of the URL.

Test RESTful Web Service in NetBeans - 02

You May Get an ActiveX Warning When Using IE to Test the RESTful Web Services

Test RESTful Web Service in NetBeans - 03

View of all the RESTful Web Services Universal Resource Identifiers (URIs)

Click on the ‘entityclasses.employee’ URI. Choose the HTTP ‘GET()’ request method from the drop-down and click the ‘Test’ button. The service should return a status of ‘200 (OK)’, along with xml output containing information on all the Adventure Works employees. Congratulation, the RESTful web services have just returned data to your browser from the SQL Server Adventure Works database, using the entity classes and data source you created.

Test RESTful Web Service in NetBeans - 04

All Employees Being Successfully Retrieved from the Adventure Works Database

Click on the other URIs to familiarize yourself with the various default resources. Test the employee ‘from/to’ URI by inputting two parameters, test the ‘count’ URI, and try changing the MIME type where applicable from XML to JSON and observe the results.

Test RESTful Web Service in NetBeans - 05

A Single Employee Being Successfully Retrieved from the Adventure Works Database Using Input Parameter

Test RESTful Web Service in NetBeans - 06

Count of All Employees Being Successfully Retrieved from the Adventure Works Database

WADL

Note the link in the upper right corner of the above screens, labeled WADL: ‘http://[your_server_path]/JdbcSqlWebSrvTest/webresources/application.wadl’

The WADL (Web Application Description Language) file is the machine-readable XML description of the RESTful web service(s). The WADL file is to RESTful web services, as the WSDL (Web Service Definition Language) file is to non-RESTful, SOA-/SOAP-oriented web services. The WADL provides all the information you need to understand to the various RESTful web service’s resources, and how to call them using their URIs. According to Wikipedia, in the WADL file, ‘the service is described using a set of resource elements. Each resource has param elements to describe the inputs, and method elements which describe the request and response of a resource. The request element specifies how to represent the input, what types are required and any specific HTTP headers that are required. The response describes the representation of the service’s response, as well as any fault information, to deal with errors.’ You can download the WADL file (application.wadl), and review it in an XML-friendly viewer such as Notepad++.

View of WADL - application.wadl

View of the RESTful Web Services’ WADL File – application.wadl

Deploy Project to GlassFish

Now that the RESTful web services are working properly from within NetBeans, we can deploy them to GlassFish. To deploy the project to GlassFish, right-click on the main project icon in the Projects tab and select ‘Clean and Build’. Once the project builds successfully, right-click again and select ‘Deploy’. This will instruct Apache Ant to deploy the project as a .war file to GlassFish, using the project’s default Ant deploy task. The SQL Server data source will also be installed into GlassFish.

Once the deployment is complete, switch to GlassFish and refresh the home page if necessary. Under the ‘Applications’ item on the left-hand navigation menu, you should see a new application with the same name as your project, ‘JdbcSqlWebSrvTest’.

GlassFish 01 - Prior to Deploying New Application

Default View of GlassFish Domain Prior to Deploying the New Web Application

GlassFish 02 - New Application Deployed to GlassFish

New Web Application and JDBC Resource and Pool Deployed Successfully to GlassFish

Also, under the ‘JDBC’ -> ‘JDBC Resources’ item, you should see a resource with the same name as the data source you created in NetBeans, ‘AdventureWorks’. Under the ‘JDBC’ -> ‘JDBC Connection Pools’, you should see a pool entitled ‘microsoft_sql_AdventureWorks_aw_devPool’. The JDBC Resource, ‘AdventureWorks’, is linked to this pool. The pool is a ‘javax.sql.DataSource’ resource type, which references the ‘com.microsoft.sqlserver.jdbc.SQLServerDataSource’. This data source is identical to the data source you built in NetBeans.

GlassFish 03 - New Datasource (Resource) Deployed to GlassFish

New JDBC Resource Successfully Deployed to GlassFish

GlassFish 04 - New JDBC Connection Pool

New JDBC Connection Pool Successfully Deployed to GlassFish

Test Web Services on GlassFish

To test the RESTful web services from GlassFish, begin by clicking on the ‘JdbcSqlWebSrvTest’ application, under ‘Applications’ menu item. On the Applications page, click on the ‘Launch’ action link. GlassFish open a new web browser window, and presents you with two ‘Web Application Links’, one link is HTTP and the other, HTTPS. Click on the HTTP link. This should display the default index.jsp page’s ‘Hello World!’ message.

Test RESTful Web Service in GlassFish - 01

Default Response from Application

To call the service, append the current URL to match the resource URIs you used when testing the services in NetBeans. For example, to display all the employees again like you did in NetBeans, append the current URL, http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/, to include the following:

http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee

This URI should return the same xml content you observed when testing this same URI locally in NetBeans.

Test RESTful Web Service in GlassFish - 02

All Employees Being Successfully Retrieved from the Adventure Works Database

As another test, append the URI to also include the Id of a single employee, as follows:

http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee/2

This should cut the amount of data returned from the Adventure Works database to a single employee record.

Test RESTful Web Service in GlassFish - 03

A Single Employee Being Successfully Retrieved from the Adventure Works Database Using a Parameter

One last test, remove the number two from the URI and add the word ‘count’, as follows:

http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee/count

This time, you should see a single integer returned to the browser, representing the count of all employees in the database’s employee table.

Test RESTful Web Service in GlassFish - 04

Count of All Employees Being Successfully Retrieved from the Adventure Works Database

Conclusion

Congratulations, the Java EE RESTful web services have been successfully deployed to GlassFish. The services are connecting to Adventure Works SQL Server database, through the entity classes and data source, and returning data to your web browser! Next step is to create a RESTful web services client application, to display the data returned by the services and/or to perform CRUD operations on the database.

, , , , , , , , , , , , , , , , , , , ,

15 Comments

Calling Microsoft SQL Server Stored Procedures from a Java Application Using JDBC

Demonstrate the use of the JDBC to call stored procedures from a Microsoft SQL Server database and return data to a Java-based console application.

Console Output of Demonstration

Update 07/10/2015: All source code now on GitHub
Update 09/08/2020: Major source code update on GitHub:
MS SQL 2017, JDBC 8.4, Java 11, Gradle

Introduction

Enterprise software solutions often combine multiple technology platforms. Accessing an Oracle database via a Microsoft .NET application, or accessing Microsoft’s SQL Server from a Java-based application is common practice. In an earlier post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, I discussed the use of the Microsoft JDBC Driver 4.0 for SQL Server to connect a Java-based RESTful web service application to a Microsoft SQL Server database. In this post, I will demonstrate the use of JDBC (Java Database Connectivity) to call stored procedures from a Microsoft SQL Server 2008 R2 database and return data to a Java-based console application. The objectives of this post include:

  • Demonstrate the differences between using static SQL statements and stored procedures to return data
  • Demonstrate the use of three types of JDBC statements to return data: Statement, PreparedStatement, and CallableStatement
  • Demonstrate how to call stored procedures with input and output parameters
  • Demonstrate how to return single values and a result set from a database using stored procedures

Why Stored Procedure?

To access data, many enterprise software organizations require their developers to call stored procedures within their code as opposed to executing static SQL statements against the database. There are several common reasons stored procedures are preferred:

  • Optimization – Stored procedures are often written by DBAs or developers who specialize in database development. They understand the best way to construct queries for optimal performance and minimal load on the database server. Think of it as a developer using an API to interact with the database.
  • Safety and Security – Stored procedures are considered safer and more secure than static SQL statements. The stored procedure provides tight control over the content of the queries, preventing malicious or unintentionally destructive code from being executed against the database.
  • Error Handling – Stored procedures can contain logic for handling errors before they bubble up to the application layer and possibly to the end-user.

Adventure Works 2008 Database

For brevity, I will use an existing SQL Server instance and database I’ve already created for recent series of blog posts. The first post in that series, Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3, covers the creation of a SQL Server 2008 R2 instance and installation of Microsoft’s Adventure Works database. The database comes prepopulated with plenty of data for demonstration purposes. In addition to the SQL Server instance and database, I will also use the ‘aw_dev’ user account created in that post. However, for this post, we will need to add additional permission to call or ‘Execute’ the database queries.

Add Execute Permission to User

Lastly, I have added four stored procedures to the Adventure Works database to use in this post. In order to follow along, you will need to install these stored procedures. The SQL install scripts are included in the downloadable code. See the SQL statements below.

View of New Stored Procedures in Database
View of New Stored Procedures in Database

Data Sources, Connections, and Properties

After adding the Microsoft JDBC Driver 4.0 for SQL Server to the project, we create a SQL Server data source (com.microsoft.sqlserver.jdbc.SQLServerDataSource) and database connection (java.sql.Connection). There are several patterns for creating and working with data sources and connections. This post does not necessarily focus on the best practices for creating or using either. In this example, the application instantiates a connection class (SqlConnection.java), which in turn instantiates the java.sql.Connection and com.microsoft.sqlserver.jdbc.SQLServerDataSource objects. The data source’s properties are supplied from an instance of a singleton class (ProjectProperties.java). This properties class instantiates the java.util.Properties class and reads values from an XML property file (properties.xml). On startup, the application creates the database connection, calls each of the example methods, and then closes the connection.

New Java Project in NetBeans
New Java Project in NetBeans
Add JDBC Driver 4.0 Jar File
Add JDBC Driver 4.0 Jar File

Examples

For each example, I will show the stored procedure, if applicable, followed by the Java method that calls the procedure or executes the static SQL statement. I have left out all the data source and connection code in the article. Again, a complete copy of all the code for this article is available on GitHub. The GitHub project contains the complete NetBeans project, including some basic JUnit tests, in a zipped package. A second zipped package contains the SQL scripts necessary to create the four stored procedures used in the examples.

Example 1: A Simple Statement

Before jumping into stored procedures, we’ll start with a simple static SQL statement. This example’s method uses the java.sql.Statement class. According to the Oracle documentation, the Statement object is used for executing a static SQL statement and returning the results it produces. This SQL statement calculates the average weight of all products in the Adventure Works database that contain a weight. It returns a solitary double value. This example demonstrates one of the simplest methods for returning data from SQL Server.

    /**
     * Statement example, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightST() {
        double averageWeight = 0;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT ROUND(AVG([Weight]), 2)";
            sql += " FROM Production.Product";
            sql += " WHERE ([Weight] > 0)";
            sql += " AND (WeightUnitMeasureCode = 'LB')";
            stmt = connection.getConnection().createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                averageWeight = rs.getDouble(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 2: The Prepared Statement

Next, we’ll execute almost the same static SQL statement as Example 1. The only change is the addition of  ‘AS averageWeight’. This allows us to parse the results by column name, making the code easier to understand as opposed to using the numeric index of the column as in Example 1. Instead of using the java.sql.Statement class, we use the java.sql.PreparedStatement class. According to Oracle’s documentation, a SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

    /**
     * PreparedStatement example, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightPS() {
        double averageWeight = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            String sql = "SELECT ROUND(AVG([Weight]), 2) AS averageWeight FROM";
            sql += " Production.Product";
            sql += " WHERE ([Weight] > 0)";
            sql += " AND (WeightUnitMeasureCode = 'LB')";
            pstmt = connection.getConnection().prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                averageWeight = rs.getDouble("averageWeight");
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 3: The Callable Statement

In this example, the average product weight query has been moved into a stored procedure. The procedure is identical in functionality to the static statement in the first two examples. To call the stored procedure, we use the java.sql.CallableStatement class. Again according to Oracle’s documentation, the CallableStatement extends PreparedStatement. It is the interface used to execute SQL stored procedures. The CallableStatement accepts both input and output parameters, however, this simple example does not use either. Like the previous two examples, the procedure returns a single double number value.

CREATE PROCEDURE [dbo].[uspGetAverageProductWeight]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ROUND(AVG([Weight]), 2)
    FROM Production.Product
    WHERE ([Weight] > 0) AND (WeightUnitMeasureCode = 'LB')
END
GO

    /**
     * CallableStatement, no parameters, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightCS() {
        CallableStatement cstmt = null;
        double averageWeight = 0;
        ResultSet rs = null;
        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call uspGetAverageProductWeight}");
            cstmt.execute();
            rs = cstmt.getResultSet();
            if (rs.next()) {
                averageWeight = rs.getDouble(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 4: Calling a Stored Procedure with an Output Parameter

In this example, we use almost the same stored procedure as in Example 3. The only difference is the inclusion of an output parameter. This time, instead of returning a result set with a value in a single unnamed column, the column has a name – ‘averageWeight’. We can now call that column by name when retrieving the value.

The stored procedure patterns found in Examples 3 and 4 are both commonly used. One procedure uses an output parameter and one not, both return the same value(s). You can use the CallableStatement to for either type.

CREATE PROCEDURE [dbo].[uspGetAverageProductWeightOUT]
    @averageWeight DECIMAL(8,2) OUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @averageWeight = ROUND(AVG([Weight]), 2)
    FROM Production.Product
    WHERE ([Weight] > 0) AND (WeightUnitMeasureCode = 'LB')
END
GO

    /**
     * CallableStatement example, (1) output parameter, db returns integer
     *
     * @return Average weight of all products
     */
    public double getAverageProductWeightOutCS() {
        CallableStatement cstmt = null;
        double averageWeight = 0;

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call dbo.uspGetAverageProductWeightOUT(?)}");
            cstmt.registerOutParameter("averageWeight", java.sql.Types.DECIMAL);
            cstmt.execute();
            averageWeight = cstmt.getDouble("averageWeight");
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return averageWeight;
    }

Example 5: Calling a Stored Procedure with an Input Parameter

Enough calculating the average weight of products. Let’s move onto another stored procedure. In this new example, the procedure returns a result set (java.sql.ResultSet) of employees whose last name starts with a particular sequence of character (e.g., ‘M’ or ‘Sta’).  The sequence of characters is passed as an input parameter to the stored procedure using the CallableStatement, again.

The method making the call iterates through the rows of the result set returned by the stored procedure, concatenating multiple columns to form the employee’s full name as a string. Each full name string is then added to an ordered collection of strings, a List<String> object. The List instance is returned by the method. You will notice this procedure takes a little longer to run because of the use of the ‘LIKE’ operator. The database server has to perform pattern matching on each last name value in the table to determine the result set.

CREATE PROCEDURE [HumanResources].[uspGetEmployeesByLastName]
    @lastNameStartsWith VARCHAR(20) = 'A'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Title, FirstName, MiddleName, LastName, Suffix
    FROM Person.Person
    WHERE (PersonType = 'EM') AND (LastName LIKE @lastNameStartsWith + '%')
END
GO

    /**
     * CallableStatement example, (1) input parameter, db returns ResultSet
     *
     * @param lastNameStartsWith
     * @return List<String> of employee names
     */
    public List<String> getEmployeesByLastNameCS(String lastNameStartsWith) {

        CallableStatement cstmt = null;
        ResultSet rs = null;
        List<String> employeeFullName = new ArrayList<>();

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call HumanResources.uspGetEmployeesByLastName(?)}",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            cstmt.setString("lastNameStartsWith", lastNameStartsWith);
            boolean results = cstmt.execute();
            int rowsAffected = 0;

            // Protects against lack of SET NOCOUNT in stored prodedure
            while (results || rowsAffected != -1) {
                if (results) {
                    rs = cstmt.getResultSet();
                    break;
                } else {
                    rowsAffected = cstmt.getUpdateCount();
                }
                results = cstmt.getMoreResults();
            }
            while (rs.next()) {
                employeeFullName.add(
                        rs.getString("LastName") + ", "
                        + rs.getString("FirstName") + " "
                        + rs.getString("MiddleName"));
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return employeeFullName;
    }

Example 6: Converting a Result Set to Ordered Collection of Objects

In this last example, we pass two input parameters, product color and product size, to a slightly more complex stored procedure. The stored procedure returns a result set containing several columns of product information. This time, the example’s method iterates through the result set returned by the procedure and constructs an ordered collection of products, a List<Product> object. The Product objects in the list are instances of the Product.java POJO class. The method converts each column/row value (key/value pair) into a Product property (i.e. Product.SizeProduct.Model). This is a common pattern for persisting data from a result set in the application. The  Product.java class is part of the project code you can download.

CREATE PROCEDURE [Production].[uspGetProductsByColorAndSize]
    @productColor VARCHAR(20),
    @productSize INTEGER
AS
BEGIN
    SET NOCOUNT ON;

    SELECT p.Name AS [Product], p.ProductNumber, p.Color, p.Size, m.Name AS [Model]
    FROM Production.ProductModel AS m INNER JOIN
        Production.Product AS p ON m.ProductModelID = p.ProductModelID
    WHERE (p.Color = @productColor) AND (p.Size = @productSize)
    ORDER BY [Model], [Product]
END
GO

    /**
     * CallableStatement example, (2) input parameters, db returns ResultSet
     *
     * @param color
     * @param size
     * @return List<Product> of Product objects
     */
    public List<Product> getProductsByColorAndSizeCS(String color, String size) {

        CallableStatement cstmt = null;
        ResultSet rs = null;
        List<Product> productList = new ArrayList<>();

        try {
            cstmt = connection.getConnection().prepareCall(
                    "{call Production.uspGetProductsByColorAndSize(?, ?)}",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            cstmt.setString("productColor", color);
            cstmt.setString("productSize", size);
            boolean results = cstmt.execute();
            int rowsAffected = 0;

            // Protects against lack of SET NOCOUNT in stored prodedure
            while (results || rowsAffected != -1) {
                if (results) {
                    rs = cstmt.getResultSet();
                    break;
                } else {
                    rowsAffected = cstmt.getUpdateCount();
                }
                results = cstmt.getMoreResults();
            }

            while (rs.next()) {
                Product product = new Product(
                        rs.getString("Product"),
                        rs.getString("ProductNumber"),
                        rs.getString("Color"),
                        rs.getString("Size"),
                        rs.getString("Model"));
                productList.add(product);
            }
        } catch (Exception ex) {
            Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JdbcStoredProcsExample.class.getName()).log(
                            Level.WARNING, null, ex);
                }
            }
        }
        return productList;
    }

Schema Reference

You’ll notice in Example 4, I refer to the dbo schema ({call dbo.uspGetAverageProductWeightOUT(?)}). In Examples 3, I did not refer to the dbo schema. Since this stored procedure is part of the dbo schema and the dbo schema is also the default schema for this database, I did not have to reference it. However, according to Microsoft, it is always good practice to refer to database objects by a schema name and the object name, separated by a period. That includes the default schema. You must always refer to the schema if it is not the default schema.

Running the Examples

The application has a main method that runs all six examples. It will display the name of the method being called, the duration of time it took to retrieve the data, and the results returned by the method.

package com.articles.examples;

import java.util.List;

/**
 * Main class that calls all example methods
 *
 * @author Gary A. Stafford
 */
public class RunExamples {

    private static Examples examples = new Examples();
    private static ProcessTimer timer = new ProcessTimer();

    /**
     * @param args the command line arguments
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        System.out.println("");
        System.out.println("SQL SERVER STATEMENT EXAMPLES");
        System.out.println("======================================");

        // Statement example, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        double averageWeight = examples.getAverageProductWeightST();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightST");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // PreparedStatement example, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightPS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightPS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement, no parameters, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightCS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement example, (1) output parameter, db returns integer
        timer.setStartTime(System.nanoTime());
        averageWeight = examples.getAverageProductWeightOutCS();
        timer.setEndTime(System.nanoTime());
        System.out.println("GetAverageProductWeightOutCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Average product weight (lb): " + averageWeight);
        System.out.println("");

        // CallableStatement example, (1) input parameter, db returns ResultSet
        timer.setStartTime(System.nanoTime());
        String lastNameStartsWith = "Sa";
        List<String> employeeFullName =
                examples.getEmployeesByLastNameCS(lastNameStartsWith);
        timer.setEndTime(System.nanoTime());
        System.out.println("GetEmployeesByLastNameCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        System.out.println("Last names starting with '"
                + lastNameStartsWith + "': " + employeeFullName.size());
        if (employeeFullName.size() > 0) {
            System.out.println("Last employee found: "
                    + employeeFullName.get(employeeFullName.size() - 1));
        } else {
            System.out.println("No employees found with last name starting with '"
                    + lastNameStartsWith + "'");
        }
        System.out.println("");

        // CallableStatement example, (2) input parameters, db returns ResultSet
        timer.setStartTime(System.nanoTime());
        String color = "Red";
        String size = "44";
        List<Product> productList =
                examples.getProductsByColorAndSizeCS(color, size);
        timer.setEndTime(System.nanoTime());
        System.out.println("GetProductsByColorAndSizeCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        if (productList.size() > 0) {
            System.out.println("Products found (color: '" + color
                    + "', size: '" + size + "'): " + productList.size());
            System.out.println("First product: "
                    + productList.get(0).getProduct()
                    + " (" + productList.get(0).getProductNumber() + ")");
        } else {
            System.out.println("No products found with color '" + color
                    + "' and size '" + size + "'");
        }
        System.out.println("");

        examples.closeConnection();
    }
}
Results with Plans Cached
Results of Running the Application

SQL Statement Performance

Although this post is not about SQL performance, I’ve added a timer feature (ProcessTimer.java class) to capture the duration of time each example takes to return data, measured in milliseconds. The  ProcessTimer.java class is part of the project code you can download. You will observe significant differences between the first four examples, even though they all basically perform the same function and return the same information. The time differences are a result of several factors, primarily pre-compilation of the SQL statements and SQL Server plan caching. The effects of these two factors are easily demonstrated by clearing the SQL Server plan cache (see SQL script below) and then running the application twice in a row. The second time, pre-compilation and plan caching should result in significantly faster times for the prepared statements and callable statements, in Examples 2–6. Differences of 2x, and up to 80x can be observed in the actual output from my machine, below.

DBCC FREEPROCCACHE;
GO

CHECKPOINT;
GO

DBCC DROPCLEANBUFFERS;
GO

C:\Users\gstaffor>java -jar
"C:\Users\gstaffor\Documents\NetBeansProjects\
JdbcStoredProcsExample\dist\JdbcStoredProcsExample.jar"

SQL SERVER STATEMENT EXAMPLES
======================================
GetAverageProductWeightST
Duration (ms): 402
Average product weight (lb): 13.48

GetAverageProductWeightPS
Duration (ms): 26
Average product weight (lb): 13.48

GetAverageProductWeightCS
Duration (ms): 35
Average product weight (lb): 13.48

GetAverageProductWeightOutCS
Duration (ms): 1620
Average product weight (lb): 13.48

GetEmployeesByLastNameCS
Duration (ms): 1108
Last names starting with 'Sa': 7
Last employee found: Sandberg, Mikael Q

GetProductsByColorAndSizeCS
Duration (ms): 56
Products found (color: 'Red', size: '44'): 7
First product: HL Road Frame - Red, 44 (FR-R92R-44)

C:\Users\gstaffor>java -jar
"C:\Users\gstaffor\Documents\NetBeansProjects\
JdbcStoredProcsExample\dist\JdbcStoredProcsExample.jar"

SQL SERVER STATEMENT EXAMPLES
======================================
GetAverageProductWeightST
Duration (ms): 57
Average product weight (lb): 13.48

GetAverageProductWeightPS
Duration (ms): 14
Average product weight (lb): 13.48

GetAverageProductWeightCS
Duration (ms): 8
Average product weight (lb): 13.48

GetAverageProductWeightOutCS
Duration (ms): 20
Average product weight (lb): 13.48

GetEmployeesByLastNameCS
Duration (ms): 42
Last names starting with 'Sa': 7
Last employee found: Sandberg, Mikael Q

GetProductsByColorAndSizeCS
Duration (ms): 8
Products found (color: 'Red', size: '44'): 7
First product: HL Road Frame - Red, 44 (FR-R92R-44)

Conclusion

This post has demonstrated several methods for querying and calling stored procedures from a SQL Server database using JDBC with the Microsoft JDBC Driver 4.0 for SQL Server. Although the examples are quite simple, the same patterns can be used with more complex stored procedures, with multiple input and output parameters, which not only select, but insert, update, and delete data.

As with any non-Oracle database driver, there are some limitations of the Microsoft JDBC Driver 4.0 for SQL Server you should be aware of by reading the documentation. However, for most tasks that require database interaction, the driver provides adequate functionality with SQL Server. The best place to learn more about using the driver is the help files that come with the driver. The path to the help files, relative to the root of the driver’s parent folder is [path to folder]\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\help\default.htm.

, , , , , , , , , , , , , , ,

14 Comments

Automated Deployment to GlassFish Using Jenkins CI Server and Apache Ant

Use Jenkins and Apace Ant to compile, assemble, test, and deploy a RESTful web service to GlassFish. All source code for this post is available on GitHub. Note GitHub repo reflects updates to project on 10/31/2013.

Jenkins, formally Hudson, is the industry-standard, java-based open-source continuous integration server. According to their website, Jenkins provides over 400 plug-ins to support building and testing almost any type of project. According to Apache, Ant is a Java library and command-line tool whose mission is to drive processes described in build files as targets. This post demonstrates the use of Jenkins and Apache Ant to compile, assemble, unit test, and deploy a Java EE 6 RESTful web service to Oracle’s GlassFish open-source application server.

For the sake of brevity, I have chosen to use the HelloWorld RESTful web service example included with NetBeans. I will use NetBeans to create the project, write the unit tests, and produce an Ant target in the build file. I will not delve deeply into the inner workings of the web service itself since the focus of this post is automation.

System Configuration

This post assumes that you have current versions of NetBeans, JUnit, Jenkins, GlassFish, Ant and Java installed and configured on your Microsoft Windows-based computer. A full installation of NetBeans comes with JUnit, Ant, and GlassFish. At the time of the original post, I was using NetBeans 7.1.2, GlassFish 3.1.2, Jenkins 1.4.6.3, Ant 1.8.3, and JDK 1.7.0_02.

For simplicity, I am using a single development machine for this demonstration, on which all applications are installed. In a true production environment you would most likely have a distributed configuration with GlassFish installed on an application server, Jenkins on a build server, and NetBeans on your development machine. Also, for this post, I am also not using a source-code management (SCM) system, also called a version control system (VCS), such as Subversion or Mercurial, to house the project’s source code. Again, in a production environment, your source-code would be placed on SCM/VCS server.

Both GlassFish and Jenkins are configured by default to run on server port 8080. Since I have both applications installed on the same machine, I have changed Jenkins’ default port to another unused port, 9090. Changing Jenkins’ port is easy to do. If you don’t know how, consult this post or similar.

NetBeans

First, create a new project in NetBeans, by selecting the New Project -> Samples -> Java Web Services, REST: Hello World (Java EE 6), as shown below. Rename the project to HelloGlassFish. When complete, the project, in the Projects tab, should look like the screen-grab, below.

New Project View in NetBeans

New Project View in NetBeans

JUnit

Next create a unit-test using JUnit, the open-source unit-testing framework. Jenkins will eventually run this test each time the project is built. Creating unit-tests is easy in NetBeans. Select the ‘NameStorageBean.java’ class object, right-click, and select Tools -> Create JUnit Tests… This will create a default ‘NameStorageBeanTest.java’ class object in a new, ‘Test Packages’ directory. Overwrite NameStorageBeanTest.java contents with the follows code. This will create a single unit test we can use to demonstrate JUnit’s integration with Jenkins. You will also notice new test objects in the Project tab.

package helloworld;
import javax.ejb.embeddable.EJBContainer;
import javax.naming.NamingException;
import org.junit.*;
import static org.junit.Assert.assertEquals;
/**
*
* @author Gary A. Stafford
*/
public class NameStorageBeanTest {
private NameStorageBean instance = null;
private EJBContainer container = null;
public NameStorageBeanTest() {
}
@BeforeClass
public static void setUpClass() throws Exception {
}
@AfterClass
public static void tearDownClass() throws Exception {
}
@Before
public void setUp() throws NamingException {
container = javax.ejb.embeddable.EJBContainer.createEJBContainer();
instance = (NameStorageBean) container.getContext().
lookup("java:global/classes/NameStorageBean");
}
@After
public void tearDown() {
}
/**
* Test of getName method, of class NameStorageBean.
*/
@Test
public void testGetName() throws Exception {
System.out.println("getName");
String expResult = "Test";
instance.setName(expResult);
String result = instance.getName();
assertEquals(expResult, result);
container.close();
}
}

Build the project and run the ‘testGetName’ unit-test to make sure it works correctly and the test passes.

Apache Ant

Next, change to the Files tab. Open the ‘build.xml’ file, as shown below. Also, for later reference, note the contents of the ‘HelloGlassFish.war’ and the location of the ‘pwdfile_domain1’ password file.

New Project File View in NetBeans

Place the following Ant target, entitled ‘jenkins-glassfish-deploy’, into the build.xml file, between the end of the commented section and the closing <project/> tag, as shown below.

<!-- Older style. Not what is in repo on GitHub. -->
<target name="jenkins-glassfish-deploy"
description="Clean, build, test and deploy application to GlassFish">
<antcall target="clean">
<antcall target="default">
<antcall target="test">
<exec failonerror="true" executable="cmd" description="Deploy to GlassFish">
<arg value="/c" />
<arg value="asadmin --echo=true --host=localhost --port=4848 --user=admin
--passwordfile=pwdfile_domain1 --secure=false
deploy --force=true --name=HelloGlassFish --contextroot=/HelloGlassFish dist\HelloGlassFish.war" />
</exec>
</target>

This is the Ant target Jenkins will use to build, test, and deploy the project. The primary ‘jenkins-glassfish-deploy’ target calls three Ant targets using the antcall element. They include clean, default, and test. Each of these Ant targets has dependencies on other Ant targets, which in turn depend on yet other targets – a dependency tree. For example, default depends on dist and javadoc. The test target depends on other targets to build the .war file. If you are not using test to execute unit tests, you can call the test target to build the .war file.

The last part of the ‘jenkins-glassfish-deploy’ target is a little different. It’s an exec (execute) element, which calls asadmin to deploy the project to GlassFish with a series of GlassFish domain-specific parameters. These parameters include the GlassFish domain’s URL and port, the domain’s administrative user and password account info (found in a password file), the location of the .war file to deploy, and destination of the .war within GlassFish. Calling asadmin deploy gives you fine control over the details of how the project is deployed to GlassFish.

The password file, referenced in the target is a simple text file, which stores the password for the user account used to execute the asadmin deploy call. The contents of the file look like:

AS_ADMIN_PASSWORD=Your_Password_Here

This target could be simplified with the depends attribute. Instead of the three antcall elements, you could simply add depends="clean, default, test" to the target element:

<!-- Older style. Not what is in repo on GitHub. -->
<target name="jenkins-glassfish-deploy-updated" depends="clean, default, test"
description="Clean, build, test and deploy application to GlassFish">
<exec failonerror="true" executable="cmd" description="Deploy to GlassFish">
<arg value="/c" />
<arg value="asadmin --echo=true --host=localhost --port=4848 --user=admin
--passwordfile=pwdfile_domain1 --secure=false
deploy --force=true --name=HelloGlassFish --contextroot=/HelloGlassFish dist\HelloGlassFish.war" />
</exec>
</target>

According to Oracle, the asadmin utility is used to perform any administrative tasks for GlassFish from the command line. You can use this asadmin utility in place of using the GlassFish Administrator interface. I am able to call asadmin directly because I have added the path to asadmin.bat to the Windows’ environmental variable, PATH. The asadmin.bat file is in the GlassFish bin directory, similar to ‘C:\Program Files\glassfish-3.1.2\glassfish\bin\’.

Jenkins

Switching to Jenkins, create a new Job named HelloGlassFish. In the HelloGlassFish configuration, we need to add two Build steps and one post-build Action. For the first Build step, since we are not using SCM, we will copy the files from the project in the NetBeans workspace to the Jenkins workspace. To do this, add an ‘Execute Windows batch command’ action with code similar to code snippet below, but substituting your own project’s file path. Note, you can substitute the %WORKSPACE% environmental variable for the xcopy destination (see call-out 1 in the below screen-grab). This variable represents the absolute path of the directory assigned to the build as a workspace, according to Jenkins. Jenkins offers many useful variables, accessible to Windows batch scripts.

xcopy "C:\Users\gstaffor\Documents\NetBeansProjects\HelloGlassFish\HelloGlassFish" "%WORKSPACE%" /s /e /h /y

Next, add the second Build task, ‘Invoke Ant’. I assume you already have Ant configured for Jenkins. In the ‘Target’s text box, enter the Ant target we created in NetBeans build.xml file, entitled ‘jenkins-glassfish-deploy’ (see call-out 2 in the below screen-grab). If the name of your build file is anything other than the default ‘build.xml’, you will need to enter the Ant file name.

Lastly, add the single Post-build Action, ‘Publish JUnit test result report’. This will show us a visual representation of the results of our project’s unit-tests. Input the relative path to your reports from the workspace root. The path should be similar to call-out 3 in the screen-grab, below.

When complete, the HelloGlassFish Job’s configuration should resemble the screen-grab, below.

Jenkins HelloGlassFish Project Configuration

Jenkins HelloGlassFish Project Configuration

Save and close the configuration. Build the HelloGlassFish Job in Jenkins and make sure it succeeds with error.

GlassFish

Open GlassFish’s browser-based Domain Admin Console, usually on server port 4848, by default. On the left-hand side of the main window, under ‘Common Tasks’, tip the ‘Applications’ node. You should see the HelloGlassFish application is now deployed to GlassFish. You don’t have to do anything in GlassFish, Jenkins and Ant has taken care of everything.

GlassFish’s browser-based Domain Admin Console

GlassFish’s browser-based Domain Admin Console

To view the HelloGlassFish application, open a new browser window and direct it to ‘http://localhost:8080/HelloGlassFish/resources/helloWorld’. You should see a ‘Hello World!’ message displayed in your browser’s window. Note, since we only changed the name of the default HelloWorld NetBeans sample project to HelloGlassFish, not the web service’s URI, ‘helloWorld’ is still a required part of the URL path.

Redeploying the Project

Lastly, let’s demonstrate how easily changes to our project can be re-complied, re-tested, and re-deployed to GlassFish by Jenkins and Ant. Return to the HelloGlassFish project in NetBeans and open the NameStorageBean.java class. Change the value of the ‘name’ field from ‘World’ to ‘GlassFish’ and save the changes. Don’t build or do anything else in NetBeans. Instead, return to Jenkins and build the HelloGlassFish Job, again.

Change the NameStorageBean name Field

Change the NameStorageBean name Field

When the Job has finished building, re-direct your browser back to ‘http://localhost:8080/HelloGlassFish/resources/helloWorld’. You should now see a ‘Hello GlassFish!’ message displayed in your browser’s window instead of the earlier message, ‘Hello World!’. Jenkins has called the Ant target, which in turn re-compiled, re-tested, and re-deployed the modified HelloGlassFish application to GlassFish.

HelloGlassFish RESTful Web Service Demo

HelloGlassFish RESTful Web Service Demo

Helpful Links

, , , , , , , , , , , , , , , , ,

5 Comments

Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0

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.

SQL Server 2008 R2 Database Diagram

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

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

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 Default Database Schema

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.

Database Connections in NetBeans

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.

One notable change since Blaise’s posts is the addition of EclipseLink JAXB (MOXy) as a JAXB provider in the latest release of GlassFish 3.1.2. It is no longer necessary to add MOXy to GlassFish, as the blog series instructs for the earlier version of GlassFish 3.0.1.

, , , , , , , , , , , , , ,

8 Comments