Posts Tagged JDBC

Java Development with Microsoft SQL Server: Calling Microsoft SQL Server Stored Procedures from Java Applications Using JDBC

Posted by Gary A. Stafford in AWS, Java Development, Software Development, SQL, SQL Server Development on September 9, 2020

Introduction

Enterprise software solutions often combine multiple technology platforms. Accessing an Oracle database via a Microsoft .NET application and vice-versa, accessing Microsoft SQL Server from a Java-based application is common. In this post, we will explore the use of the JDBC (Java Database Connectivity) API to call stored procedures from a Microsoft SQL Server 2017 database and return data to a Java 11-based console application.

View of the post’s Java project from JetBrains’ IntelliJ IDE

The objectives of this post include:

Demonstrate the differences between using static SQL statements and stored procedures to return data.
Demonstrate 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 Procedures?

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

Optimization: Stored procedures are often written by DBAs or database 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.

AdventureWorks 2017 Database

For brevity, I will use an existing and well-known Microsoft SQL Server database, AdventureWorks. The AdventureWorks database was originally published by Microsoft for SQL Server 2008. Although a bit dated architecturally, the database comes prepopulated with plenty of data for demonstration purposes.

The `HumanResources` schema, one of five schemas within the AdventureWorks database

For the demonstration, I have created an Amazon RDS for SQL Server 2017 Express Edition instance on AWS. You have several options for deploying SQL Server, including AWS, Microsoft Azure, Google Cloud, or installed on your local workstation.

There are many methods to deploy the AdventureWorks database to Microsoft SQL Server. For this post’s demonstration, I used the AdventureWorks2017.bak backup file, which I copied to Amazon S3. Then, I enabled and configured the native backup and restore feature of Amazon RDS for SQL Server to import and install the backup.

DROP DATABASE IF EXISTS AdventureWorks;
GO

EXECUTE msdb.dbo.rds_restore_database
     @restore_db_name='AdventureWorks',
     @s3_arn_to_restore_from='arn:aws:s3:::my-bucket/AdventureWorks2017.bak',
     @type='FULL',
     @with_norecovery=0;

-- get task_id from output (e.g. 1)

EXECUTE msdb.dbo.rds_task_status
     @db_name='AdventureWorks',
     @task_id=1;

Install Stored Procedures

For the demonstration, I have added four stored procedures to the AdventureWorks database to use in this post. To follow along, you will need to install these stored procedures, which are included in the GitHub project.

View of the new stored procedures from JetBrains’ IntelliJ IDE Database tab

Data Sources, Connections, and Properties

Using the latest Microsoft JDBC Driver 8.4 for SQL Server (ver. 8.4.1.jre11), 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 JDBC 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, which reads values from a configuration properties file, config.properties. On startup, the application creates the database connection, calls each of the example methods, and then closes the connection.

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 the data source and connection code in the article. Again, a complete copy of all the code for this article is available on GitHub, including Java source code, SQL statements, helper SQL scripts, and a set of basic JUnit tests.

To run the JUnit unit tests, using Gradle, which the project is based on, use the ./gradlew cleanTest test --warning-mode none command.

To build and run the application, using Gradle, which the project is based on, use the ./gradlew run --warning-mode none command.

The output of the Java console application

Example 1: SQL Statement

Before jumping into stored procedures, we will start with a simple static SQL statement. This example’s method, getAverageProductWeightST, uses the java.sql.Statement class. According to Oracle’s JDBC 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 AdventureWorks database. It returns a solitary double numeric value. This example demonstrates one of the simplest methods for returning data from SQL Server.

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

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

Example 2: Prepared Statement

Next, we will execute almost the same static SQL statement as in Example 1. The only change is the addition of the column name, 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.

Also, 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 execute this statement multiple times efficiently.

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

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

Example 3: 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. 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 double numeric value.

CREATE OR
ALTER PROCEDURE [Production].[uspGetAverageProductWeight]
AS
BEGIN
  SET NOCOUNT ON;

WITH
    Weights_CTE(AverageWeight)
    AS
    (
        SELECT [Weight] AS [AverageWeight]
        FROM [Production].[Product]
        WHERE [Weight] > 0
          AND [WeightUnitMeasureCode] = 'LB'
      UNION
        SELECT [Weight] * 0.00220462262185 AS [AverageWeight]
        FROM [Production].[Product]
        WHERE [Weight] > 0
          AND [WeightUnitMeasureCode] = 'G'
    )
  SELECT ROUND(AVG([AverageWeight]), 2)
  FROM [Weights_CTE];
END

GO

The calling Java method is shown below.

/**
 * CallableStatement, no parameters, 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 [Production].[uspGetAverageProductWeight]}");
        cstmt.execute();
        rs = cstmt.getResultSet();
        if (rs.next()) {
            averageWeight = rs.getDouble(1);
        }
    } catch (Exception ex) {
        Logger.getLogger(RunExamples.class.getName()).
                log(Level.SEVERE, null, ex);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(RunExamples.class.getName()).
                        log(Level.SEVERE, null, ex);
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(RunExamples.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 OR
ALTER PROCEDURE [Production].[uspGetAverageProductWeightOUT]@averageWeight DECIMAL(8, 2) OUT
AS
BEGIN
  SET NOCOUNT ON;

WITH
    Weights_CTE(AverageWeight)
    AS
    (
        SELECT [Weight] AS [AverageWeight]
        FROM [Production].[Product]
        WHERE [Weight] > 0
          AND [WeightUnitMeasureCode] = 'LB'
      UNION
        SELECT [Weight] * 0.00220462262185 AS [AverageWeight]
        FROM [Production].[Product]
        WHERE [Weight] > 0
          AND [WeightUnitMeasureCode] = 'G'
    )
  SELECT @averageWeight = ROUND(AVG([AverageWeight]), 2)
  FROM [Weights_CTE];
END

GO

The calling Java method is shown below.

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

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

Example 5: Calling a Stored Procedure with an Input Parameter

In this example, the procedure returns a result set, java.sql.ResultSet, of employees whose last name starts with a particular sequence of characters (e.g., ‘M’ or ‘Sa’). The sequence of characters is passed as an input parameter, lastNameStartsWith, to the stored procedure using the CallableStatement.

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 OR
ALTER PROCEDURE [HumanResources].[uspGetEmployeesByLastName]
@lastNameStartsWith VARCHAR(20) = 'A'
AS
BEGIN
  SET NOCOUNT ON;

SELECT p.[FirstName], p.[MiddleName], p.[LastName], p.[Suffix], e.[JobTitle], m.[EmailAddress]
  FROM [HumanResources].[Employee] AS e
    LEFT JOIN [Person].[Person] p ON e.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT JOIN [Person].[EmailAddress] m ON e.[BusinessEntityID] = m.[BusinessEntityID]
  WHERE e.[CurrentFlag] = 1
    AND p.[PersonType] = 'EM'
    AND p.[LastName] LIKE @lastNameStartsWith + '%'
  ORDER BY p.[LastName], p.[FirstName], p.[MiddleName]
END

GO

The calling Java method is shown below.

/**
 * CallableStatement example, (1) input parameter, returns ResultSet
 *
 * @param lastNameStartsWith
 * @return List 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 procedure
        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(RunExamples.class.getName()).
                log(Level.SEVERE, null, ex);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(RunExamples.class.getName()).
                        log(Level.WARNING, null, ex);
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(RunExamples.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, productColor and productSize, 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, List<Product> object. The Product objects in the list are instances of the Product.java POJO class. The method converts each results set’s row’s field value into a Product property (e.g., Product.Size, Product.Model). Using a collection is a common method for persisting data from a result set in an application.

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

SELECT p.[ProductNumber], m.[Name] AS [Model], p.[Name] AS [Product], p.[Color], p.[Size]
  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 p.[ProductNumber], [Model], [Product]
END

GO

The calling Java method is shown below.

/**
 * CallableStatement example, (2) input parameters, returns ResultSet
 *
 * @param color
 * @param size
 * @return List 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 procedure
        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(RunExamples.class.getName()).
                log(Level.SEVERE, null, ex);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(RunExamples.class.getName()).
                        log(Level.WARNING, null, ex);
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(RunExamples.class.getName()).
                        log(Level.WARNING, null, ex);
            }
        }
    }
    return productList;
}

Proper T-SQL: Schema Reference and Brackets

You will notice in all T-SQL statements, I refer to the schema as well as the table or stored procedure name (e.g., {call [Production].[uspGetAverageProductWeightOUT](?)}). 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 even includes the default schema (e.g., dbo).

You will also notice I wrap the schema and object names in square brackets (e.g., SELECT [ProductNumber] FROM [Production].[ProductModel]). The square brackets are to indicate that the name represents an object and not a reserved word (e.g, CURRENT or NATIONAL). By default, SQL Server adds these to make sure the scripts it generates run correctly.

Running the Examples

The application will display the name of the method being called, a description, the duration of time it took to retrieve the data, and the results returned by the method.

	package com.article.examples;

	import java.util.List;

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

	private static final Examples examples = new Examples();
	private static final 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, returns Integer
	timer.setStartTime(System.nanoTime());
	double averageWeight = examples.getAverageProductWeightST();
	timer.setEndTime(System.nanoTime());
	System.out.println("Method: GetAverageProductWeightST");
	System.out.println("Description: Statement, no parameters, returns Integer");
	System.out.printf("Duration (ms): %d%n", timer.getDuration());
	System.out.printf("Results: Average product weight (lb): %s%n", averageWeight);
	System.out.println("—");

	// PreparedStatement example, no parameters, returns Integer
	timer.setStartTime(System.nanoTime());
	averageWeight = examples.getAverageProductWeightPS();
	timer.setEndTime(System.nanoTime());
	System.out.println("Method: GetAverageProductWeightPS");
	System.out.println("Description: PreparedStatement, no parameters, returns Integer");
	System.out.printf("Duration (ms): %d%n", timer.getDuration());
	System.out.printf("Results: Average product weight (lb): %s%n", averageWeight);
	System.out.println("—");

	// CallableStatement, no parameters, returns Integer
	timer.setStartTime(System.nanoTime());
	averageWeight = examples.getAverageProductWeightCS();
	timer.setEndTime(System.nanoTime());
	System.out.println("Method: GetAverageProductWeightCS");
	System.out.println("Description: CallableStatement, no parameters, returns Integer");
	System.out.printf("Duration (ms): %d%n", timer.getDuration());
	System.out.println("—");

	// CallableStatement example, (1) output parameter, returns Integer
	timer.setStartTime(System.nanoTime());
	averageWeight = examples.getAverageProductWeightOutCS();
	timer.setEndTime(System.nanoTime());
	System.out.println("Method: GetAverageProductWeightOutCS");
	System.out.println("Description: CallableStatement, (1) output parameter, returns Integer");
	System.out.printf("Duration (ms): %d%n", timer.getDuration());
	System.out.printf("Results: Average product weight (lb): %s%n", averageWeight);
	System.out.println("—");

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

	// CallableStatement example, (2) input parameters, 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("Method: GetProductsByColorAndSizeCS");
	System.out.println("Description: CallableStatement, (2) input parameter, returns ResultSet");
	System.out.printf("Duration (ms): %d%n", timer.getDuration());
	if (productList.size() > 0) {
	System.out.printf("Results: Products found (color: '%s', size: '%s'): %d%n", color, size, productList.size());
	System.out.printf(" First product: %s (%s)%n", productList.get(0).getProduct(), productList.get(0).getProductNumber());
	} else {
	System.out.printf("No products found with color '%s' and size '%s'%n", color, size);
	}
	System.out.println("—");

	examples.closeConnection();
	}
	}

view raw RunExamples.java hosted with ❤ by GitHub

Below, we see the results.

SQL Statement Performance

This post is certainly not about SQL performance, demonstrated by the fact I am only using Amazon RDS for SQL Server 2017 Express Edition on a single, very underpowered db.t2.micro Amazon RDS instance types. However, I have 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. Using the timer, you should observe significant differences between the first run and proceeding runs of the application for several of the called methods. The time difference is 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) using DBCC (Database Console Commands) statements. 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. In the two random runs shown below, we see up to a 497% improvement in query time.

USE AdventureWorks;

DBCC FREESYSTEMCACHE('SQL Plans');
GO

CHECKPOINT;
GO

-- Impossible to run with Amazon RDS for Microsoft SQL Server on AWS
-- DBCC DROPCLEANBUFFERS;
-- GO

The first run results are shown below.

SQL SERVER STATEMENT EXAMPLES
======================================
Method: GetAverageProductWeightST
Description: Statement, no parameters, returns Integer
Duration (ms): 122
Results: Average product weight (lb): 12.43
---
Method: GetAverageProductWeightPS
Description: PreparedStatement, no parameters, returns Integer
Duration (ms): 146
Results: Average product weight (lb): 12.43
---
Method: GetAverageProductWeightCS
Description: CallableStatement, no parameters, returns Integer
Duration (ms): 72
Results: Average product weight (lb): 12.43
---
Method: GetAverageProductWeightOutCS
Description: CallableStatement, (1) output parameter, returns Integer
Duration (ms): 623
Results: Average product weight (lb): 12.43
---
Method: GetEmployeesByLastNameCS
Description: CallableStatement, (1) input parameter, returns ResultSet
Duration (ms): 830
Results: Last names starting with 'Sa': 7
         Last employee found: Sandberg, Mikael Q
---
Method: GetProductsByColorAndSizeCS
Description: CallableStatement, (2) input parameter, returns ResultSet
Duration (ms): 427
Results: Products found (color: 'Red', size: '44'): 7
         First product: Road-650 Red, 44 (BK-R50R-44)
---

The second run results are shown below.

SQL SERVER STATEMENT EXAMPLES
======================================
Method: GetAverageProductWeightST
Description: Statement, no parameters, returns Integer
Duration (ms): 116
Results: Average product weight (lb): 12.43
---
Method: GetAverageProductWeightPS
Description: PreparedStatement, no parameters, returns Integer
Duration (ms): 89
Results: Average product weight (lb): 12.43
---
Method: GetAverageProductWeightCS
Description: CallableStatement, no parameters, returns Integer
Duration (ms): 80
Results: Average product weight (lb): 12.43
---
Method: GetAverageProductWeightOutCS
Description: CallableStatement, (1) output parameter, returns Integer
Duration (ms): 340
Results: Average product weight (lb): 12.43
---
Method: GetEmployeesByLastNameCS
Description: CallableStatement, (1) input parameter, returns ResultSet
Duration (ms): 139
Results: Last names starting with 'Sa': 7
         Last employee found: Sandberg, Mikael Q
---
Method: GetProductsByColorAndSizeCS
Description: CallableStatement, (2) input parameter, returns ResultSet
Duration (ms): 208
Results: Products found (color: 'Red', size: '44'): 7
         First product: Road-650 Red, 44 (BK-R50R-44)
---

Conclusion

This post has demonstrated several methods for querying and calling stored procedures from a SQL Server 2017 database using JDBC with the Microsoft JDBC Driver 8.4 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.

There are some limitations of the Microsoft JDBC Driver 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.

This blog represents my own viewpoints and not of my employer, Amazon Web Services.

Database, Java, JDBC, Microsoft SQL Server, MSSQL

Java RESTful Web Services Using MySQL Server, EclipseLink, and Jersey

Posted by Gary A. Stafford in Client-Side Development, Java Development on April 12, 2013

Demonstrates the development of Java RESTful Web Services using MySQL Server, EclipseLink (JPA) and Jersey (JAX-RS). Built using NetBeans and hosted on GlassFish. Both the Java Library and RESTful Service NetBeans’ projects, demonstrated in this post, are now available on GitHub.

Introduction

When implementing a Relational Database Management System (RDBMS), many enterprise software developers tend to favor Oracle 11g or Microsoft SQL Server relational databases, depending on their technology stack. However, there are several excellent alternative relational databases, including MySQL. In fact, MySQL is the world’s most popular open source database software, according to Oracle.

MySQL is available on over 20 platforms and operating systems including Linux, Unix, Mac and Windows, according to the MySQL website. Like Oracle and Microsoft’s flagship RDBMS, MySQL Server comes in at least four flavors, ranging from the free Community Edition, demonstrated here, to a full-featured, enterprise-level Cluster Carrier Grade Edition. Support for MySQL, like Oracle and Microsoft, extends beyond just technical support. MySQL provides JDBC, ODBC, .NET drivers for Java and .NET development, as well as other languages. MySQL is supported by many popular IDE’s, including MySQL’s own RDBMS IDE, MySQL Workbench. Lastly, like Oracle and Microsoft, MySQL provides extensive documentation, tutorials, and even sample databases, built using recommended architectural patterns.

In this post, we will use JDBC to map JPA entity classes to tables and views within a MySQL database. We will then build RESTful web services, EJB classes, which communicate with MySQL through the entities. We will separate the JPA entities into a Java Class Library. The class library will be referenced by the RESTful web services. The RESTful web services, part of a Java Web Application, will be deployed to GlassFish, where they are accessed with HTTP methods and tested.

Installation and Configuration

If you’ve worked with Microsoft SQL Server or particularly Oracle 11g, you’ll have a minimal learning curve with MySQL. Basic installation, configuration, and integration within your Java applications is like Oracle and Microsoft. Start by downloading and installing the latest versions of MySQL Server, MySQL Workbench, MySQL JDBC Connector/J Driver, and MySQL Sakila sample database. If on Linux, you could use the command line, or a native application management application, like Synaptic Package Manager, to perform most of the installations. To get the latest software and installation and configuration recommendations, I prefer to download and install them myself from the MySQL web site. All links are included at the end of this post.

For reference when following this post, I have installed MySQL Server 5.5.x on 64-bit Ubuntu 12.10 LTS, running within a Windows version of Oracle VM VirtualBox. I will be using the latest Linux version of NetBeans IDE 7.3 to develop the demonstration project. I will host the project on Oracle’s GlassFish Open Source Application Server 3.1.2.2, running on Ubuntu. Lastly, I will be referring to the latest JDK 1.7, in NetBeans, for the project.

MySQL Demo User Account

Once MySQL is installed and running, I suggest adding a new MySQL demo user account, to the Sakila database for this demonstration, using MySQL Workbench. For security, you should limit the user account to just those permissions necessary for this demonstration, as detailed in the following screen-grabs. You can also add the user from the command line, if you are familiar with administering MySQL in that way.

MySQL Workbench IDE

Configuring Demo User Login

Configuring Demo User Administrative Roles

Configuring Demo User Account Limits

Configuring Demo User Schema Privileges

New MySQL Database Connection

To begin development in NetBeans, first create a new JDBC database connection to the MySQL Sakila database. In the Services tab, right-click on the Databases item and select New Connection… Use the new demo user account for the connection.

Note in the first screen-grab below, that instead of using the default NetBeans JDBC MySQL Connector/J driver version, I have downloaded and replaced it with the most current version, 5.1.24. This is not necessary, but I like to use the latest drivers to avoid problems.

Locating the Driver in the New Connection Wizard

Make sure to test your connection before finishing, using the ‘Test’ button. It’s frustrating to track down database connection issues once you start coding and testing.

New Connection Wizard - Customize Connection

Customize the Connection in the New Connection Wizard

Sakila Database Doesn’t Contain Additional Schema

Choosing a Name for the Connection

New Database Connection for MySQL Sakila Database

New Java Class Library

Similar to an earlier post, create new Java Class Library project in NetBeans. Select New Project -> Java -> Java Class Library. This library will eventually contain the JPA entity classes, mapped to tables and views in the MySQL Sakila database. Following standard n-tier design principles, I prefer separate the data access layer (DAL) from the service layer. You can then reuse the data access layer for other types of data-consumers, such as SOAP-based services.

Create New Java Class Library Project

Naming New Java Class Library

Entity Classes from Database

Next, we will add entity classes to our project, mapped to several of the MySQL Sakila database’s tables and views. Right-click on the project and select New -> Entity Classes from Database… In the next window, choose the database connection we made before. NetBeans will then load all the available tables and views from the Sakila database. Next, select ‘actor_info(view)’, ‘film_actor’, and ‘film_list(view)’. Three related tables will also be added automatically by NetBeans. Not the warning at the bottom of the window about the need to specify Entity IDs. We will address this next.

Choosing Database Tables and Views

Entity Class Options

Entity Mapping Options

When selecting ‘Entity Classes from Database…’, NetBeans adds the ‘EclipseLink (JPA 2.0)’ global library to the project. This library contains three jars, including EclipseLink 2.3.x, Java Persistence API (JPA) 2.0.x, and state model API for JPQL queries. There is a newer EclipseLink 2.4.x library available from their web site. The 2.4.x version has many new features. You can download and replace NetBeans’ EclipseLink (JPA 2.0) library by creating a new EclipseLink 2.4.x library, if you want to give its new features, like JPA-RS, a try. It is not necessary for this demonstration, however.

Java Class Project with JPA Entity Classes

Adding Entity IDs to Views

To eliminate warnings displayed when we built the entities, Entity ID’s must be designated for the two database views we selected, ‘actor_info(view)’ and ‘film_list(view)’. Database views (virtual tables), do not have a primary key defined, which NetBeans requires for the entity classes. NetBeans will guide you through adding the ID, if you click on the error icon shown below.

Adding and Entity ID to ActorInfo Entity

Entity ID Added to Entity Class

ActorInfo.java Entity Class contents:

	package com.mysql.entities;

	import java.io.Serializable;
	import javax.persistence.Basic;
	import javax.persistence.Column;
	import javax.persistence.Entity;
	import javax.persistence.Id;
	import javax.persistence.Lob;
	import javax.persistence.NamedQueries;
	import javax.persistence.NamedQuery;
	import javax.persistence.Table;
	import javax.xml.bind.annotation.XmlRootElement;

	@Entity
	@Table(name = "actor_info")
	@XmlRootElement
	@NamedQueries({
	@NamedQuery(name = "ActorInfo.findAll", query = "SELECT a FROM ActorInfo a"),
	@NamedQuery(name = "ActorInfo.findByActorId", query = "SELECT a FROM ActorInfo a WHERE a.actorId = :actorId"),
	@NamedQuery(name = "ActorInfo.findByFirstName", query = "SELECT a FROM ActorInfo a WHERE a.firstName = :firstName"),
	@NamedQuery(name = "ActorInfo.findByLastName", query = "SELECT a FROM ActorInfo a WHERE a.lastName = :lastName")})
	public class ActorInfo implements Serializable {

	private static final long serialVersionUID = 1L;
	@Basic(optional = false)
	@Column(name = "actor_id")
	@Id
	private short actorId;
	@Basic(optional = false)
	@Column(name = "first_name")
	private String firstName;
	@Basic(optional = false)
	@Column(name = "last_name")
	private String lastName;
	@Lob
	@Column(name = "film_info")
	private String filmInfo;

	public ActorInfo() {
	}

	public short getActorId() {
	return actorId;
	}

	public void setActorId(short actorId) {
	this.actorId = actorId;
	}

	public String getFirstName() {
	return firstName;
	}

	public void setFirstName(String firstName) {
	this.firstName = firstName;
	}

	public String getLastName() {
	return lastName;
	}

	public void setLastName(String lastName) {
	this.lastName = lastName;
	}

	public String getFilmInfo() {
	return filmInfo;
	}

	public void setFilmInfo(String filmInfo) {
	this.filmInfo = filmInfo;
	}
	}

view raw ActorInfo.java hosted with ❤ by GitHub

New Java Web Application

Next, we will create the RESTful Web Services. Each service will be mapped to one of the corresponding JPA entity we just created in the Java class library project. Select New Project -> Java Web -> Web Application.

New Web Application Project

Naming New Web Application

Configuring Server and Settings

Configuring Frameworks

New Java Web Application Project

RESTful Web Services from Entity Classes

Before we will build the RESTful web services, we need to add a reference to the previous Java class library project, containing the JPA entity classes. In the Java web application’s properties dialog window, under Categories -> Libraries -> Compile, add a link to the Java class library project’s .jar file.

Adding MySQL Entity Class Library

Next, right-click on the project and select New -> RESTful Web Services from Entity Classes…

Adding RESTful Web Service from Entities

Adding RESTful Web Service from Entity Classes

In the preceding dialogue window, add all the ‘Available Entity Classes’ to the ‘Selected Entity Classes’ column.

Choosing Entity Classes

Chosen Entity Classes

After clicking next, you will prompted to configure the Persistence Unit and the Persistence Unit’s Data Source. Please refer to my earlier post for more information on the Persistence Unit. This data source will also be used by GlassFish, once the project is deployed, to connect to the Sakila MySQL database. The Persistence Unit will use the JNDI name to reference the data source.

Creating Data Source for Persistence Unit

Creating Data Source and JNDI Name

Creating Persistence Unit

Persistence Unit (persistence.xml) contents:

	<?xml version="1.0" encoding="UTF-8"?>
	<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
	<persistence-unit name="MySQLDemoServicePU" transaction-type="JTA">
	<jta-data-source>jdbc/mysql_sakila</jta-data-source>
	<class>com.mysql.entities.Actor</class>
	<class>com.mysql.entities.ActorInfo</class>
	<class>com.mysql.entities.Film</class>
	<class>com.mysql.entities.FilmActor</class>
	<class>com.mysql.entities.Language</class>
	<exclude-unlisted-classes>true</exclude-unlisted-classes>
	<properties/>
	</persistence-unit>
	</persistence>

view raw persistence.xml hosted with ❤ by GitHub

Generating Classes Using Jersey Options

Java Web Application with RESTful Web Services

As part of constructing the RESTful Web Services, notice NetBeans has added several Jersey (JAX-RS) libraries to the project. These libraries also reference Jackson (JSON Processor), Jettison (JSON StAX), MOXy (JAXB), and Grizzly (NIO) APIs.

Libraries Loaded by NetBeans to Java Web Application

Creating RESTful Web Services Test

Finally, we will test the RESTful Web Services, and indirectly the underlying entity classes mapped to the MySQL Sakila database. NetBeans makes this easy. To begin, right-click on the ‘RESTful Web Services’ folder in the Java web application project and select ‘Test RESTful Web Services’. NetBeans will automatically generate all the necessary files and links to test each of the RESTful web services’ operations.

As part of creating the tests, NetBeans will deploy the web application to GlassFish. When configuring the tests in the ‘Configure REST Test Client’ dialog window, make sure to use the second option, ‘Web Test Client in Project’. The first option only works with Microsoft’s Internet Explorer, an odd choice for a Java-based application running on Linux.

Configuring the REST Test Client

Highlighted below in red are the components NetBeans will install on the GlassFish application server. They include the RESTful web services application, a .war file. Each of the RESTful web service are Stateless Session Beans, installed as part of the application. In deployment also includes a JDBC Resource and a JDBC Connection Pool, which connects the application to the MySQL Sakila database. The Resource is automatically associated with the Connection Pool.

RESTful Web Services Deployed to GlassFish Server

After creating the necessary files and deploying the application, NetBeans will open a web browser. allowing you can test the services. Each of the RESTful web services is available to test by clicking on the links in the left-hand navigation menu. NetBeans has generated a few default operations, including ‘{id}’, ‘{from/to}’, and ‘count’, each mapped to separate methods in the service classes. Also notice you can choose to display the results of the service calls in multiple formats, including XML, JSON, and plain text.

Testing RESTful Web Services from NetBeans Using Chrome

We can also test the RESTful Web Services by calling the service URLs, directly. Below, is the results of a my call to the Actor service’s URL, from a separate Windows client machine.

Calling the RESTful Web Services Directly

You can also use applications like Fiddler, cURL, Firefox with Firebug, and Google Chrome’s Advanced REST Client and REST Console to test the services. Below, I used Fiddler to call the Actor service, again. Note the response contains a JSON payload, not XML. With Jersey, you can request and receive JSON from the services without additional programming.

Fiddler2 Request Example

Conclusion

Using these services, you can build any number of server-side and client-side data-driven applications. The service layer is platform agnostic, accessible from any web-browser, mobile device, or native desktop application, on Windows, Linux, and Apple.

Links

MySQL Server: http://www.mysql.com/downloads/mysql

MySQL Connector/J JDBC driver for MySQL: http://dev.mysql.com/downloads/connector/j

MySQL Workbench: http://www.mysql.com/downloads/workbench

MySQL Sakila Sample Database: http://dev.mysql.com/doc/sakila/en/sakila-installation.html

NetBeans IDE: http://www.netbeans.org

EclipseLink: http://projects.eclipse.org/projects/rt.eclipselink

Data Souce, EclipseLink, GlassFish, jackson, Java, JAVA EE, JAX-RS, JDBC, jersey, JNDI, JPA, JSON, mySQL, NetBeans, Oracle

10 Comments

RESTful Mobile: Consuming Java EE RESTful Web Services Using jQuery Mobile

Posted by Gary A. Stafford in Java Development, Mobile HTML Development, Software Development on October 18, 2012

Use jQuery Mobile to build a mobile HTML website, capable of calling Jersey-specific Java EE RESTful web services and displaying JSONP in a mobile web browser.

Both NetBeans projects used in this post are 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.

Background

In the previous two-part series, Returning JSONP from Java EE RESTful Web Services Using jQuery, Jersey, and GlassFish, we created a Jersey-specific RESTful web service from a database using EclipseLink (JPA 2.0 Reference Implementation), Jersey (JAX-RS Reference Implementation), JAXB, and Jackson Java JSON-processor. The service and associated entity class mapped to a copy of Microsoft SQL Server’s Adventure Works database. An HTML and jQuery-based client called the service, which returned a JSONP response payload. The JSON data it contained was formatted and displayed in a simple HTML table, in a web-browser.

Objectives

In this post, we will extend the previous example to the mobile platform. Using jQuery and jQuery Mobile JavaScript libraries, we will call two RESTful web services and display the resulting JSONP data using the common list/detail UX design pattern. We will display a list of Adventure Works employees. When the end-user clicks on an employee in the web-browser, a new page will display detailed demographic information about that employee.

Similar to the previous post, when the client website is accessed by the end-user in a mobile web browser, the client site’s HTML, CSS, and JavaScript files are downloaded and cached on the end-users machine. The JavaScript file, using jQuery and Ajax, makes a call to the RESTful web service, which returns JSON (or, JSONP in this case). This simulates a typical cross-domain situation where a client 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.

We will extend both the ‘JerseyRESTfulServices’ and ‘JerseyRESTfulClient’ projects we built in the last series of posts. Here are the high-level steps we will walk-through in this post:

Create a second view (virtual table) in the Adventure Works database;
Create a second entity class that maps to the new database view;
Modify the existing entity class, adding JAXB and Jackson JSON annotations;
Create a second Jersey-specific RESTful web service from the new entity using Jersey and Jackson;
Modify the existing Jersey-specific RESTful web service, adding one new methods;
Modify the web.xml file to allow us to use natural JSON notation;
Implement a JAXBContext resolver to serialize the JSON using natural JSON notation;
Create a simple list/detail two-page mobile HTML5 website using jQuery Mobile;
Use jQuery, Ajax, and CSS to call, parse, and display the JSONP returned by the service.

RESTful Web Services Project

When we are done, the final RESTful web services projects will look like the screen-grab, below. It will contain (2) entity classes, (2) RESTful web service classes, (1) JAXBContext resolver class, and the web.xml configuration file:

JerseyRESTfulServices Project View in NetBeans

1: Create the Second Database View
Create a new database view, vEmployeeNames, in the Adventure Works database:

USE [AdventureWorks]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [HumanResources].[vEmployeeNames]
AS
SELECT TOP (100) PERCENT BusinessEntityID, REPLACE(RTRIM(LastName 
     + COALESCE (' ' + Suffix + '', N'') + COALESCE (', ' + FirstName + ' ', N'') 
     + COALESCE (MiddleName + ' ', N'')), '  ', ' ') AS FullName
FROM Person.Person
WHERE (PersonType = 'EM')
ORDER BY FullName
GO

2: Create the Second Entity
Add the new VEmployeeNames.java entity class, mapped to the vEmployeeNames database view, using NetBeans’ ‘Entity Classes from Database…’ wizard. Then, modify the class to match the code below.

package entities;

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlType;

@Entity
@Table(name = "vEmployeeNames", catalog = "AdventureWorks", schema = "HumanResources")
@XmlRootElement(name = "vEmployeeNames")
@NamedQueries({
    @NamedQuery(name = "VEmployeeNames.findAll", query = "SELECT v FROM VEmployeeNames v"),
    @NamedQuery(name = "VEmployeeNames.findByBusinessEntityID", query = "SELECT v FROM VEmployeeNames v WHERE v.businessEntityID = :businessEntityID"),
    @NamedQuery(name = "VEmployeeNames.findByFullName", query = "SELECT v FROM VEmployeeNames v WHERE v.fullName = :fullName")})
public class VEmployeeNames implements Serializable {

    private static final long serialVersionUID = 1L;
    @Basic(optional = false)
    @NotNull
    @Id
    @Column(name = "BusinessEntityID")
    private int businessEntityID;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 102)
    @Column(name = "FullName")
    private String fullName;

    public VEmployeeNames() {
    }

    public int getBusinessEntityID() {
        return businessEntityID;
    }

    public void setBusinessEntityID(int businessEntityID) {
        this.businessEntityID = businessEntityID;
    }

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }
}

3: Modify the Existing Entity
Modify the existing VEmployee.java entity class to use JAXB and Jackson JSON Annotations as shown below (class code abridged). Note the addition of the @XmlType(propOrder = { "businessEntityID"... }) to the class, the @JsonProperty(value = ...) tags to each member variable, and the @Id tag to the businessEntityID, which serves as the entity’s primary key. We will see the advantages of the first two annotations later in the post when we return the JSON to the client.

package entities;

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlType;
import org.codehaus.jackson.annotate.JsonProperty;

@Entity
@Table(name = "vEmployee", catalog = "AdventureWorks", schema = "HumanResources")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "VEmployee.findAll", query = "SELECT v FROM VEmployee v"),
    ...})
    @XmlType(propOrder = {
    "businessEntityID",
    "title",
    "firstName",
    "middleName",
    "lastName",
    "suffix",
    "jobTitle",
    "phoneNumberType",
    "phoneNumber",
    "emailAddress",
    "emailPromotion",
    "addressLine1",
    "addressLine2",
    "city",
    "stateProvinceName",
    "postalCode",
    "countryRegionName",
    "additionalContactInfo"
})
public class VEmployee implements Serializable {

    private static final long serialVersionUID = 1L;
    @Basic(optional = false)
    @NotNull
    @Id
    @JsonProperty(value = "Employee ID")
    private int businessEntityID;
    @Size(max = 8)
    @JsonProperty(value = "Title")
    private String title;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 50)
    @JsonProperty(value = "First Name")
    private String firstName;
    @Size(max = 50)
    @JsonProperty(value = "Middle Name")
    private String middleName;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 50)
    @JsonProperty(value = "Last Name")
    private String lastName;
    @Size(max = 10)
    @JsonProperty(value = "Suffix")
    private String suffix;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 50)
    @JsonProperty(value = "Job Title")
    private String jobTitle;
    @Size(max = 25)
    @JsonProperty(value = "Phone Number")
    private String phoneNumber;
    @Size(max = 50)
    @JsonProperty(value = "Phone Number Type")
    private String phoneNumberType;
    @Size(max = 50)
    @JsonProperty(value = "Email Address")
    private String emailAddress;
    @Basic(optional = false)
    @NotNull
    @JsonProperty(value = "Email Promotion")
    private int emailPromotion;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 60)
    @JsonProperty(value = "Address Line 1")
    private String addressLine1;
    @Size(max = 60)
    @JsonProperty(value = "Address Line 2")
    private String addressLine2;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 30)
    @JsonProperty(value = "City")
    private String city;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 50)
    @JsonProperty(value = "State or Province Name")
    private String stateProvinceName;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 15)
    @JsonProperty(value = "Postal Code")
    private String postalCode;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 50)
    @JsonProperty(value = "Country or Region Name")
    private String countryRegionName;
    @Size(max = 2147483647)
    @JsonProperty(value = "Additional Contact Info")
    private String additionalContactInfo;

    public VEmployee() {
    }
    ...
}

4: Create the New RESTful Web Service
Add the new VEmployeeNamesFacadeREST.java RESTful web service class using NetBean’s ‘RESTful Web Services from Entity Classes…’ wizard. Then, modify the new class, adding the new findAllJSONP() method shown below (class code abridged). This method call the same super.findAll() method from the parent AbstractFacade.java class as the default findAll({id}) method. However, the findAllJSONP() method returns JSONP instead of XML or JSON, as findAll({id}) does. This is done by passing the results of super.findAll() to a new instance of Jersey’s JSONWithPadding() class (com.sun.jersey.api.json.JSONWithPadding).

package service;

import com.sun.jersey.api.json.JSONWithPadding;
import entities.VEmployeeNames;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import javax.ws.rs.Consumes;
import javax.ws.rs.DELETE;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.PUT;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.GenericEntity;

@Stateless
@Path("entities.vemployeenames")
public class VEmployeeNamesFacadeREST extends AbstractFacade<VEmployeeNames> {
    ...
    @GET
    @Path("jsonp")
    @Produces({"application/javascript"})
    public JSONWithPadding findAllJSONP(@QueryParam("callback") String callback) {
        CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
        CriteriaQuery cq = cb.createQuery();
        Root empRoot = cq.from(VEmployeeNames.class);
        cq.select(empRoot);
        cq.orderBy(cb.asc(empRoot.get("fullName")));
        javax.persistence.Query q = getEntityManager().createQuery(cq);

        List<VEmployeeNames> employees = q.getResultList();
        return new JSONWithPadding(
                new GenericEntity<Collection<VEmployeeNames>>(employees) {
                }, callback);
    }
    ...
}

5: Modify the Existing Service
Modify the existing VEmployeeFacadeREST.java RESTful web service class, adding the findJSONP() method shown below (class code abridged). This method calls the same super.find({id}) in the AbstractFacade.java parent class as the default find({id}) method, but returns JSONP instead of XML or JSON. As with the previous service class above, this is done by passing the results to a new instance of Jersey’s JSONWithPadding() class (com.sun.jersey.api.json.JSONWithPadding). There are no changes required to the default AbstractFacade.java class.

package service;

import com.sun.jersey.api.json.JSONWithPadding;
import entities.VEmployee;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import javax.ws.rs.Consumes;
import javax.ws.rs.DELETE;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.PUT;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.GenericEntity;

@Stateless
@Path("entities.vemployee")
public class VEmployeeFacadeREST extends AbstractFacade<VEmployee> {
    ...
    @GET
    @Path("{id}/jsonp")
    @Produces({"application/javascript"})
    public JSONWithPadding findJSONP(@PathParam("id") Integer id,
            @QueryParam("callback") String callback) {
        List<VEmployee> employees = new ArrayList<VEmployee>();
        employees.add(super.find(id));
        return new JSONWithPadding(
                new GenericEntity<Collection<VEmployee>>(employees) {
                }, callback);
    }
    ...
}

6: Allow POJO JSON Support
Add the JSONConfiguration.FEATURE_POJO_MAPPING servlet init parameter to web.xml, as shown below (xml abridged). According to the Jersey website, this will allow us to use POJO support, the easiest way to convert our Java Objects to JSON. It is based on the Jackson library.

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
    <servlet>
        <servlet-name>ServletAdaptor</servlet-name>
        <servlet-class>com.sun.jersey.spi.container.servlet.ServletContainer</servlet-class>
        <init-param>
            <description>Multiple packages, separated by semicolon(;), can be specified in param-value</description>
            <param-name>com.sun.jersey.config.property.packages</param-name>
            <param-value>service</param-value>
        </init-param>
        <init-param>
            <param-name>com.sun.jersey.api.json.POJOMappingFeature</param-name>
            <param-value>true</param-value>
        </init-param>
        ...

7: Implement a JAXBContext Resolver
Create the VEmployeeFacadeREST.java JAXBContext resolver class, shown below. This allows us to serialize the JSON using natural JSON notation. A good explanation of the use of a JAXBContext resolver can be found on the Jersey website.

package config;

import com.sun.jersey.api.json.JSONConfiguration;
import com.sun.jersey.api.json.JSONJAXBContext;
import javax.ws.rs.ext.ContextResolver;
import javax.ws.rs.ext.Provider;
import javax.xml.bind.JAXBContext;

@Provider
public class JAXBContextResolver implements ContextResolver<JAXBContext> {

    JAXBContext jaxbContext;
    private Class[] types = {entities.VEmployee.class, entities.VEmployeeNames.class};

    public JAXBContextResolver() throws Exception {
        this.jaxbContext =
                new JSONJAXBContext(JSONConfiguration.natural().build(), types);
    }

    @Override
    public JAXBContext getContext(Class<?> objectType) {
        for (Class type : types) {
            if (type == objectType) {
                return jaxbContext;
            }
        }
        return null;
    }
}

What is Natural JSON Notation?
According to the Jersey website, “with natural notation, Jersey will automatically figure out how individual items need to be processed, so that you do not need to do any kind of manual configuration. Java arrays and lists are mapped into JSON arrays, even for single-element cases. Java numbers and booleans are correctly mapped into JSON numbers and booleans, and you do not need to bother with XML attributes, as in JSON, they keep the original names.”

What does that mean? Better yet, what does that look like? Here is an example of an employee record, first as plain old JAXB JSON in a JSONP wrapper:

callback({"vEmployee":{"businessEntityID":"211","firstName":"Hazem","middleName":"E","lastName":"Abolrous","jobTitle":"Quality Assurance Manager","phoneNumberType":"Work","phoneNumber":"869-555-0125","emailAddress":"hazem0@adventure-works.com","emailPromotion":"0","addressLine1":"5050 Mt. Wilson Way","city":"Kenmore","stateProvinceName":"Washington","postalCode":"98028","countryRegionName":"United States"}})

And second, JSON wrapped in JSONP, using Jersey’s natural notation. Note the differences in the way the parent vEmployee node, numbers, and nulls are handled in natural JSON notation.

callback([{"Employee ID":211,"Title":null,"First Name":"Hazem","Middle Name":"E","Last Name":"Abolrous","Suffix":null,"Job Title":"Quality Assurance Manager","Phone Number Type":"Work","Phone Number":"869-555-0125","Email Address":"hazem0@adventure-works.com","Email Promotion":0,"Address Line 1":"5050 Mt. Wilson Way","Address Line 2":null,"City":"Kenmore","State or Province Name":"Washington","Postal Code":"98028","Country or Region Name":"United States","Additional Contact Info":null}])

Mobile Client Project

When we are done with the mobile client, the final RESTful web services mobile client NetBeans projects should look like the screen-grab, below. Note the inclusion of jQuery Mobile 1.2.0. You will need to download the library and associated components, and install them in the project. I chose to keep them in a separate folder since there were several files included with the library. This example requires a few new features introduced in jQuery Mobile 1.2.0. Make sure to get this version or later.

JerseyRESTfulClient Project View in NetBeans

8: Create a List/Detail Mobile HTML Site
The process to display the data from the Adventure Works database in the mobile web browser is identical to the process used in the last series of posts. We are still using jQuery with Ajax, calling the same services, but with a few new methods. The biggest change is the use of jQuery Mobile to display the employee data. The jQuery Mobile library, especially with the release of 1.2.0, makes displaying data, quick and elegant. The library does all the hard work under the covers, with the features such as the listview control. We simply need to use jQuery and Ajax to retrieve the data and pass it to the control.

We will create three new files. They include the HTML, CSS, and JavaScript files. We add a ‘.m’ to the file names to differentiate them from the normal web browser files from the last post. As with the previous post, the HTML page and CSS file are minimal. The HTML page uses the jQuery Mobile multi-page template available on the jQuery Mobile website. Although it appears as two different web pages to the end-user, it is actually a single-page site.

Source code for employee.m.html:

<!DOCTYPE html>
<html>
    <head> 
        <title>Employee List</title> 
        <meta name="viewport" content="width=device-width, initial-scale=1"> 
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

        <link rel="stylesheet" href="jquery.mobile-1.2.0/jquery.mobile-1.2.0.min.css" />
        <link type="text/css" rel="stylesheet" href="employees.m.css" />

        <script src="jquery-1.8.2.min.js" type="text/javascript"></script>
        <script src="jquery.mobile-1.2.0/jquery.mobile-1.2.0.min.js" type="text/javascript"></script>
        <script src="employees.m.js" type="text/javascript"></script>
    </head> 
    <body> 
        <!-- Start of first page: #one -->
        <div data-role="page" id="one" data-theme="b">
            <div data-role="header" data-theme="b">
                <h1>Employee List</h1>
            </div><!-- /header -->
            <div data-role="content">	
                <div id="errorMessage"></div>
                <div class="ui-grid-solo">
                    <form>
                        <ul data-role="listview" data-filter="true" 
                            id="employeeList" data-theme="c" data-autodividers="true">
                        </ul>
                    </form>
                </div>
            </div><!-- /content -->
            <div data-role="footer" data-theme="b">
                <h4>Programmatic Ponderings, 2012</h4>
            </div><!-- /footer -->
        </div><!-- /page -->
        
        <!-- Start of second page: #two -->
        <div data-role="page" id="two" data-theme="c">
            <div data-role="header" data-theme="b">
                <a href="#one" data-icon="back">Return</a>
                <h1>Employee Detail</h1>
            </div><!-- /header -->
            <div data-role="content" data-theme="c">	
                <div id="employeeDetail"></div>
            </div><!-- /content -->
            <div data-role="footer" data-theme="b">
                <h4>Programmatic Ponderings, 2012</h4>
            </div><!-- /footer -->
        </div><!-- /page two -->
    </body>
</html>

Source code for employee.m.css:

#employeeList {
    clear:both;
}

#employeeDetail div {
    padding-top: 2px;
    white-space: nowrap;
}

.field {
    margin-bottom: 0px;
    font-size: smaller;
    color: #707070;
}

.value {
    font-weight: bolder;
    padding-bottom: 12px;
    border-bottom: 1px #d0d0d0 solid;
}

.ui-block-a{
    padding-left: 6px;
    padding-right: 6px;
}

.ui-grid-a{
    padding-bottom: 12px;
    padding-top: -6px;
}

8: Retrieve, Parse, and Display the Data
The mobile JavaScript file below is identical in many ways to the JavaScript file used in the last series of posts for a non-mobile browser. One useful change we have made is the addition of two arguments to the function that calls jQuery.Ajax(). The address of the service (URI) that the jQuery.Ajax() method requests, and the function that Ajax calls after successful completion, are both passed into the callService(Uri, successFunction) function as arguments. This allows us to reuse the Ajax method for different purposes. In this case, we call the function once to populate the Employee List with the full names of the employees. We call it again to populate the Employee Detail page with demographic information of a single employee chosen from the Employee List. Both calls are to different URIs representing the two different RESTful web services, which in turn are associated with the two different entities, which in turn are mapped to the two different database views.

callService = function (uri, successFunction) {
        $.ajax({
            cache: true,
            url: uri,
            data: "{}",
            type: "GET",
            contentType: "application/javascript",
            dataType: "jsonp",
            error: ajaxCallFailed,
            failure: ajaxCallFailed,
            success: successFunction
        });          
    };

The rest of the functions are self-explanatory. There are two calls to the jQuery Ajax method to return data from the service, two functions to parse and format the JSONP for display in the browser, and one jQuery method that adds click events to the Employee List. We perform a bit of string manipulation to imbed the employee id into the id property of each list item (li element. Later, when the end-user clicks on the employee name in the list, the employee id is extracted from the id property of the selected list item and passed back to the service to retrieve the employee detail. The HTML snippet below shows how a single employee row in the jQuery listview. Note the id property of the li element, id="empId_121", for employee id 121.

<li id="empId_121" class="ui-btn ui-btn-icon-right ui-li-has-arrow ui-li ui-btn-up-c" 
    data-corners="false" data-shadow="false" data-iconshadow="true" 
    data-wrapperels="div" data-icon="arrow-r" data-iconpos="right" data-theme="c">
    <div class="ui-btn-inner ui-li">
        <div class="ui-btn-text">
            <a class="ui-link-inherit" href="#">Ackerman, Pilar G</a>
        </div>
        <span class="ui-icon ui-icon-arrow-r ui-icon-shadow"> </span>
    </div>
</li>

To make this example work, you need to change the restfulWebServiceBaseUri variable to the server and port of the GlassFish domain running your RESTful web services. If you are testing the client locally on your mobile device, I suggest using the IP address for the GlassFish server versus a domain name, which your phone will be able to connect to in your local wireless environment. At least on the iPhone, there is no easy way to change the hosts file to provide local domain name resolution.

Source code for employee.m.js:

// ===========================================================================
// 
// Author: Gary A. Stafford
// Website: http://www.programmaticponderings.com
// Description: Call RESTful Web Services from mobile HTML pages
//              using jQuery mobile, Jersey, Jackson, and EclipseLink
// 
// ===========================================================================

// Immediate function
(function () {
    "use strict";
    
    var restfulWebServiceBaseUri, employeeListFindAllUri, employeeByIdUri,
    callService, ajaxCallFailed,
    getEmployeeById, displayEmployeeList, displayEmployeeDetail;
    
    // Base URI of RESTful web service
    restfulWebServiceBaseUri = "http://your_server_name_or_ip:8080/JerseyRESTfulServices/webresources/";
    
    // URI maps to service.VEmployeeNamesFacadeREST.findAllJSONP
    employeeListFindAllUri = restfulWebServiceBaseUri + "entities.vemployeenames/jsonp";
        
    // URI maps to service.VEmployeeFacadeREST.findJSONP
    employeeByIdUri = restfulWebServiceBaseUri + "entities.vemployee/{id}/jsonp";
    
    
    // Execute after the page one dom is fully loaded
    $(".one").ready(function () {        
        // Retrieve employee list
        callService(employeeListFindAllUri, displayEmployeeList);
        
        // Attach onclick event to each row of employee list on page one
        $("#employeeList").on("click", "li", function(event){
            getEmployeeById($(this).attr("id").split("empId_").pop());
        });
    });
      
    // Call a service URI and return JSONP to a function
    callService = function (Uri, successFunction) {
        $.ajax({
            cache: true,
            url: Uri,
            data: "{}",
            type: "GET",
            contentType: "application/javascript",
            dataType: "jsonp",
            error: ajaxCallFailed,
            failure: ajaxCallFailed,
            success: successFunction
        });          
    };
    
    // Called if ajax call fails
    ajaxCallFailed = function (jqXHR, textStatus) { 
        console.log("Error: " + textStatus);
        console.log(jqXHR);
        $("form").css("visibility", "hidden");
        $("#errorMessage").empty().
        append("Sorry, there was an error.").
        css("color", "red");
    };
    
    // Display employee list on page one
    displayEmployeeList = function (employee) {
        var employeeList = "";
                
        $.each(employee, function(index, employee) {
            employeeList = employeeList.concat(
                "<li id=empId_" + employee.businessEntityID.toString() + ">" + 
                "<a href='#'>" + 
                employee.fullName.toString() + "</a></li>");
        });
        
        $('#employeeList').empty();
        $('#employeeList').append(employeeList).listview("refresh", true);
    };
    
    // Display employee detail on page two
    displayEmployeeDetail = function(employee) {
        $.mobile.loading( 'show', {
            text: '',
            textVisible: false,
            theme: 'a',
            html: ""
            
        });
        window.location = "#two";
        var employeeDetail = "";
                
        $.each(employee, function(key, value) {
            $.each(value, function(key, value) {
                if(!value) {
                    value = "&nbsp;";
                }
                
                employeeDetail = employeeDetail.concat(
                    "<div class='detail'>" +
                    "<div class='field'>" + key + "</div>" +
                    "<div class='value'>" + value + "</div>" +
                    "</div>");   
            });
        });
        
        $("#employeeDetail").empty().append(employeeDetail);
    };
    
    // Retrieve employee detail based on employee id
    getEmployeeById = function (employeeID) {
        callService(employeeByIdUri.replace("{id}", employeeID), displayEmployeeDetail);
    };
} ());

The Final Result

Viewed in Google’s Chrome for Mobile web browser on iOS 6, the previous project’s Employee List looks pretty bland and un-mobile like:

Previous Project as Viewed in Google Chrome Mobile Browser

Previous Project as Viewed in Google Chrome for Mobile Web Browser

However, with a little jQuery Mobile magic you get a simple yet effective and highly functional mobile web presentation. Seen below on page one, the Employee List is displayed in Safari on an iPhone 4 with iOS 6. It features some of the new capabilities of jQuery Mobile 1.2.0’s improved listview, including autodividers.

Employee List

Here again is the Employee List using the jQuery Mobile 1.2.0’s improved listview search filter bar:

Employee List – Filtered

Here is the Employee Detail on page 2. Note the order and names of the fields. Remember previously when we annotated the VEmployeeNames.java entity with the @XmlType(propOrder = {"businessEntityID", ...}) to the class and the @JsonProperty(value = ...) tags to each member variable. This is the results of those efforts; our JSON is delivered pre-sorted and titled the way we want. No need to handle those functions on the client-side. This allows the client to be loosely-coupled to the data. The client simply displays whichever key/value pairs are delivered in the JSONP response payload.

Employee Detail

Employee Detail – Bottom

AJAX, Apache Ant, Chrome, Domain, EclipseLink, GlassFish, HTML, iOS6, iPhone, jackson, Java, JAVA EE, JavaScript, JAX-RS, JAXB, JDBC, jersey, jersey-json, JPA, jQuery, jquery mobile, JSON, JSON with Padding, JSONP, JSONwithPadding, mobile, NetBeans, Persistence, REST, RESTful Web Services, same origin policy, SQL, SQL Server, SQL2008R2

4 Comments

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

Posted by Gary A. Stafford in Java Development, Software Development on October 4, 2012

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

In part 1 of this series, we created a Jersey-specific RESTful web service from a database using NetBeans. The service returns JSONP in addition to JSON and XML. The service was deployed to a GlassFish domain, running on a Windows box. On this same box is the SQL Server instance, running the Adventure Works database, from which the service obtains data, via the entity class.

Objectives

In part two of this series, we will create a simple web client to consume and display the JSONP returned by the RESTful web service. There are many options available for creating a service consumer (client) depending on your development platform and project requirements. We will keep it simple, no complex, complied code, just HTML and JavaScript with jQuery, the well-known JavaScript library.

We will host the client on a separate 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.

Here are the high-level steps we will walk-through in part two:

Create a simple HTML client using jQuery and ajax to call the RESTful web service;
Add jQuery functionality to parse and display the JSONP returned by the service;
Deploy the client to a separate remote instance of GlassFish using Apache Ant;
Test the client’s ability to call the service across domains and display JSONP.

Creating the RESTful Web Service Client

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

Create a New Web Application Project 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

Adding Files to Project
The final client project will contains four new files:

employees.html – HTML web page that displays a list of employees;
employees.css – CSS information used to by employees.html;
employees.js – JavaScript code used to by employees.html;
jquery-1.8.2.min.js – jQuery 1.8.2 JavaScript library, minified.

First, we need to download and install jQuery. At the time of this post, jQuery 1.8.2 was the latest version. I installed the minified version (jquery-1.8.2.min.js) to save space.

Next, we will create the three new files (employees.html, employees.css, and employees.js), using the code below. When finished, we need to place all four files into the ‘Web Pages’ folder. The final project should look like:

Final Client Project View

HTML
The HTML file is the smallest of the three files. The HTML page references the CSS file, the JavaScript file, and the jQuery library file. The CSS file provides the presentation (look and feel) and JavaScript file, using jQuery, dynamically provides much of the content that the HTML page normally would contain.

<!DOCTYPE html>
<html>
    <head>
        <title>Employee List</title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <link type="text/css" rel="stylesheet" href="employees.css" />
        <script src="jquery-1.8.2.min.js" type="text/javascript"></script>
        <script src="employees.js" type="text/javascript"></script>
    </head>
    <body>
        <div id="pageTitle">Employee List</div>
        <div id="employeeList"></div>
    </body>
</html>

Cascading Style Sheets (CSS)
The CSS file is also pretty straight-forward. The pageTitle and employeeList id selectors and type selectors are used directly by the HTML page. The class selectors are all applied to the page by jQuery, in the JavaScript file.

body {
    font-family: sans-serif;
    font-size: small;
    padding-left: 6px;
}

span {
    padding: 6px;
    display: inline-block;
}

div {
    border-bottom: lightgray solid 1px;
}

#pageTitle {
    font-size: medium;
    font-weight: bold;
    padding: 12px 0px 12px 0px;
    border: none;
}

#employeeList {
    float: left;
    border: gray solid 1px;
}

.empId {
    width: 50px;
    text-align: center;
    border-right: lightgray solid 1px;
}

.name {
    width: 200px;
    border-right: lightgray solid 1px;
}

.jobTitle {
    width: 250px;
}

.header {
    font-weight: bold;
    border-bottom: gray solid 1px;
}

.even{
    background-color: rgba(0, 255, 128, 0.09);
}

.odd {
    background-color: rgba(0, 255, 128, 0.05);
}

.last {
    border-bottom: none;
}

jQuery and JavaScript
The JavaScript file is where all the magic happens. There are two primary functions. First, getEmployees, which calls the jQuery.ajax() method. According jQuery’s website, the jQuery Ajax method performs an asynchronous HTTP (Ajax) request. In this case, it calls our RESTful web service and returns JSONP. The jQuery Ajax method uses an HTTP GET method to request the following service resource (URI):

http://[your-service's-glassfish-server-name]:[your-service's-glassfish-domain-port]/JerseyRESTfulService/webresources/entities.vemployee/{from}/{to}/jsonp?callback={callback}.

The base (root) URI of the service in the URI above is as follows:

http://[server]:[port]/JerseyRESTfulService/webresources/entities.vemployee/

This is followed by a series of elements (nodes), {from}/{to}/jsonp, which together form a reference to a specific method in our service. As explained in the first post of this series, we include the /jsonp element to indicate we want to call the new findRangeJsonP method to return JSONP, as opposed to findRange method that returns JSON or XML. We pass the {from} path parameter a value of ‘0’ and the {to} path parameter a value of ‘10’.

Lastly, the method specifies the callback function name for the JSONP request, parseResponse, using the jsonpCallback setting. This value will be used instead of the random name automatically generated by jQuery. The callback function name is appended to the end of the URI as a query parameter. The final URL is as follows:

http://[server]:[port]/JerseyRESTfulService/webresources/entities.vemployee/0/10/jsonp?callback=parseResponse.

Note the use of the jsonpCallback setting is not required, or necessarily recommended by jQuery. Without it, jQuery generate a unique name as it will make it easier to manage the requests and provide callbacks and error handling. This example will work fine if you exclude the jsonpCallback: "parseResponse" setting.

getEmployees = function () {
    $.ajax({
        cache: true,
        url: restfulWebServiceURI,
        data: "{}",
        type: "GET",
        jsonpCallback: "parseResponse",
        contentType: "application/javascript",
        dataType: "jsonp",
        error: ajaxCallFailed,
        failure: ajaxCallFailed,
        success: parseResponse
    });
};

Once we have successfully returned the JSONP, the jQuery Ajax method calls the parseResponse(data) function, passing the JSON to the data argument. The parseResponse function iterates through the employee objects using the jQuery.each() method. Each field of data is surrounding with span and div tags, and concatenated to the employeeList string variable. The string is appended to the div tag with the id of ‘employeeList’, using jQuery’s .append() method. The result is an HTML table-like grid of employee names, ids, and job title, displayed on the employees.html page.

Lastly, we call the colorRows() function. This function uses jQuery’s .addClass(className) to assign CSS classes to objects in the DOM. The classes are added to stylize the grid with alternating row colors and other formatting.

parseResponse = function (data) {
    var employee = data.vEmployee;

    var employeeList = "";

    employeeList = employeeList.concat("<div class='header'>" +
        "<span class='empId'>Id</span>" +
        "<span class='name'>Employee Name</span>" +
        "<span class='jobTitle'>Job Title</span>" +
        "</div>");

    $.each(employee, function(index, employee) {
        employeeList = employeeList.concat("<div class='employee'>" +
            "<span class='empId'>" +
            employee.businessEntityID +
            "</span><span class='name'>" +
            employee.firstName + " " + employee.lastName +
            "</span><span class='jobTitle'>" +
            employee.jobTitle +
            "</span></div>");
    });

    $("#employeeList").empty();
    $("#employeeList").append(employeeList);
    colorRows();
};

Here are the complete JavaScript file contents:

// Immediate function
(function () {
    "use strict";
    
    var restfulWebServiceURI, getEmployees, ajaxCallFailed, colorRows, parseResponse;
    
    restfulWebServiceURI = "http://[your-service's-server-name]:[your-service's-port]/JerseyRESTfulService/webresources/entities.vemployee/0/10/jsonp";
    
    // Execute after the DOM is fully loaded
    $(document).ready(function () {
        getEmployees();
    });

    // Retrieve Employee List as JSONP
    getEmployees = function () {
        $.ajax({
            cache: true,
            url: restfulWebServiceURI,
            data: "{}",
            type: "GET",
            jsonpCallback: "parseResponse",
            contentType: "application/javascript",
            dataType: "jsonp",
            error: ajaxCallFailed,
            failure: ajaxCallFailed,
            success: parseResponse
        });          
    };
    
    // Called if ajax call fails
    ajaxCallFailed = function (jqXHR, textStatus) { 
        console.log("Error: " + textStatus);
        console.log(jqXHR);
        $("#employeeList").empty();
        $("#employeeList").append("Error: " + textStatus);
    };
            
    // Called if ajax call is successful
    parseResponse = function (data) {
        var employee = data.vEmployee;   
        
        var employeeList = "";
        
        employeeList = employeeList.concat("<div class='header'>" +
            "<span class='empId'>Id</span>" + 
            "<span class='name'>Employee Name</span>" + 
            "<span class='jobTitle'>Job Title</span>" + 
            "</div>"); 
        
        $.each(employee, function(index, employee) {
            employeeList = employeeList.concat("<div class='employee'>" +
                "<span class='empId'>" +
                employee.businessEntityID + 
                "</span><span class='name'>" +
                employee.firstName + " " + employee.lastName +
                "</span><span class='jobTitle'>" +
                employee.jobTitle + 
                "</span></div>");
        });
        
        $("#employeeList").empty();
        $("#employeeList").append(employeeList);
        colorRows();
    };
    
    // Styles the Employee List
    colorRows = function(){
        $("#employeeList .employee:odd").addClass("odd");
        $("#employeeList .employee:even").addClass("even");
        $("#employeeList .employee:last").addClass("last");
    };
} ());

Deployment to GlassFish
To deploy the RESTful web service client 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 client than it was for the service in part 1 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-client's-glassfish-server-name] 
            --port=[your-client's-glassfish-domain-admin-port]
            --user=admin --passwordfile=pwdfile --secure=false
            deploy --force=true --name=JerseyRESTfulClient
            --contextroot=/JerseyRESTfulClient dist\JerseyRESTfulClient.war" />
    </exec>
</target>

Although the client application does not require any Java code, JSP pages, or Servlets, I chose to use NetBeans’ Web Application project template to create the client and chose to create a .war file to make deployment to GlassFish easier. You could just install the four client files (jQuery, HTML, CSS, and JavaScript) on Apache, IIS, or any other web server as a simple HTML site.

08c - Deploy RESTful Web Service Client to Remote GlassFish Server

Deploy Client Application to Remote GlassFish Domain Using Ant Target

Once the application is deployed to GlassFish, you should see the ‘JerseyRESTfulClient’ listed under the Applications tab within the remote server domain.

08d - Deploy RESTful Web Service Client to Remote GlassFish Server

Client Application Deployed to Remote GlassFish Domain

We will call the client application from our browser. The client application, whose files are downloaded and are now local on our machine, will in turn will call the service. The URL to call the client is: http://[your-client's-glassfish-server-name]:[your-client's-glassfish-domain-port]/JerseyRESTfulClient/employees.html (see call-out 1, in the screen-grab, below).

Using Firefox with Firebug, we can observe a few important items once the results are displayed (see the screen-grab, below):

The four client files (jQuery, HTML, CSS, and JavaScript) are cached after the first time the client URL loads, but the jQuery Ajax service call is never cached (call-out 2);
All the client application files are loaded from one domain, while the service is called from another domain (call-out 3);
The ‘parseRequest’ callback function in the JSONP response payload, wraps the JSON data (call-out 4).

Employee List Displayed by Client Application in Firefox

The JSONP returned by the service to the client (abridged for length):

parseResponse({"vEmployee":[{"addressLine1":"4350 Minute Dr.","businessEntityID":"1","city":"Newport Hills","countryRegionName":"United States","emailAddress":"ken0@adventure-works.com","emailPromotion":"0","firstName":"Ken","jobTitle":"Chief Executive Officer","lastName":"Sánchez","middleName":"J","phoneNumber":"697-555-0142","phoneNumberType":"Cell","postalCode":"98006","stateProvinceName":"Washington"},{"addressLine1":"7559 Worth Ct.","businessEntityID":"2","city":"Renton","countryRegionName":"United States","emailAddress":"terri0@adventure-works.com","emailPromotion":"1","firstName":"Terri","jobTitle":"Vice President of Engineering","lastName":"Duffy","middleName":"Lee","phoneNumber":"819-555-0175","phoneNumberType":"Work","postalCode":"98055","stateProvinceName":"Washington"},{...}]})

The JSON passed to the parseResponse(data) function’s data argument (abridged for length):

{"vEmployee":[{"addressLine1":"4350 Minute Dr.","businessEntityID":"1","city":"Newport Hills","countryRegionName":"United States","emailAddress":"ken0@adventure-works.com","emailPromotion":"0","firstName":"Ken","jobTitle":"Chief Executive Officer","lastName":"Sánchez","middleName":"J","phoneNumber":"697-555-0142","phoneNumberType":"Cell","postalCode":"98006","stateProvinceName":"Washington"},{"addressLine1":"7559 Worth Ct.","businessEntityID":"2","city":"Renton","countryRegionName":"United States","emailAddress":"terri0@adventure-works.com","emailPromotion":"1","firstName":"Terri","jobTitle":"Vice President of Engineering","lastName":"Duffy","middleName":"Lee","phoneNumber":"819-555-0175","phoneNumberType":"Work","postalCode":"98055","stateProvinceName":"Washington"},{...}]}

Firebug also allows us to view the JSON in a more structured and object-oriented view:

Employee List Displayed by Client Application in Firefox (showing JSON in Firebug)

Firefox Showing formatted JSON Data Using Firebug

Conclusion

We have successfully built and deployed a RESTful web service to one GlassFish domain, capable of returning JSONP. We have also built and deployed an HTML client to another GlassFish domain, capable of calling the service and displaying the JSONP. The service and client in this example have very minimal functionality. However, the service can easily be scaled to include multiple entities and RESTful services. The client’s capability can be expanded to perform a full array of CRUD operations on the database, through the RESTful web service(s).

AJAX, Apache Ant, Domain, EclipseLink, GlassFish, HTML, jackson, Java, JAVA EE, JavaScript, JAX-RS, JAXB, JDBC, jersey-json, JPA, jQuery, JSON, JSON with Padding, JSONP, JSONwithPadding, Linux, NetBeans, Persistence, REST, RESTful Web Services, same origin policy, SQL, SQL Server, SQL2008R2, Ubuntu, Virtual Machine, VirtualBox, VM

4 Comments

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

Posted by Gary A. Stafford in Java Development, Software Development on October 1, 2012

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:

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.
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:

Microsoft SQL Server 2008 R2 (www.microsoft.com/sqlserver)
Microsoft’s Adventure Works 2008 R2 Sample Database (msftdbprodsamples.codeplex.com)
Microsoft JDBC Driver 4.0 for SQL Server (msdn.microsoft.com/en-us/sqlserver/aa937724.aspx)
NetBeans 7.2 Open Source IDE (netbeans.org)
Apache Any 1.82 (installed with NetBeans or downloaded separately from ant.apache.org)
GlassFish 3.2.2.2 Open Source Edition Application Server (installed with NetBeans or downloaded separately from glassfish.java.net)

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.

Create a New Web Application Project in NetBeans

Name and Location of New Web Application Project

Server and Settings of New Web Application Project

Optional Frameworks to Include in New Web Application Project

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.

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…’.

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.

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.

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.

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://[your-service's-glassfish-server-name]:[your-service's-glassfish-domain-port]/JerseyRESTfulService/webresources/entities.vemployee/{from}/{to}/jsonp?callback={callback}

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

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 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.

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://[your-service's-glassfish-server-name]:[your-service's-glassfish-domain-port]/JerseyRESTfulService/webresources/entities.vemployee/1/3/jsonp?callback=parseResponse

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.

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

Posted by Gary A. Stafford in Java Development, Software Development on September 15, 2012

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:

Microsoft SQL Server 2008 R2 (www.microsoft.com/sqlserver)
Microsoft’s Adventure Works 2008 R2 Sample Database (msftdbprodsamples.codeplex.com)
Microsoft JDBC Driver 4.0 for SQL Server (msdn.microsoft.com/en-us/sqlserver/aa937724.aspx)
NetBeans 7.2 Open Source IDE (netbeans.org)
Apache Any 1.82 (installed with NetBeans or downloaded separately from ant.apache.org)
GlassFish 3.2.2.2 Open Source Edition Application Server (installed with NetBeans or downloaded separately from glassfish.java.net)

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:

Confirm the SQL Server instance, database, and user are functioning properly;
Create a new Web Application project in NetBeans;
Create the SQL Server data source in the project;
Create entity classes from the SQL Server database;
Create RESTful web services using the entity classes;
Test the web services locally in NetBeans;
Build and deploy the project to GlassFish;
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.

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

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

Provide the SQL Server Adventure Works Database Connection Information

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

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

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 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

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.

.war, Adventure Works, Connection Pool, Deploy, Entity Classes, GlassFish, JAVA EE, JAX-RS, JDBC, JDBC Resource, JNDI, JPA, NetBeans, Persistence, REST, RESTful Web Services, SQL, SQL Server, SQL2008R2, Web Application, XML

15 Comments

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

Posted by Gary A. Stafford in Java Development, Software Development, SQL Server Development on August 24, 2012

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.

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.

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

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.

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.Size, Product.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.

Adventure Works, CallableStatement, cross-platform, Database, database driver, Java, Java Database Connectivity, JDBC, Microsoft, NetBeans, PreparedStatement, SQL, SQL Server, SQL2008R2, Statement, stored procedure

15 Comments

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

Posted by Gary A. Stafford in Java Development, Software Development on April 8, 2012

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

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

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

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

Microsoft SQL Server as Data Source

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

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

Customer Service Database Diagram

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

Configuring Microsoft’s JDBC Driver with NetBeans

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

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

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

Adding a New JDBC Driver in NetBeans

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

Creating a New Database Connection in NetBeans

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

Selecting a Default Database Schema

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

List of Database Connections in NetBeans

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

Configuring Microsoft’s JDBC Driver with GlassFish

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

Additional Capabilities

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

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.

Database, Datasource, GlassFish, Java, Java Development, JAVA EE, JDBC, JPA, MOXy, NetBeans, Persistence, SQL, SQL Server, SQL2008R2, XML

8 Comments

Posts Tagged JDBC

Introduction

Why Stored Procedures?

AdventureWorks 2017 Database

Install Stored Procedures

Data Sources, Connections, and Properties

Examples

Example 1: SQL Statement

Example 2: Prepared Statement

Example 3: Callable Statement

Example 4: Calling a Stored Procedure with an Output Parameter

Example 5: Calling a Stored Procedure with an Input Parameter

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

Proper T-SQL: Schema Reference and Brackets

Running the Examples

SQL Statement Performance

Conclusion

Introduction

Installation and Configuration

MySQL Demo User Account

New MySQL Database Connection

New Java Class Library

Entity Classes from Database

Adding Entity IDs to Views

New Java Web Application

RESTful Web Services from Entity Classes

Creating RESTful Web Services Test

Conclusion

Links

Background

Objectives

RESTful Web Services Project

Mobile Client Project

The Final Result

Background

Objectives

Creating the RESTful Web Service Client

Conclusion

Background

Objectives

Demonstration

Creating the RESTful Web Service

Conclusion

Introduction

Setting up the Post’s Example

The Demonstration

SQL Server

Create New Web Application Project

Create SQL Server Data Source and Entity Classes from the Database

RESTful Web Services from Entity Classes

Adding a Primary Key to Entity Classes

Test RESTful Web Services Locally in NetBeans

WADL

Deploy Project to GlassFish

Test Web Services on GlassFish

Conclusion

Introduction

Why Stored Procedure?

Adventure Works 2008 Database

Data Sources, Connections, and Properties

Examples

Example 1: A Simple Statement

Example 2: The Prepared Statement

Example 3: The Callable Statement

Example 4: Calling a Stored Procedure with an Output Parameter

Example 5: Calling a Stored Procedure with an Input Parameter

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

Schema Reference

Running the Examples

SQL Statement Performance

Conclusion

Microsoft SQL Server as Data Source

Configuring Microsoft’s JDBC Driver with NetBeans

Configuring Microsoft’s JDBC Driver with GlassFish

Additional Capabilities

Gary Stafford

Recent Posts

Top Posts & Pages

Tag Cloud