Posts Tagged SQL Server
Using the WCF Web HTTP Programming Model with Entity Framework 5
Posted by Gary A. Stafford in .NET Development, Client-Side Development, Software Development on December 12, 2012
Build a IIS-hosted WCF Service using the WCF Web HTTP Programming Model. Use basic HTTP Methods with the WCF Service to perform CRUD operations on a SQL Server database using a Data Access Layer, built with Entity Framework 5 and the Database First Development Model.
You can download a complete copy of this Post’s source code from DropBox.
Introduction
In the two previous Posts, we used the new Entity Framework 5 to create a Data Access Layer, using both the Code First and Database First Development Models. In this Post, we will create a Windows Communication Foundation (WCF) Service. The service will sit between the client application and our previous Post’s Data Access Layer (DAL), built with an ADO.NET Entity Data Model (EDM). Using the WCF Web HTTP Programming Model, we will expose the WCF Service’s operations to a non-SOAP endpoint, and call them using HTTP Methods.
Why use the WCF Web HTTP Programming Model? WCF is a well-established, reliable, secure, enterprise technology. Many large, as well as small, organizations use WCF to build service-oriented applications. However, as communications become increasingly Internet-enabled and mobile, the WCF Web HTTP Programming Model allows us to add the use of simple HTTP methods, such as POST, GET, DELETE, and PUT, to existing WCF services. Adding a web endpoint to an existing WCF service extends its reach to modern end-user platforms with minimal effort. Lastly, using the WCF Web HTTP Programming Model allows us to move toward the increasingly popular RESTful Web Service Model, so many organizations are finally starting to embrace in the enterprise.
Creating the WCF Service
The major steps involved in this example are as follows:
- Create a new WCF Service Application Project;
- Add the Entity Framework package via NuGet;
- Add a Reference the previous Post’s DAL project;
- Add a Connection String to the project’s configuration;
- Create the WCF Service Contract;
- Create the operations the service will expose via a web endpoint;
- Configure the service’s behaviors, binding, and web endpoint;
- Publish the WCF Service to IIS using VS2012’s Web Project Publishing Tool;
- Test the service’s operations with Fiddler.
The WCF Service Application Project
Solution Explorer View of New Solution for Reference
Start by creating a new Visual Studio 2012 WCF Service Application Project, named ‘HealthTracker.WcfService’. Add it to a new Solution, named ‘HealthTracker’. The WCF Service Application Project type is specifically designed to be hosted by Microsoft’s Internet Information Services (IIS).
Create New WCF Service Application Project
Once the Project and Solution are created, install Entity Framework (‘System.Data.Entity’) into the Solution by right-clicking on the Solution and selecting ‘Manage NuGet Packages for Solution…’ Install the ‘EntityFramework’ package. If you haven’t discovered the power of NuGet for Visual Studio, check out their site.
Manage NuGet Packages – Add Entity Framework to Solution
Next, add a Reference in the new Project, to the previous ‘HealthTracker.DataAccess.DbFirst’ Project. When the WCF Service Application Project is built, a copy of the ‘HealthTracker.DataAccess.DbFirst.dll’ assembly will be placed into the ‘bin’ folder of the ‘HealthTracker.WcfService’ Project.
Add a Reference to Previous EF5 Database First Project
Next, copy the connection string from the previous project’s ‘App.Config file’ and paste into the new WCF Service Application Project’s ‘Web.config’ file. The connection is required by the ‘HealthTracker.DataAccess.DbFirst.dll’ assembly. The connection string should look similar to the below code.
<connectionStrings>
<add name="HealthTrackerEntities" connectionString="metadata=res://*/HealthTracker.csdl|res://*/HealthTracker.ssdl|res://*/HealthTracker.msl;provider=System.Data.SqlClient;provider connection string="data source=[Your_Server]\[Your_SQL_Instance];initial catalog=HealthTracker;persist security info=True;user id=DemoLogin;password=[Your_Password];MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
The WCF Service
Delete the default ‘Service.svc’ and ‘IService.cs’ created by the Project Template. You can also delete the default ‘App_Data’ folder. Add a new WCF Service, named ‘HealthTrackerWcfService.svc’. Adding a new service creates both the WCF Service file (.svc), as well as a WCF Service Contract file (.cs), an Interface, named ‘IHealthTrackerWcfService.cs’. The ‘HealthTrackerWcfService’ class implements the ‘IHealthTrackerWcfService’ Interface class (‘public class HealthTrackerWcfService : IHealthTrackerWcfService’).
Add New WCF Service to Project
The WCF Service file contains public methods, called service operations, which the service will expose through a web endpoint. The second file, an Interface class, is referred to as the Service Contract. The Service Contract contains the method signatures of all the operations the service’s web endpoint expose. The Service Contract contains attributes, part of the ‘System.ServiceModel’ and ‘System.ServiceModel.Web’ Namespaces, describing how the service and its operation will be exposed. To create the Service Contract, replace the default code in the file, ‘IHealthTrackerWcfService.cs’, with the following code.
using System.Collections.Generic;
using System.ServiceModel;
using System.ServiceModel.Web;
using HealthTracker.DataAccess.DbFirst;
namespace HealthTracker.WcfService
{
[ServiceContract]
public interface IHealthTrackerWcfService
{
[OperationContract]
[WebInvoke(UriTemplate = "GetPersonId?name={personName}",
Method = "GET")]
int GetPersonId(string personName);
[OperationContract]
[WebInvoke(UriTemplate = "GetPeople",
Method = "GET")]
List<Person> GetPeople();
[OperationContract]
[WebInvoke(UriTemplate = "GetPersonSummaryStoredProc?id={personId}",
Method = "GET")]
List<GetPersonSummary_Result> GetPersonSummaryStoredProc(int personId);
[OperationContract]
[WebInvoke(UriTemplate = "InsertPerson",
Method = "POST")]
bool InsertPerson(Person person);
[OperationContract]
[WebInvoke(UriTemplate = "UpdatePerson",
Method = "PUT")]
bool UpdatePerson(Person person);
[OperationContract]
[WebInvoke(UriTemplate = "DeletePerson?id={personId}",
Method = "DELETE")]
bool DeletePerson(int personId);
[OperationContract]
[WebInvoke(UriTemplate = "UpdateOrInsertHydration?id={personId}",
Method = "POST")]
bool UpdateOrInsertHydration(int personId);
[OperationContract]
[WebInvoke(UriTemplate = "InsertActivity",
Method = "POST")]
bool InsertActivity(Activity activity);
[OperationContract]
[WebInvoke(UriTemplate = "DeleteActivity?id={activityId}",
Method = "DELETE")]
bool DeleteActivity(int activityId);
[OperationContract]
[WebInvoke(UriTemplate = "GetActivities?id={personId}",
Method = "GET")]
List<ActivityDetail> GetActivities(int personId);
[OperationContract]
[WebInvoke(UriTemplate = "InsertMeal",
Method = "POST")]
bool InsertMeal(Meal meal);
[OperationContract]
[WebInvoke(UriTemplate = "DeleteMeal?id={mealId}",
Method = "DELETE")]
bool DeleteMeal(int mealId);
[OperationContract]
[WebInvoke(UriTemplate = "GetMeals?id={personId}",
Method = "GET")]
List<MealDetail> GetMeals(int personId);
[OperationContract]
[WebInvoke(UriTemplate = "GetPersonSummaryView?id={personId}",
Method = "GET")]
List<PersonSummaryView> GetPersonSummaryView(int personId);
}
}
The service’s operations use a variety of HTTP Methods, including GET, POST, PUT, and DELETE. The operations take a mix of primitive data types, as well as complex objects as arguments. The operations also return the same variety of simple data types, as well as complex objects. Note the operation ‘InsertActivity’ for example. It takes a complex object, an ‘Activity’, as an argument, and returns a Boolean. All the CRUD operations dealing with inserting, updating, or deleting data return a Boolean, indicating success or failure of the operation’s execution. This makes unit testing and error handling on the client-side easier.
Next, we will create the WCF Service. Replace the existing contents of the ‘HealthTrackerWcfService.svc’ file with the following code.
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.ServiceModel;
using HealthTracker.DataAccess.DbFirst;
namespace HealthTracker.WcfService
{
[ServiceBehavior(AddressFilterMode = AddressFilterMode.Any)]
public class HealthTrackerWcfService : IHealthTrackerWcfService
{
private readonly DateTime _today = DateTime.Now.Date;
#region Service Operations
/// <summary>
/// Example of Adding a new Person.
/// </summary>
/// <param name="person">New Person Object</param>
/// <returns>True if successful</returns>
public bool InsertPerson(Person person)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
dbContext.People.Add(new DataAccess.DbFirst.Person { Name = person.Name });
dbContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return false;
}
}
/// <summary>
/// Example of Updating a Person.
/// </summary>
/// <param name="person">New Person Object</param>
/// <returns>True if successful</returns>
public bool UpdatePerson(Person person)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var personToUpdate = dbContext.People.First(p => p.PersonId == person.PersonId);
if (personToUpdate == null) return false;
personToUpdate.Name = person.Name;
dbContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return false;
}
}
/// <summary>
/// Example of deleting a Person.
/// </summary>
/// <param name="personId">PersonId</param>
/// <returns>True if successful</returns>
public bool DeletePerson(int personId)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var personToDelete = dbContext.People.First(p => p.PersonId == personId);
if (personToDelete == null) return false;
dbContext.People.Remove(personToDelete);
dbContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return false;
}
}
/// <summary>
/// Example of finding a Person's Id.
/// </summary>
/// <param name="personName">Name of the Person to find</param>
/// <returns>Person's unique Id (PersonId)</returns>
public int GetPersonId(string personName)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var personId = dbContext.People
.Where(person => person.Name == personName)
.Select(person => person.PersonId)
.First();
return personId;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return -1;
}
}
/// <summary>
/// Returns a list of all People.
/// </summary>
/// <returns>List of People</returns>
public List<Person> GetPeople()
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var people = (dbContext.People.Select(p => p));
var peopleList = people.Select(p => new Person
{
PersonId = p.PersonId,
Name = p.Name
}).ToList();
return peopleList;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return null;
}
}
/// <summary>
/// Example of adding a Meal.
/// </summary>
/// <param name="meal">New Meal Object</param>
/// <returns>True if successful</returns>
public bool InsertMeal(Meal meal)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
dbContext.Meals.Add(new DataAccess.DbFirst.Meal
{
PersonId = meal.PersonId,
Date = _today,
MealTypeId = meal.MealTypeId,
Description = meal.Description
});
dbContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return false;
}
}
/// <summary>
/// Example of deleting a Meal.
/// </summary>
/// <param name="mealId">MealId</param>
/// <returns>True if successful</returns>
public bool DeleteMeal(int mealId)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var mealToDelete = dbContext.Meals.First(m => m.MealTypeId == mealId);
if (mealToDelete == null) return false;
dbContext.Meals.Remove(mealToDelete);
dbContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return false;
}
}
/// <summary>
/// Return all Meals for a Person.
/// </summary>
/// <param name="personId">PersonId</param>
/// <returns></returns>
public List<MealDetail> GetMeals(int personId)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var meals = dbContext.Meals.Where(m => m.PersonId == personId)
.Select(m => new MealDetail
{
MealId = m.MealId,
Date = m.Date,
Type = m.MealType.Description,
Description = m.Description
}).ToList();
return meals;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return null;
}
}
/// <summary>
/// Example of adding an Activity.
/// </summary>
/// <param name="activity">New Activity Object</param>
/// <returns>True if successful</returns>
public bool InsertActivity(Activity activity)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
dbContext.Activities.Add(new DataAccess.DbFirst.Activity
{
PersonId = activity.PersonId,
Date = _today,
ActivityTypeId = activity.ActivityTypeId,
Notes = activity.Notes
});
dbContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return false;
}
}
/// <summary>
/// Example of deleting a Activity.
/// </summary>
/// <param name="activityId">ActivityId</param>
/// <returns>True if successful</returns>
public bool DeleteActivity(int activityId)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var activityToDelete = dbContext.Activities.First(a => a.ActivityId == activityId);
if (activityToDelete == null) return false;
dbContext.Activities.Remove(activityToDelete);
dbContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return false;
}
}
/// <summary>
/// Return all Activities for a Person.
/// </summary>
/// <param name="personId">PersonId</param>
/// <returns>List of Activities</returns>
public List<ActivityDetail> GetActivities(int personId)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var activities = dbContext.Activities.Where(a => a.PersonId == personId)
.Select(a => new ActivityDetail
{
ActivityId = a.ActivityId,
Date = a.Date,
Type = a.ActivityType.Description,
Notes = a.Notes
}).ToList();
return activities;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return null;
}
}
/// <summary>
/// Example of updating existing Hydration count.
/// Else adding new Hydration if it doesn't exist.
/// </summary>
/// <param name="personId">PersonId</param>
/// <returns>True if successful</returns>
public bool UpdateOrInsertHydration(int personId)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var existingHydration = dbContext.Hydrations.First(
hydration => hydration.PersonId == personId
&& hydration.Date == _today);
if (existingHydration != null && existingHydration.HydrationId > 0)
{
existingHydration.Count++;
dbContext.SaveChanges();
return true;
}
dbContext.Hydrations.Add(new Hydration
{
PersonId = personId,
Date = _today,
Count = 1
});
dbContext.SaveChanges();
return true;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return false;
}
}
/// <summary>
/// Return a count of all Meals, Hydrations, and Activities for a Person.
/// Based on a Database View (virtual table).
/// </summary>
/// <param name="personId">PersonId</param>
/// <returns>Summary for a Person</returns>
public List<PersonSummaryView> GetPersonSummaryView(int personId)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var personView = (dbContext.PersonSummaryViews
.Where(p => p.PersonId == personId))
.ToList();
return personView;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return null;
}
}
/// <summary>
/// Return a count of all Meals, Hydrations, and Activities for a Person.
/// Based on a Stored Procedure.
/// </summary>
/// <param name="personId">PersonId</param>
/// <returns>Summary for a Person</returns>
public List<GetPersonSummary_Result> GetPersonSummaryStoredProc(int personId)
{
try
{
using (var dbContext = new HealthTrackerEntities())
{
var personView = (dbContext.GetPersonSummary(personId)
.Where(p => p.PersonId == personId))
.ToList();
return personView;
}
}
catch (Exception exception)
{
Debug.WriteLine(exception);
return null;
}
}
#endregion
}
#region POCO Classes
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
}
public class Meal
{
public int PersonId { get; set; }
public int MealTypeId { get; set; }
public string Description { get; set; }
}
public class MealDetail
{
public int MealId { get; set; }
public DateTime Date { get; set; }
public string Type { get; set; }
public string Description { get; set; }
}
public class Activity
{
public int PersonId { get; set; }
public int ActivityTypeId { get; set; }
public string Notes { get; set; }
}
public class ActivityDetail
{
public int ActivityId { get; set; }
public DateTime Date { get; set; }
public string Type { get; set; }
public string Notes { get; set; }
}
#endregion
}
Each method instantiates an instance of ‘HeatlthTrackerEntities’, Referenced by the project and accessible to the class via the ‘using HealthTracker.DataAccess.DbFirst;’ statement, ‘HeatlthTrackerEntities’ implements ‘System.Data.Entity.DBContext’. Each method uses LINQ to Entities to interact with the Entity Data Model, through the ‘HeatlthTrackerEntities’ object.
In addition to the methods (service operations) contained in the HealthTrackerWcfService class, there are several POCO classes. Some of these POCO classes, such as ‘NewMeal’ and ‘NewActivity’, are instantiated to hold data passed in the operation’s arguments by the client Request message. Other POCO classes, such as ‘MealDetail’ and ‘ActivityDetail’, are instantiated to hold data passed back to the client by the operations, in the Response message. These POCO instances are serialized to and deserialized from JSON or XML.
The WCF Service’s Configuration
The most complex and potentially the most confusing part of creating a WCF Service, at least for me, is always the service’s configuration. Due in part to the flexibility of WCF Services to accommodate many types of client, server, network, and security situations, the configuration of the services takes an in-depth understanding of bindings, behaviors, endpoints, security, and associated settings. The best books I’ve found on configuring WCF Services is Pro WCF 4: Practical Microsoft SOA Implementation, by Nishith Pathak. The book goes into great detail on all aspects of configuring WCF Services to meet your particular project’s needs.
Since we are only using the WCF Web HTTP Programming Model to build and expose our service, the ‘webHttpBinding’ binding is the only binding we need to configure. I have made an effort to strip out all the unnecessary boilerplate settings from our service’s configuration.
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<appSettings>
<add key="aspnet:UseTaskFriendlySynchronizationContext" value="true" />
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<system.serviceModel>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
<behaviors>
<endpointBehaviors>
<behavior name="webHttpBehavior">
<webHttp helpEnabled="true" defaultOutgoingResponseFormat="Json"
defaultBodyStyle="Bare" automaticFormatSelectionEnabled="true"/>
</behavior>
</endpointBehaviors>
</behaviors>
<services>
<service name="HealthTracker.WcfService.HealthTrackerWcfService">
<endpoint address="web" binding="webHttpBinding" behaviorConfiguration="webHttpBehavior"
contract="HealthTracker.WcfService.IHealthTrackerWcfService" />
</service>
</services>
</system.serviceModel>
<system.webServer>
<modules runAllManagedModulesForAllRequests="true" />
<directoryBrowse enabled="false" />
</system.webServer>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
</entityFramework>
<connectionStrings>
<add name="HealthTrackerEntities" connectionString="metadata=res://*/HealthTracker.csdl|res://*/HealthTracker.ssdl|res://*/HealthTracker.msl;provider=System.Data.SqlClient;provider connection string="data source=gstafford-windows-laptop\DEVELOPMENT;initial catalog=HealthTracker;persist security info=True;user id=DemoLogin;password=DemoLogin123;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
Some items to note in the configuration:
- Line 4: Entity Framework – The Entity Framework 5 reference you added earlier via NuGet.
- Line 18: Help – This enables an automatically generated Help page, displaying all the service’s operations for the endpoint, with details on how to call each operation.
- Lines 18-19: Request and Response Message Formats – Default settings for message format and body style of Request and Response messages. In this case, JSON and Bare. Setting defaults saves lots of time, not having to add attributes to each individual operation.
- Line 25-26: Endpoint – The service’s single endpoint, with a single binding and behavior. For this Post, we are only using the ‘webHttpBinding’ binding type.
- Line 38: Connection String – The SQL Server Connection String you copied from the previous Post’s Project. Required by the DAL Project Reference you added, earlier.
Deploying the Service to IIS
Now that the service is complete, we will deploy and host it in IIS. There are many options when it comes to creating and configuring a new website – setting up domain names, choosing ports, configuring firewalls, defining bindings, setting permissions, and so forth. This Post will not go into that level of detail. I will demonstrate how I chose to set up my website and publish my WCF Service.
We need a physical location to deploy the WCF Service’s contents. I recommend a location outside of the IIS root directory, such as ‘C:\HealthTrackerWfcService’. Create this folder on the server where you will be running IIS, either locally or remotely. This folder is where we will publish the service’s contents to from Visual Studio, next.
Create a new website in IIS to host the service. Name the site ‘HealthTracker’. You can configure and use a domain name or a specific port, from which to call the service. I chose to configure a domain name on my IIS Server, ”WcfService.HealthTracker.com’. If you are unsure how to setup a new domain name on your network, then a local, open port is probably easier for you. Pick any random port, like 15678.
Create New Website in IIS to Host Service
Publish the WCF Service to the deployment location, explained above, using Visual Studio 2012’s Web Project Publishing Tool. Exactly how and where you set-up your website, and any security considerations, will affect the configuration of the Publishing Tool’s Profile. My options will not necessarily work for your specific environment.
-
- Publish Web – Profile
-
- Publish Web – Connection
-
- Publish Web – Settings
-
- Publish Web – Preview
Testing the WCF Service
Congratulations, your service is deployed. Now, let’s see if it works. Before we test the individual operations, we will ensure the service is being hosted correctly. Open the service’s Help page. This page automatically shows details on all operations of a particular endpoint. The address should follow the convention of http://%5Byour_domain%5D:%5Byour_port%5D/%5Byour_service%5D/%5Byour_endpoint_address%5D/help. In my case ‘http://wcfservice.healthtracker.com/HealthTrackerWcfService.svc/web/help’. If this page displays, then the service is deployed correctly and it’s web endpoint is responding as expected.
WCF Service Help Page – Service Endpoint Operations
While on the Help page, click on any of the HTTP Methods to see a further explanation of that particular operation. This page is especially useful for copying the URL of the operation for use in Fiddler. It is even more useful for grabbing the sample JSON or XML Request messages. Just substitute your test values for the default values, in Fiddler. It saves a lot of typing and many potential errors.
WCF Service Help Page – Example Request Body
Fiddler
The easiest way to test each of the service’s operations is Fiddler. Download and install Fiddler, if you don’t already have it. Using Fiddler, construct a Request message and call the operations by executing the operation’s associated HTTP Method. Below is an example of calling the ‘InsertActivity’ operation. This CRUD operation accepts a new Activity object as an argument, inserts into the database via the Entity Data Model, and returns a Boolean value indicating success.
To call the ‘InsertActivity’ operation, 1) select the ‘POST’ HTTP method, 2) input the URL for the ‘InsertActivity’ operation, 3) select a version of HTTP (1.2), 4) input the Content-Type (JSON or XML) in the Request Headers section, 5) input the body of the Request, a new ‘Activity’ as JSON, in the Request Body section, and 6) select ‘Execute’. The 7) Response should appear in the Web Sessions window.
Fiddler Example – InsertActivity Operation Request
Executing the 1) Request (constructed above), should result in a 2) Response in the Web Sessions window. Double clicking on the Web Session should result in the display of the 3) Response message in the lower righthand window. The operation returns a Boolean indicating if the operation succeeded or failed. In this case, we received a value of ‘true’.
Fiddler Example – InsertActivity Operation Response
To view the Activity we just inserted, we need to call the ‘GetActivities’ operation, passing it the same ‘PersonId’ argument. In Fiddler, 1) select the ‘GET’ HTTP method, 2) input the URL for the ‘GetActivities’ operation including a value for the ‘PersonId’ argument, 3) select the desired version of HTTP (1.2), 4) input a Content-Type (JSON or XML) in the Request Headers section, and 5) select ‘Execute’. Same as before, the 6) Response should appear in the Web Sessions window. This time there is no Request body content.
Fiddler Example – GetActivities Operation Request
As before, executing the 1) Request should result in a 2) Response in the Web Sessions window. Doubling clicking on the Web Session should result in the display of the 3) Response in the lower left window. This method returns a JSON payload with each Activity, associated with the PersonId argument.
Fiddler Example – GetActivities Operation Response
You can use this same process to test all the other operations at the WCF Service’s endpoint. You can also save the Request message or complete Web Sessions in Fiddler should you need to re-test.
Conclusion
We now have a WCF Service deployed and running in IIS, and tested. The service’s operations can be called from any application capable of making an HTTP call. Thank you for taking the time to read this Post. I hope you found it beneficial.
Database First Development with Entity Framework 5 in Visual Studio 2012
Posted by Gary A. Stafford in .NET Development, Software Development on November 22, 2012
Build and test a Data Access Layer (DAL) using Entity Framework 5 and Database First Development in Visual Studio 2012. Use the Entity Framework Designer to build an ADO.NET Entity Data Model containing database tables, views, stored procedures, and scalar-valued functions. An updated version of this project’s source code, using EF6 is now available on GitHub. The GitHub repository contains all three Entity Framework blog posts.
Introduction
In the last post, we explored Microsoft’s new Entity Framework 5 with Code First Development. In this post, we will explore Entity Framework 5 with Database First Development. We will be using the same data model as before. However, this time instead of POCOs, we will start with a SQL Server 2008 R2 database and use the Entity Framework Designer to build an ADO.NET Entity Data Model (EDM). In addition to database tables, we will look at Entity Framework’s ability to support database views (virtual tables), stored procedures, and scalar-valued functions.
Download a complete copy of the post’s source code, with SQL scripts to create the database objects and populate the database with sample data, from DropBox.
Entity Framework’s Code First and Model First development offer many great options for .NET developers. However, in my experience, most enterprise-level application developers work with a Database First Development model. Using Database First Development, Entity Framework 5 (EF5) provides the ability to construct a powerful yet easy-to-implement data access layer (DAL) between the database and the business logic.
The steps involved in this example are as follows:
- Create the new SQL Server database;
- Create the database objects;
- Create a new C# Class Library Project in Visual Studio 2012 Solution;
- Add a new ADO.NET Entity Data Model to project;
- Create a new Database Connection;
- Import the database objects into the EDM;
- Modify the EDM to accommodate the scalar-valued functions;
- Populate the database with sample data;
- Validate the EDM using a Unit Test Project;
Below is a final view of the entire Solution for reference as you work through the post.
Solution Explorer View of Final Solution
The Database
Using SQL Server 2008 R2 Management Studio (SSMS), Toad for SQL, or similar application, create a new database, named ‘HealthTracker’. I left all the default database settings unchanged for this post.
Create the New Health Tracker Database
Next, execute the supplied sql script to populate the HealthTracker database with the necessary database objects. The script should insert the following objects: (6) tables, (1) view, (1) stored procedure, (3) scalar-valued functions, and all the necessary table relationships. All objects will be members of the default ‘dbo’ schema.
Database in SQL Server Object Viewer
Barring a few minor changes, this data model is identical to the one we built in the last post using Code First Development with POCOs. The below Database Diagram illustrates the one-to-many relationships between the tables. The tables are pluralized in the database, as opposed to singular in the ADO.NET Entity Data Model (Meals vs. Meal, People vs. Person, etc.). This is a common pattern with Entity Framework.
Database Diagram of Table Relationships
Optional: Setting Up Database Credentials
For security and simplicity, I choose to add a new Login, User, and Role to the database. This step is not necessary for this post. However, it is good to get into the habit of securing your database, using database Logins, Users, Roles, and Permissions. In addition, if you are planning to deploy the database and the DAL to other environments such as Test or Production, don’t tie your Solution to personal credentials, a machine-specific account, or to an administrative role in the database with overly broad permissions.
The Database User, DemoUser, is associated with the Login, DemoLogin. DemoUser is a member of the Database Role, DemoRole. I will use the DemoLogin account to connect the EDM to the database. DemoRole has the minimal required database permissions the Entity will need to function: Alter, Insert, Delete, Execute, Select, Update, and View definition. DemoUser only needs Connect permission. Again, this step is optional. You can use your own credentials if you choose.
Included with the downloadable code is a third sql script that should create the User, Role, Login, and required Permissions, if you choose to use them to follow along with the post.
Database Permissions for User and Role
The Data Access Layer
Following good software design principles, we will separate our concerns between Projects. We want to create a Data Access Layer (DAL), to act as an interface between our database and our business logic. We don’t want to interact with the data directly in our DAL Project. By separating the DAL into its own project, we can reference that project’s assembly (.dll) from any other project, be it another class library (our business logic), a WCF service, WPF, Silverlight or console application, or an ASP.NET site. To start, create a new Visual C# Class Library. Name it ‘HealthTracker.DataAccess.DbFirst’. Create a new Solution for the Project in the same dialog box, named ‘HealthTracker’.
New Visual C# Windows Class Library Project
First, install Entity Framework (System.Data.Entity namespace classes) into the Solution by right clicking on the Solution and selecting ‘Manage NuGet Packages for Solution…’. Install the ‘EntityFramework’ package. If you haven’t discovered the power of NuGet with Visual Studio, check out their site.
Manage NuGet Packages – Install EntityFramework Package
Next, add a new ‘ADO.NET Entity Data Model’ item, named ‘HealthTracker.edmx’, to the HealthTracker.DataAccess.DbFirst project. According to Microsoft, an .edmx file also contains information used by the ADO.NET Entity Data Model Designer (Entity Designer) to render a model graphically. An .edmx file is the combination of three metadata files: the conceptual schema definition language (CSDL), store schema definition language (SSDL), and mapping specification language (MSL) files. For more information, see .edmx File Overview (Entity Framework).
Adding the ADO.NET Entity Data Model to Project
Adding the ADO.NET Entity Data Model item will start the Entity Data Model Wizard. Since we are exploring Database First Development, select ‘Generate from Database’.
Entity Data Model Wizard – Generate from Database
Next, we will be prompted to choose a data connection. Since this is the first time we are accessing our newly created HealthTracker database, we need to create a new data connection. Select ‘New Connection…’
The options you chose in the ‘Connection Properties’ dialog window, such as the server and instance name, will depend on your own SQL Server configuration and the method you chose to log onto the server. As mentioned before, I will use the ‘DemoLogin’ account. The connection string will reside in the project’s app.config file. Make sure to always chose ‘Test Connection’ to verify you have configured the Data Connection properly.
New Connection – Database Connection Properties
Once the data connection is established, we are prompted to add the database objects to the EDM. Only add the objects that we created earlier with the sql script.
Entity Data Model Wizard – Choose Your Database Objects
When the import is complete, the EDM should look like the following in the Entity Designer. You should see the six table entities, with one-to-many associations between them, as well as the one view entity, ‘PersonSummaryView’. Each database object you imported is referred to as an entity. Drag the entities into any position you want on the Design surface.
HealthTracker Entity Data Model Diagram
Similarly, when the import is complete, the EDM should look like the following in the Model Browser.
Model Browser – View of the Entity Data Model
Stored Procedures
You recall we imported a stored procedure, ‘GetPersonSummary’. What happened to that object? In the Model Browser, double-click on the GetPersonSummary item under the Function Imports. The stored procedure was imported into the EDM by EF. The results the procedure returns from the database is associated with a new complex object type, ‘GetPersonSummary_Result’.
Function Import – Stored Procedure
Scalar Functions
If you view the sql code for the above stored procedure, ‘GetPersonSummary’, you will note it calls three scalar-valued functions. These three happen to be the three functions we imported into the EDM. Each function takes a single input parameter, ‘personId’, and returns an integer value – the count of Meals, Activities, and Hydrations for a that Person, based on their Id.
We can also call the scalar-valued functions directly. Unfortunately, in my experience, working scalar-valued functions in Entity Framework is still not as easy as tables, views, and stored procedures. I have found two methods to work with scalar-valued functions. The first method is a bit of hack in my opinion, but it works. The method is documented in several Internet posts, including this one on Stack Overflow.
This method requires some minor changes of the .edmx file’s xml, directly. To do so, right-click on the .edmx file and select ‘Open With…’, ‘XML (Text) Editor’. This is how the functions looks in the .edmx file before changes:
<Function Name="CountActivities" ReturnType="int" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<Parameter Name="personId" Type="int" Mode="In" />
</Function>
<Function Name="CountHydrations" ReturnType="int" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<Parameter Name="personId" Type="int" Mode="In" />
</Function>
<Function Name="CountMeals" ReturnType="int" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<Parameter Name="personId" Type="int" Mode="In" />
</Function>
Remove the ‘ReturnType’ attribute from the <Function /> element. Then, add a <CommandText /> element to each of the <Function /> elements. See the modified .edmx file below for the contents of the <CommandText /> elements.
<Function Name="CountActivities" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<CommandText>
SELECT [dbo].[CountActivities] (@personId)
</CommandText>
<Parameter Name="personId" Type="int" Mode="In" />
</Function>
<Function Name="CountHydrations" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<CommandText>
SELECT [dbo].[CountHydrations] (@personId)
</CommandText>
<Parameter Name="personId" Type="int" Mode="In" />
</Function>
<Function Name="CountMeals" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<CommandText>
SELECT [dbo].[CountMeals] (@personId)
</CommandText>
<Parameter Name="personId" Type="int" Mode="In" />
</Function>
Next, in the Model Browser, right-click on the ‘Function Imports’ folder and select ‘Add Function Import…’ This brings up the ‘Add Function Import’ dialog window. We will import the ‘CountActivities’ scalar-valued function to show this method. Enter the following information in the dialog window and select Save:
Function Import – Scalar-valued Function
You can do the same for the other two scalar-valued functions, if you choose. We will only test the ‘CountActivities’ function, next with our Unit Tests. The downside of this method is the edits to the .edmx file will be lost when you update the EDM from the database. You will have to re-edit the .edmx file each time. This not a great solution.
The second method to call a scalar-valued function uses a feature of Entity Framework 5, the ‘Database.SqlQuery Method (String, Object[])’ Method. According to Microsoft, an instance of this class is obtained from an DbContext object and can be used to manage the actual database backing a DbContext or connection. Using ‘SqlQuery’ method, a raw SQL query that will return elements of the given generic type is created. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type.
Below is an example of the method’s use, similar to code in the Unit Test Project we will create next, to test our EDM.
using (HealthTrackerEntities context = new HealthTrackerEntities())
{
string sqlQuery = "SELECT [dbo].[CountMeals] ({0})";
Object[] parameters = { 1 };
int activityCount = db.Database.SqlQuery<int>(sqlQuery, parameters).FirstOrDefault();
}
This method allows us to call the scalar-valued function directly from the database, just as we could any other object using a sql query. The downside of this method is that we are not really taking advantage of the EDM we constructed. It is easier than the first method and it doesn’t need continued changes if we update the EDM. Undoubtedly, there are better methods out there than I have presented here.
Testing the Entity Data Model
To confirm that the EDM is functioning properly, we will create and execute a series of Unit Tests. In reality, although we will be using Visual Studio’s Unit Test Project type, the tests are more like functional tests than true unit tests. This is especially true because we are writing the tests after we have completed development our DAL’s EDM.
We will perform minimal testing of the EDM’s tables, view, stored procedure, and scalar-valued functions, with a series of several simple tests. The tests are only meant to demonstrate the type of tests you could use across all entities in the Model to confirm various functions.
Sample Data
In SSMS or VS2012, execute the supplied sql script that populates the database with test data. The script contains a variety of Meal Types, Activity Types, People, Meals, Activities, and Hydrations table records. Note the script deletes all existing data from those tables. Below is a sample of the Meal table’s sample data.
Sample Meal Data
The Unit Test Project
After adding the sample data to the HealthTracker database, add a new Visual Studio 2012 Unit Test Project, named ‘HealthTracker.UnitTests’, to the ‘HealthTracker’ Solution.
Add New Unit Test Project to Solution
Next, add a Reference to the Unit Test Project from our DAL, the ‘HealthTracker.DataAccess.DbFirst’ Project. This step adds the ‘HealthTracker.DataAccess.DbFirst.dll’ assembly to our Unit Test Project.
Add Entity Data Model Project Reference to Unit Test Project
Next, we need to add the same Database Connection we used in the ‘HealthTracker.DataAccess.DbFirst’ Project, to this Project. I always forget this step and end up with a database connection error the first time I try to run a new project. Right-click on the Unit Test Project and select ‘Add New Item…’ Add an ‘Application Configuration File’ item, named ‘app.config’, to the Unit Test Project.
Add Application Configuration File to Unit Test Project
Open the corresponding Application Configuration File in the ‘HealthTracker.DataAccess.DbFirst’ Project and copy the <connectionStrings /> element to our Unit Test Project’s app.config file. The file’s contents should look similar to the following when complete (note, your ‘connectionString’ attribute will have different values).
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="HealthTrackerEntities" connectionString="metadata=res://*/HealthTracker.csdl|res://*/HealthTracker.ssdl|res://*/HealthTracker.msl;provider=System.Data.SqlClient;provider connection string="data source=gstafford-windows-laptop\DEVELOPMENT;initial catalog=HealthTracker;persist security info=True;user id=DemoLogin;password=DemoLogin123;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
Lastly, rename the default ‘UnitTest’ class in the Unit Test Project to ‘HealthTrackerUnitTests’. Enter or copy and paste the contents of the supplied HealthTrackerUnitTests.cs file to this file. The supplied file contains all the unit tests.
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using HealthTracker.DataAccess.DbFirst;
namespace HealthTracker.UnitTests
{
[TestClass]
public class HealthTrackerUnitTests
{
private const string PersonOriginal = "John Doe";
private const string PersonNew = "New Person";
private const string PersonNameUpdated = "Updated Name";
/// <summary>
/// Delete any non-sample People from the database created by previous tests
/// </summary>
[TestInitialize]
public void RemoveNonSamplePeople()
{
using (var db = new HealthTrackerEntities())
{
var peopleToDelete = db.People
.Where(person => person.PersonId > 4);
foreach (var personToDelete in peopleToDelete)
{
db.People.Remove(personToDelete);
}
db.SaveChanges();
}
}
/// <summary>
/// Return the count of People in the database, which should be 4.
/// </summary>
[TestMethod]
public void PersonCountTest()
{
using (var db = new HealthTrackerEntities())
{
var personCount = (db.People.Select(p => p)).Count();
Assert.IsTrue(personCount > 0);
}
}
/// <summary>
/// Return the PersonId of 'John Doe', which should be is 1.
/// </summary>
[TestMethod]
public void PersonIdTest()
{
using (var db = new HealthTrackerEntities())
{
var personId = db.People
.Where(person => person.Name == PersonOriginal)
.Select(person => person.PersonId)
.First();
Assert.AreEqual(1, personId);
}
}
/// <summary>
/// Insert a new Person into the database.
/// </summary>
[TestMethod]
public void PersonAddNewTest()
{
using (var db = new HealthTrackerEntities())
{
// Setup test
db.People.Add(new Person { Name = PersonNew });
db.SaveChanges();
// Test 1
var personCount = (db.People.Select(p => p)).Count();
Assert.AreEqual(5, personCount);
// Test 2
var newPersonFound = db.People.FirstOrDefault(
person => person.Name == PersonNew);
Assert.IsNotNull(newPersonFound);
}
}
/// <summary>
/// Update a Person's name in the database.
/// </summary>
[TestMethod]
public void PersonUpdateNameTest()
{
using (var db = new HealthTrackerEntities())
{
// Setup test
var personToUpdate = db.People.FirstOrDefault(
person => person.Name == PersonOriginal);
if (personToUpdate != null) personToUpdate.Name = PersonNameUpdated;
db.SaveChanges();
// Test
var updatedPerson = db.People.FirstOrDefault(
person => person.Name == PersonNameUpdated);
Assert.IsNotNull(updatedPerson);
// Tear down test
var personToRevert = db.People.FirstOrDefault(
person => person.Name == PersonNameUpdated);
if (personToRevert != null) personToRevert.Name = PersonOriginal;
db.SaveChanges();
}
}
/// <summary>
/// Return the Meal count from PersonSummaryViews database view, which should be 21.
/// </summary>
[TestMethod]
public void PersonSummaryViewTest()
{
using (var db = new HealthTrackerEntities())
{
var mealCount = (db.PersonSummaryViews
.Where(p => p.PersonId == 1)
.Select(p => p.MealsCount))
.First();
Assert.AreEqual(21, mealCount);
}
}
/// <summary>
/// Call CountActivities scalar-valued function directly from in the database.
/// </summary>
[TestMethod]
public void ActivtyCountFunctionFromDatabaseTest()
{
using (var db = new HealthTrackerEntities())
{
object[] parameters = { 1 };
var activityCount = db.Database.SqlQuery<int>(
"SELECT [dbo].[CountActivities] ({0})",
parameters).FirstOrDefault();
Assert.AreEqual(7, activityCount);
}
}
/// <summary>
/// Call CountActivities scalar-valued function from the Entity Data Model.
/// </summary>
[TestMethod]
public void ActivtyCountFunctionFromEntityTest()
{
using (var db = new HealthTrackerEntities())
{
var activityCount = db.CountActivities(1).First();
if (activityCount != null) activityCount = activityCount.Value;
Assert.AreEqual(7, activityCount);
}
}
}
}
When complete, build the Solution. Then finally, from the Test menu in the top Visual Studio menu bar, or by right clicking on the ‘HealthTrackerUnitTests’, run all Unit Tests. The test results should look like the following.
Test Explorer Showing Results of Unit Tests
Conclusion
Congratulations, we have built and tested a Data Access Layer using Entity Framework 5. The DAL can now be referenced from a middle-tier business assemble, WCF Service, or directly from a client application.
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 = " ";
}
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 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
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
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
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.
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.
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:
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).
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,
- Create an entity class from the Adventure Works database using EclipseLink;
- Create a Jersey-specific RESTful web service using the entity class using Jersey and JAXB;
- Add a new method to service, which leverages Jersey and Jackson’s abilities to return JSONP;
- Deploy the RESTful web service to a remote instance of GlassFish, using Apache Ant;
- Test the RESTful web service using cURL.
- In a new RESTful web service client web application project,
- 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.
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)
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.
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.
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.
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.
Choice of Database Tables and Views from Human Resources Schema
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’.
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.
Select the ‘Fully Qualified Database Table Names’ Mapping Options
Click finished. You have successfully created the SQL Server data source and entity classes.
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.
Fix the Entity Class’s Missing Primary Key (Entity ID)
Fix the Entity Class’s Missing Primary Key (Entity ID)
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’.
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’.
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
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.
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.
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.
Test the RESTful Web Service Locally in NetBeans (XML Response Shown)
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.
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.
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.
Choose the Web Application Project-Type in NetBeans
Provide a Project Name and Location
Select the Target GlassFish Server and Domain Where the Project Will be Installed
Include any Frameworks You Will Use with the Project
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.
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…’.
Database Tables View Before Data Source is Created
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.
Locate the Microsoft JDBC Driver 4.0 for SQL Server Driver .jar File
On the next screen, ‘Customize the Connection’, input the required SQL Server information. The host is the machine your instance of SQL Server is installed on, such as ‘localhost’. The instance is the name of the SQL Server instance in which the Adventure Works database is installed, such as ‘Development’. Once you complete the form, click ‘Test Connection’. If it doesn’t succeed, check your settings, again.
Provide the SQL Server Adventure Works Database Connection Information
As I mentioned in an earlier post, the SQL Server Data Source forces you to select a single database schema. On the ‘Choose Database Schema’ screen, select the ‘HumanResources’ schema. The database tables you will be able to reference from you entity classes are limited to just this schema, when using this data source. To reference other schemas, you will need to create more data sources.
Select the ‘HumanResources’ Database Schema
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.
Retrieving all the ‘HumanResources’ Schema Database Tables and Views
All the ‘HumanResources’ Schema Database Tables and Views Available
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’.
Provide the Name of the Package Where the Entity Classes Will be Created
On the next screen, ‘Mapping Options’, choose ‘Fully Qualified Database Table Names’. Without this option selected, I have had problems trying to make the RESTful web services function properly. This is also the reason I chose to create the entity classes first, and then create the RESTful web services, separately. NetBeans has an option that combines these two tasks into a single step, by choosing ‘RESTful Web Services from Database’. However, the ‘Fully Qualified Database Table Names’ option is not available on the equivalent screen, using that process (at least in my version of NetBeans 7.2). I prefer the two-step approach.
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 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.
persistence.xml Design View
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 the New RESTful Web Service from Four Entity Classes
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.
Provide a Package Location to Place the RESTful Web Service Classes Into
Click finished. You have successfully created the RESTful web services.
View of Web Application Project with Entity Classes and RESTful Web Services Added
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.
NetBeans Highlights the Entity Id Error in Two View-Based Entity Classes
Select the ‘businessEntityID’ Field as the Primary Key
View of Entity Class with ‘@Id’ Annotation Added
Test RESTful Web Services Locally in NetBeans
NetBeans provides an easy way to test the RESTful web services, locally. Right-click on the ‘RESTful Web Services’ project folder within the main project, and select ‘Test RESTful Web Services’. Select the first option, ‘Locally Generated Test Client’, in the ‘Configure REST Test Client’ pop-up window.
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.
You May Get an ActiveX Warning When Using IE to Test the RESTful Web Services
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.
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.
A Single Employee Being Successfully Retrieved from the Adventure Works Database Using Input Parameter
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’.
Default View of GlassFish Domain Prior to Deploying the New Web Application
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.
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.
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.
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.
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.
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.
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, andCallableStatement - 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.
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();
}
}
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.
Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 3/3
Posted by Gary A. Stafford in .NET Development, Software Development, SQL Server Development, Team Foundation Server (TFS) Development on August 8, 2012
Objectives of 3-Part Series:
Part I: Setting up the Example Database and Visual Studio Projects
- Setup and configure a new instance of SQL Server 2008 R2
- Setup and configure a copy of Microsoft’s Adventure Works database
- Create and configure both a Visual Studio 2010 server project and Visual Studio 2010 database project
- Test the project’s ability to deploy changes to the database
Part II: Converting the Visual Studio 2010 Database and Server Projects to SSDT
- Convert the Adventure Works Visual Studio 2010 database and server projects to SSDT projects
- Create a second Solution configuration and SSDT publish profile for an additional database environment
- Test the converted database project’s ability to publish changes to multiple database environments
Part III: Automate the Building and Publishing of the SSDT Database Project Using Jenkins
- Automate the build and delivery of a sql change script artifact, for any database environment, to a designated release location using a parameterized build.
- Automate the build and publishing of the SSDT project’s changes directly to any database environment using a parameterized build.
Part III: Automate the Building and Publishing of the SSDT Database Project Using Jenkins
In this last post we will use Jenkins to publishing of changes from the Adventure Works SSDT database project to the Adventure Works database. Jenkins, formally Hudson, is the industry-standard, java-based open-source continuous integration server.
Jenkins
If you are unfamiliar with Jenkins, I recommend an earlier post, Automated Deployment to GlassFish Using Jenkins and Ant. That post goes into detail on Jenkins and its associated plug-in architecture. Jenkins’ website provides excellent resources for installing and configuring Jenkins on Windows. For this post, I’ll assume that you have Jenkins installed and running as a Windows Service.
The latest available version of Jenkins, at the time of this post is 1.476. To follow along with the post, you will need to install and configure the following (4) plug-ins:
- Apache Ant Plug-in
- Jenkins MSBuild Plug-in
- Jenkins Artifact Deployer Plug-in
- Jenkins Email Extension Plug-in
User Authentication
In the first two posts, we connected to the Adventure Works database with the ‘aw_dev’ SQL Server user account, using SQL Authentication. This account was used to perform schema comparisons and publish changes from the Visual Studio project. Although SQL Authentication is an acceptable means of accessing SQL Server, Windows Authentication is more common in corporate and enterprise software environments, especially where Microsoft’s Active Directory is used. Windows Authentication with Active Directory (AD) provides an easier, centralized user account security model. It is considered more secure.
With Windows Authentication, we associate a SQL Server Login with an existing Windows user account. The account may be local to the SQL Server or part of an Active Directory domain. For this post, instead using SQL Authentication, passing the ‘aw_dev’ user’s credentials to SQL Server in database project’s connection strings, we will switch to Windows Authentication. Using Windows Authentication will allow Jenkins to connect directly to SQL Server.
Setting up the Jenkins Windows User Account
Let’s outline the process of creating a Jenkins Windows user account and using Windows Authentication with our Adventure Works project:
- Create a new ‘jenkins’ Windows user account.
- Change the Jenkins Windows service Log On account to the ‘jenkins’ Windows account.
- Create a new ‘jenkins’ SQL Server Login, associated with the ‘jenkins’ Windows user account, using Windows Authentication.
- Provide privileges in SQL Server to the ‘jenkins’ user identical to the ‘aw_dev’ user.
- Change the connection strings in the publishing profiles to use Windows Authentication.
First, create the ‘jenkins’ Windows user account on the computer where you have SQL Server and Jenkins installed. If they are on separate computers, then you will need to install the account on both computers, or use Active Directory. For this demonstration, I have both SQL Server and Jenkins installed on the same computer. I gave the ‘jenkins’ user administrative-level rights on my machine, by assigning it to the Administrators group.
Create New Jenkins User
Next, change the ‘Log On’ Windows user account for the Jenkins Windows service to the ‘jenkins’ Windows user account. Restart the Jenkins Windows service to apply the change. If the service fails to restart, it is likely you did not give enough rights to the user. I suggest adding the user to the Administrators group, to check if the problem you have encountering is permissions-related.
Jenkins Windows Service
Set Log On Account for Jenkins Windows Service
Log On Account for Jenkins Windows Service
Log On Account for Jenkins Windows Service Granted
Setting up the Jenkins SQL Server Login
Next, to use Windows Authentication with SQL Server, create a new ‘jenkins’ Login for the Production instance of SQL Server and it with the ‘jenkins’ Windows user account. Replicate the ‘aw_dev’ SQL user’s various permissions for the ‘jenkins’ user. The ‘jenkins’ account will be performing similar tasks to ‘aw_dev’, but this time initiated by Jenkins, not Visual Studio. Repeat this process for the Development instance of SQL Server.
Jenkins Login Added to Development Instance
Jenkins User Any Definition on Production Instance
Jenkins User View Definition on Production Instance Database
SSMS View of Jenkins User
Windows Authentication with the Publishing Profile
In Visual Studio, switch the connection strings in the Development and Production publishing profiles in both the server project and database projects to Windows Authentication with Integrated Security. They should look similar to the code below. Substitute your server name and SQL instance for each profile.
Data Source=[SERVER NAME]\[INSTANCE NAME];Integrated Security=True;Pooling=False
Important note here, once you switch the profile’s connection string to Windows Authentication, the Windows user account that you logged into your computer with, is the account that Visual Studio will now user to connect to the database. Make sure your Windows user account has at least the same level of permissions as the ‘aw_dev’ and ‘jenkins’ accounts. As a developer, you would likely have greater permissions than these two accounts.
Configuring Jenkins for Delivery of Script to Release
In many production environments, delivering or ‘turning over’ release-ready code to another team for deployment, as opposed to deploying the code directly, is common practice. A developer starts or ‘kicks off a build’ of the job in Jenkins, which generates artifact(s). Artifacts are usually logical collections of deployable code and other associated components and files, constituting the application being built. Artifacts are often separated by type, such as database, web, Windows services, web services, configuration files, and so forth. Each type may be deployed by a different team or to a different location. Our project will only have one artifact to deliver, the sql change script.
This first Jenkins job we create will just generate the change script, which will then be delivered to a specific remote location for later release. We start by creating what Jenkins refers to as a parameterized build job. It allows us to pass parameters to each build of our job. We pass the name of the configuration (same as our environment name) we want our build to target. With this single parameter, ‘TARGET_ENVIRONMENT’, we can use a single Jenkins job to target any environment we have configured by simply passing its name to the build; a very powerful, time-saving feature of Jenkins.
Step 1 – Parameterized Build Parameter
Let’s outline the steps we will configure our Jenkins job with, to deliver a change script for release:
- Copy the Solution from its current location to the Jenkins job’s workspace.
- Accept the target environment as a parameterized build parameter (ex. ‘Production’ or ‘Development’).
- Build the database project and its dependencies based on the environment parameter.
- Generate the sql change script based on the environment parameter.
- Compress and name the sql change script based on the environment parameter and build id.
- Deliver the compressed script artifact to a designated release location for deployment.
- Notify release team that the artifact is ready for release.
- Archive the build’s artifact(s).
Copy the Solution to Jenkins
I am not using a revision control system, such as TFS or Subversion, for our example. The Adventure Works Solution resides in a file directory, on my development machine. To copy the entire Solution from its current location into job’s workspace, we add a step in the Jenkins job to execute a simple xcopy command. With source control, you would replace the xcopy step with a similar step to retrieve the project from a specific branch)within the revision control system, using one of many Jenkins’ revision control plug-ins.
Step 2 – Copy Solution to Jenkins Workspace
echo 'Copying Adventure Works Solution to Jenkins workspace...' xcopy "[Path to your Project]\AdventureWorks2008" "%WORKSPACE%" /S /E /H /Y /R /EXCLUDE:[Path to exclude file]\[name of exclude file].txt echo 'Deleting artifacts from previous builds...' del "%WORKSPACE%\*_publish.zip" /F /Q
Excluding Solution files from Jenkins job’s workspace that are unnecessary for the job to succeed is good practice. Excluding files saves time during the xcopy and can make troubleshooting build problems easier. To exclude unneeded Solution files, use the xcopy command’s ‘exclude’ parameter. To use exclude, we must first create an exclude text file, listing the directories we don’t need copied, and call it using with the exclude parameter with the xcopy command. Make sure to change the path shown above to reflect the location and name of your exclude file. Here is a list of the directories I chose to exclude. They are either unused by the build, or created as part of the build, for example the sql directories and there subdirectories.
\AdventureWorks2008\sql\ \AdventureWorks2008\Sandbox\ \AdventureWorks2008\_ConversionReport_Files\ \Development\sql\ \Development\Sandbox\ \Development\_ConversionReport_Files\
Build the Solution with Jenkins
Once the Solution’s files are copied into the Jenkins job’s workspace, we perform a build of the database project with an MSBuild build step, using the Jenkins MSBuild Plug-in. Jenkins executes the same MSBuild command Visual Studio would execute to build the project. Jenkins calls MSBuild, which in turn calls the MSBuild ‘Build’ target with parameters that specify the Solution configuration and platform to target.
Generate the Script with Jenkins
After Building the database project, in the same step as the build, we perform a publish of the database project. MSBuild calls the new SSDT’s ‘Publish’ target with parameters that specify the Solution configuration, target platform, publishing profile to use, and whether to only generate a sql change script, or publish the project’s changes directly to the database. In this first example, we are only generating a script. Note the use of the build parameter (%TARGET_ENVIRONMENT%) and environmental variables (%WORKSPACE%) in the MSBuild command. Again, a very powerful feature of Jenkins.
Step 3 – Build and Publish Project
"%WORKSPACE%\AdventureWorks2008\AdventureWorks2008.sqlproj" /p:Configuration=%TARGET_ENVIRONMENT% /p:Platform=AnyCPU /t:Build;Publish /p:SqlPublishProfilePath="%WORKSPACE%\AdventureWorks2008\%TARGET_ENVIRONMENT%.publish.xml" /p:UpdateDatabase=False
Compressing Artifacts with Apache Ant
To streamline the delivery, we will add a step to compress the change script using Jenkins Apache Ant Plug-in. Many consider Ant strictly a build tool for Java development. To the contrary, there are many tasks that can be automated for .NET developers with Ant. One particularly nice feature of Ant is its built-in support for zip compression.
Step 4 – Invoke Ant to Compress Artifact
configuration=$TARGET_ENVIRONMENT buildNo=$BUILD_NUMBER
The Ant plug-in calls Ant, which in turn calls an Ant buildfile, passing it the properties we give. First, create an Ant buildfile with a single task to zip the change script. To avoid confusion during release, Ant will also append the configuration name and unique Jenkins job build number to the filename. For example, ‘AdventureWorks.publish.sql’ becomes ‘AdventureWorks_Production_123_publish.zip’. This is accomplished by passing the configuration name (Jenkins parameterized build parameter) and the build number (Jenkins environmental variable), as parameters to the buildfile (shown above). The parameters, in the form of key-value-pairs, are treated as properties within the buildfile. Using Ant to zip and name the script literally took us one line of Ant code. The contents of the build.xml buildfile is shown below.
<?xml version="1.0" encoding="utf-8"?>
<project name="AdventureWorks2008" basedir="." default="default">
<description>SSDT Database Project Type ZIP Example</description>
<!-- Example configuration ant call with parameter:
ant -Dconfiguration=Development -DbuildNo=123 -->
<target name="default" description="ZIP sql deployment script">
<echo>$${basedir}=${basedir}</echo>
<echo>$${configuration}=${configuration}</echo>
<echo>$${buildNo}=${buildNo}</echo>
<zip basedir="AdventureWorks2008/sql/${configuration}"
destfile="AdventureWorks_${configuration}_${buildNo}_publish.zip"
includes="*.publish.sql" />
</target>
</project>
Delivery of Artifacts
Lastly, we add a step to deliver the zipped script artifact to a ‘release’ location. Ideally, another team would retrieve and execute the change script against the database. Delivering the artifact to a remote location is easily accomplished using the Jenkins Artifact Deployer Plug-in. First, if it doesn’t already exist, create the location where you will deliver the scripts. Then, ensure Jenkins has permission to manage the location’s contents. In this example, the ‘release’ location is a shared folder I created. In order for Jenkins to access the ‘release’ location, give the ‘jenkins’ Windows user Read/Write (Change) permissions to the shared folder. With the deployment plug-in, you also have the option to delete the previous artifact(s) each time there is a new deployment, or leave them to accumulate.
Sharing Folder for Released Artifacts
Jenkins User Permissions for Shared Folder
Permissions for Shared Folder
Step 5 – Deploy Artifact to Release Location
Multiple Zipped Artifacts in Release Folder
Email Notification
Lastly, we want to alert the right team that artifacts have been turned-over for release. There are many plug-ins Jenkins to communicate with end-users or other system. We will use the Jenkins Email Extension Plug-in to email the release team. Configuring dynamic messages to include the parameterized build parameters and Jenkins’ environmental variables is easy with this plug-in. My sample message includes several variables in the body of the message, including target environment, target database, artifact name, and Jenkins build URL.
I had some trouble passing the Jenkins’ parameterized build parameter (‘TARGET_ENVIRONMENT’) to the email plug-in, until I found this post. The format required by the plug-in for the type of variable is a bit obscure as compared to Ant, MSBuild, or other plug-ins.
Artifact: AdventureWorks_${ENV,var="TARGET_ENVIRONMENT"}_${BUILD_NUMBER}_publish.zip
Environment: ${ENV,var="TARGET_ENVIRONMENT"}
Database: AdventureWorks
Jenkins Build URL: ${BUILD_URL}
Please contact Development for questions or problems regarding this release.
Release Request Notification Email Message
Publishing Directly to the Database
As the last demonstration in this series of posts, we will publish the project changes directly to the database. Good news, we have done 95% of the work already. We merely need to copy the Jenkins job we already created, change one step, remove three others steps, and we’re publishing! Start by creating a new Jenkins job by copying the existing script delivery job. Next, drop the Invoke Ant, Artifact Deployer, and Archive Artifacts steps from the job’s configuration. Lastly, set the last parameter of the MSBuild task, ‘UpdateDatabase’, to True from False. That’s it! Instead of creating the script, compressing it, and sending it to a location to be executed later, the changes are generated and applied to the database in a single step.
Hybrid Solution
If you are not comfortable with the direct approach, there is a middle ground between only generating a script and publishing directly to the database. You can keep a record of the changes made to the database as part of publishing. To do so, change the ‘UpdateDatabase’ parameter to True, and only drop the Artifact Deployer step; leave the Invoke Ant and Archive Artifacts steps. The resulting job generates the change script, publishes the changes to the database, and compresses and archives the script. You now have a record of the changes made to the database.
Conclusion
In this last of three posts we demonstrated the use of Jenkins and its plug-ins to created three jobs, representing three possible SSDT publishing workflows. Using the parameterized build feature of Jenkins, each job capable of being executed against any database environment that we have a configuration and publishing profile defined for. Hopefully, one of these three workflows may fit your particular release methodology.
Jenkins SSDT Jobs View
Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 2/3
Posted by Gary A. Stafford in .NET Development, Software Development, SQL Server Development, Team Foundation Server (TFS) Development on August 1, 2012
Objectives of 3-Part Series:
Part I: Setting up the Example Database and Visual Studio Projects
- Setup and configure a new instance of SQL Server 2008 R2
- Setup and configure a copy of Microsoft’s Adventure Works database
- Create and configure both a Visual Studio 2010 server project and Visual Studio 2010 database project
- Test the project’s ability to deploy changes to the database
Part II: Converting the Visual Studio 2010 Database and Server Projects to SSDT
- Convert the Adventure Works Visual Studio 2010 database and server projects to SSDT projects
- Create a second Solution configuration and SSDT publish profile for an additional database environment
- Test the converted database project’s ability to publish changes to multiple database environments
Part III: Automate the Building and Publishing of the SSDT Database Project Using Jenkins
- Automate the build and delivery of a sql change script artifact, for any database environment, to a designated release location using a parameterized build.
- Automate the build and publishing of the SSDT project’s changes directly to any database environment using a parameterized build.
Part II: Converting the Visual Studio 2010 Database and Server Projects to SSDT
Picking up where part one of this three-part series left off, we are ready to convert the Adventure Works VS 2010 database project and associated server project to the new SSDT project type. Once converted, we will create an additional Solution configuration for Production. Finally, we will publish (vs. deploy) changes to database project’s schema to both the Development and Production environments. Note that Microsoft refers to the new format as either SSDT project type or SQL Server Database Project. I chose the prior in this post, it seemed clearer.
Convert the Projects to SSDT
Microsoft could not have made the conversion to the new SSDT project-type any simpler. Right-click on the Development server project and select ‘Convert to SQL Server Database project’. Select ‘Yes’, select ‘Backup originals for converted files’, and click ‘OK’. The conversion process should take only a minute or two. Following that, you are presented with a Conversion Report when the process is complete. The report should show the successful conversion to the SSDT project type.
Convert Project to SQL Server Database Project
Server Project Conversion Report
Repeat this process for the AdventureWorks2008 database project. Again, you see a Conversion Report when complete. It should also not contain any errors, nor files marked as ‘Not converted’.
Database Project Conversion Report
The New Project File Format
Reviewing the Conversion Report for the databae project, note the change to the primary project file. This is the first key difference between the VS 2010 project types and the new SSDT project types. The project file was converted from ‘AdventureWorks2008.dbproj’ to ‘AdventureWorks2008.sqlproj’ (see Conversion Report screen grab, above). Although the earlier project file with the ‘.dbproj’ file suffix is still in the project’s file directory, the Visual Studio Solution is now associated with the new ‘.sqlproj’ project file. This is the same for the server project. The ‘.dbproj’ files are no longer needed. You can drop then from the project’s file directory or from your source control system. This will prevent any confusion going forward.
Publishing Profiles
The second change you will note after the conversion is in the Solution Explorer tab. Each project has three items with the file suffix ‘.publish.xml’. These are publishing profiles. There are profiles for the each Solution configuration – Debug, Release, and Development. A publishing profile has all the settings necessary to publish changes made to the SSDT database project to a specific database environment. As part of the conversion to SSDT, all existing project settings migrated into the current project. Portions of the configuration-specific settings stay in the converted Solution configurations, while publish-specific settings are in the publishing profiles. Publishing profiles, like pre- and post-deployment scripts, are not part of the build. Select a profile in the Project Explorer tab. Note the ‘Build Action’ property in the Properties tab is set to ‘None’.
Solution Explorer View of Converted Projects
Additional Project and Profile Settings
There are also new settings in the converted projects. They support newer technologies like SSDT, SQL 2012, and Azure. As part of our first major conversion to SSDT, took the opportunity to review all project and publish settings with our database developers and DBAs. We stove to understand all the setting’s purpose and make sure they were correctly configured and documented for each of our many database environments.
Development Publishing Profile Advanced Settings
Testing the Converted Projects
To test the successful conversion of the both project to the SSDT project types, select the Development Solution configuration and perform a Rebuild on the Solution. In the Build section of the Output tab, you should see both projects built successfully.
Initial Build Results of SSDT Projects
Development Publishing Profile
Right-click on the ‘Development.publish.xml’ file in the AdventureWorks2008 project and select ‘Publish…’ Wait for the project to build. Selecting Publish or opening a publishing profile causes the project to build. Afterwards, you should see the ‘Publish Database’ window appear. Here is where you change the settings of Development profile. When converting the Adventure Works project to SSDT, I’ve found the database connection information does not migrate to the profile. Setup the ‘Target database connection’ information in the ‘Connection Properties’ pop-up window by clicking ‘Edit…’. When finished, click ‘OK’ to return to the ‘Publish Database’ window. Finally, save the revised Development publishing profile by clicking ‘Save Profile As…’. I will not cover the specific profile settings, accessed by clicking ‘Advanced…’. Many of these settings will be specific to your environments and workflows. They can be left as default for this demonstration.
Development Publishing Profile
Development Publishing Profile Connection Properties
Saving Development Publishing Profile Settings for Server
Generate Script for Adventure Works Database
Without leaving the ‘Publish Database’ window, click ‘Generate Script’. As in the first post, this action will initiate a schema comparison resulting the generation of a script that aggregates all the schema changes to the project, not already reflected in the database. The script represents the schema ‘delta’ (the difference) between the project and the database. The script will automatically open in Visual Studio’s main window after being created. In the ‘Data Tools Operations’ tab you should see messages indicating generation of the script was successful.
Generate Script Results
Also included in the script, along with the schema changes, are any pre- and post-deployment scripts. You should see the single post-deployment script that we created in part one of this series. Pre- and post-deployment scripts are always included in the script, whether or not they have already been executed. This is why it is imperative that pre- and post-deployment scripts are re-runnable. Scripts must have the ability to be executed more than once without producing unintended changes to the database.
Publishing to the Development Database
Next, right-click on the ‘Development.publish.xml’ file, and select ‘Publish…’. This will return you to the same window you were just in to generate the script. Click ‘Publish’. Again, in the ‘Data Tools Operations’ tab you should see messages that the publish operation completed successfully.
Successful Script Generation and Deployment to Development
Congratulations, you have completed and tested conversion of the Adventure Works database project to SSDT project type.
Note with SSDT, the term ‘Deploy’, which refers to a specific MSBuild target, is replaced with ‘Publish’, a SSDT-specific build target. Instead of deploying changes to the database, like we did in the first post, we will publish changes with SSDT. To understand how MSBuild is able to call the new SSDT Publish target, open the AdventureWorks2008.sqlproj file by right-clicking on the project and selecting ‘Edit Project File’. In the project file’s xml you will find an ‘Import’ tag that imports the SSDT targets into the project, making them accessible to MSBuild.
<!--Import the settings--> MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
New Production Environment
Between posts, I installed another instance of SQL Server 2008 R2, named ‘Production’. Into this instance I installed another copy of the Adventure Works database. I added the same ‘aw_dev’ user that we used in Development, with the same permissions. This SQL Server instance and Adventure Works database simulates a second database environment, Production. Normally, this instance would be installed on to separate server, but for simplicity sake I installed the Production instance on the same physical server as the Development instance. It makes no difference for the purposes of this post.
If you wish to follow all examples presented in the next two posts, you will need to install and configure the Production instance of SQL Server. Otherwise, you can disregard the portions of the two posts on publishing to Production, and just stick with the single Development environment. The conversion to SSDT doesn’t require the added Production environment.
New Production Configuration and Publish Profile
Next, we will create a new configuration in the SSDT project’s Solution and configure the resulting publishing profile, targeting the Production environment. We will use this to publish changes from the project to the Production environment. Using the Solution’s Configuration Manager, create a new Solution configuration. This process is unchanged from the VS 2010 database project-type.
New Production Solution Configuration
Solution Configuration Manager
New Production Configuration Build Settings for Database Project
Right click on the AdventureWorks2008 project and select ‘Publish…’. This will return us to the ‘Publish Database’ window. Like before, with the Development publishing profile, complete the connection string information, this time targeting the Production instance. Change the ‘Publish script name’ setting to ‘Production.sql’. Click ‘Save Profile As…’, and save this profile configuration into the project file path as ‘Production.publish.xml’. Repeat this process for the Development SSDT server project.
Database Project
Production Publishing Profile Settings for Database
Production Publishing Profile Connection Properties for Database
Saving Production Publishing Profile Settings for Database
Server Project
Production Publishing Profile Settings for Server
Saving Development Publishing Profile Settings for Server
We now have a new Production Solution configuration and corresponding publishing profiles in each of our two projects.
Solution Explorer View of Production Publishing Profiles
We can now target two different database environments from our AdventureWorks2008 project, Development and Production. In a typical production workflow, as a developer, you would make changes to the database project directly, or copy using a source control system like TFS. After testing your changes locally, you execute the publish task to send your schema changes and/or pre- and post-deployment scripts to the Development database instance. This process is be repeated by other developers in your department.
After successfully testing your application(s) against the Development database, you are ready to release the database changes to Testing, or in this example, directly to Production. You execute the Publish task again, this time choosing the Production Solution configuration and Production publishing profile. The schema changes and any pre- and post-deployment scripts are now executed against the Production database. You would follow the same process for other environments, such as Testing or Staging.
Making Schema Changes to Multiple Environments
For this test, we will make schema changes to the ‘Employee’ table, part of the ‘HumanResources’ schema. In Visual Studio, open the Employee table and add two new columns to the end of the table, as shown below. If you have not worked with the SSDT project type before, the view of the table will look very different to you. Microsoft has changed the earlier table view to include a friendlier design view as seen in SSMS, versus the earlier sql create statement only view. There is also a window which details all the key, indexes, and triggers associated with the table. I consider this light years better in term of usability from the developer’s standpoint. Save the changes to the table object and close it.
New SSDT Table Object View
Select the Development Solution configuration. Right-click on the Development profile in the AdventureWorks2008 project and click ‘Publish…’ Wait for the project to build. When the ‘Publish Database’ window appears, click ‘Publish’. You have just deployed the Employee table schema changes to the Development instance of the database.
Schema Changes to Employee Table in Script
Repeat this same process for Production. Don’t forget to switch to the Production Solution configuration and select the Production publish profile. You have now applied the same schema changes to the Production environment. Your customer will be happy they can now track the drug testing of their employees.
Successful Script Generation and Deployment to Development
There are other methods available with SSDT to deploy changes to the database. Using a script is the method I have chosen to show in this post.
Conclusion
In this post we converted the Adventure Works database project and Development server project to SSDT project-types. We created a new Solution Configuration and publishing profiles, targeting Production. We made schema changes to the SSDT database project. Finally, we deployed those changes to both the Development and Production database environments.
In Part III of this series, I will show how to use Jenkins CI Server to automate building, testing, delivering scripts, and publishing to a database from the SSDT database project.
Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3
Posted by Gary A. Stafford in .NET Development, Software Development, SQL Server Development, Team Foundation Server (TFS) Development on July 31, 2012
Objectives of 3-Part Series:
Part I: Setting up the Example Database and Visual Studio Projects
- Setup and configure a new instance of SQL Server 2008 R2
- Setup and configure a copy of Microsoft’s Adventure Works database
- Create and configure both a Visual Studio 2010 server project and Visual Studio 2010 database project
- Test the project’s ability to deploy changes to the database
Part II: Converting the Visual Studio 2010 Database and Server Projects to SSDT
- Convert the Adventure Works Visual Studio 2010 database and server projects to SSDT projects
- Create a second Solution configuration and SSDT publish profile for an additional database environment
- Test the converted database project’s ability to publish changes to multiple database environments
Part III: Automate the Building and Publishing of the SSDT Database Project Using Jenkins
- Automate the build and delivery of a sql change script artifact, for any database environment, to a designated release location using a parameterized build.
- Automate the build and publishing of the SSDT project’s changes directly to any database environment using a parameterized build.
Background
Microsoft’s Visual Studio 2010 (VS 2010) IDE has been available to developers since April, 2010. Microsoft’s SQL Server 2008 R2 (SQL 2008 R2) has also been available since April, 2010. If you are a modern software development shop or in-house corporate development group, using the .NET technology stack, you probably use VS 2010 and SQL 2008 R2. Moreover, odds are pretty good that you’ve implemented a Visual Studio 2010 Database Project SQL Server Project to support what Microsoft terms a Database Development Life Cycle (DDLC).
Now, along comes SSDT. Recently, along with the release of SQL Server 2012, Microsoft released SQL Server Data Tools (SSDT). Microsoft refers to SSDT as “an evolution of the existing Visual Studio Database project type.” According to Microsoft, SSDT offers an integrated environment within Visual Studio 2010 Professional SP1 or higher for developers to carry out all their database design work for any SQL Server platform (both on and off premise). The term ‘off premises’ refers to SSDT ‘s ability to handle development in the Cloud – SQL Azure. SSDT supports all current versions of SQL Server, including SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Azure.
SSDT offers many advantages over the VS 2010 database project type. SSDT provides a more database-centric user-experience, similar to SQL Server Management Studio (SSMS). Anyone who has used VS 2010 database project knows the Visual Studio experience offered a less sophisticated user-interface than SSMS or similar database IDE’s, like Toad for SQL. Installing Microsoft’s free SSDT component, after making sure you have SP1 installed for VS 2010 Professional or higher, you can easily convert you VS 2010 database projects to the new SSDT project type. The conversion offers a better development and deployment experience, and prepare you for an eventual move to SQL Server 2012.
Part I: Setting up the Example Database and Visual Studio Projects
Setting up the Example
To avoid learning SSDT with a copy of your client’s or company’s database, I suggest taking the same route I’ve taken in this post. To demonstrate how to convert from a VS 2010 database project to SSDT, I am using a copy of Microsoft’s Adventure Works 2008 database. Installed, the database only takes up about 180 MBs of space, but is well designed and has enough data to use as a good training tool it, as Microsoft intended. There are several version of the AdventureWorks2008 database available for download depending on your interests – OLTP, SSRS, Analysis Services, Azure, or SQL 2012. I chose to download the full database backup of the AdventureWorks2008R2 without filestream for this post.
Creating the SQL Server 2008 R2 Instance and Database
Before installing the database, I used the SQL Server Installation Center to install a new instance of SQL Server 2008 R2, which I named ‘Development’. This represents a development group’s database environment. Other environments in the software release life-cycle commonly include Testing, Staging, and Production. Each environment usually has its own servers with their own instances of SQL Server, with its own copy of the databases. Environments can also include web servers, application servers, firewalls, routers, and so forth.
After installing the Development instance, I logged into it as an Administrator and created a new empty database, which I named ‘AdventureWorks’. I then restored the downloaded backup copy of Adventure Works 2008 R2, which I downloaded, to the newly created Adventure Works database.
Creating the New Database
Creating the New Database
Creating the Database: Restoring the Full Backup
Creating the Database: Restoring the Full Backup
Creating the Database: Restoring the Full Backup
You may note some differences between the configuration settings displayed below in the screen grabs and the default configuration of the Adventure Works database. This post is not intended to recommend configuration settings for your SQL Server databases or database projects. Every company is different in the way it configures its databases. The key is to make sure that the configuration settings in your database align with the equivalent configuration settings in the database project in Visual Studio 2010. If not, when you initially publish changes to the database from the database project, the project will script the differences and change the database to align to the project.
Creating the Database: Database Properties
Creating the Server Login and Database User
Lastly in SSMS, I added a new login account to the Development SQL Server instance and a user to the Adventure Works database, named ‘aw_dev’. This user represents a developer who will interact with the database through the SSDT database project in VS 2010. For simplicity, I used SQL authentication for this user versus Windows authentication. I gave the user the minimal permissions necessary for this example. Depending on the types of interactions you have with the database, you may need to extend the rights of the user.
New Database Login
New Database Login
Two key, explicit permissions must be assigned for the user for SSDT to work properly. First is the ‘view any definition’ permission on the Development instance. Second is the ‘view definition’ permission on the Adventure Works database. These enable the SSDT project to perform a schema comparison to the Adventure Works database, explained later in the post. Lack of the view definition permission is one of the most common errors I’ve seen during deployments. They usually occur after adding a new database environment, database, database user, or continuous integration server.
New Database Login: ‘View any definition’ Permission
New Database Login: ‘View definition’ Permission
Setting up Visual Studio Database Project
In VS 2010, I created a new SQL Server 2008 database project, named ‘AdventureWorks2008’. In the same Visual Studio Solution, I also created a new SQL Server 2008 server project, named ‘Development’. The database projects mirrors the schema of the Adventure Works database, and the server project mirrors the instance of SQL Server 2008 R2 on which the database is housed. The exact details of creating and configuring these two projects are too long for this post, but I have a set of screen grabs, hyperlinked below, to aid in creating these two projects. For the database project, I only showed the screens that are signficantly different then the server project screens to save some space.
Server Project
VS 2010 Server Project: New Project SQL Server 2008 Wizard
VS 2010 Server Project: New Project SQL Server 2008 Wizard
VS 2010 Server Project: Project Properties
VS 2010 Server Project: Set Database Options
VS 2010 Server Project: Import Database Schema
VS 2010 Server Project: Database Connection Properties
VS 2010 Server Project: Configure Build and Deploy
Database Project
VS 2010 Database Project: New Project SQL Server 2008 Wizard
VS 2010 Database Project: Project Properties
VS 2010 Database Project: Summary
Reference from Database Project to Server Project
After creating both projects, I created a reference (dependency) from the Adventure Works 2008 database project to the Development server project. The database project reference to the server project creates the same parent-child relationship that exists between the Development SQL Server instance and the Adventure Works database. Once both projects are created and the reference made, your Solution should look like the second screen grab, below.
Adding a Reference to the Database Project
Database Project Dependencies
Final View of VS 2010 Solution with both Projects and Reference
Creating the Development Solution Configuration
Next, I created a new ‘Development’ Solution configuration. This configuration defines the build and deployment parameters for the database project when it targets the Development environment. Again, in a normal production environment, you would have several configurations, each targeting a specific database environment. In the context of this post, a database environment refers to a unique combination of servers, server instances, databases, data, and users. For this first example we are only setting up one database environment, Development.
New Development Solution Configuration
New Development Solution Configuration
The configuration specifies the parameters specific to the Adventure Works database in the Development environment. The connection string containing the server, instance, and database names, user account, and connection parameters, are all specific to the Development environment. They are different in the other environments – Testing, Staging, and Production.
Testing the Development Configuration
Once the Development configuration was completed, I ran the ‘Rebuild’ command on the Solution, using the Development configuration, to make sure there are no errors or warnings. Next, with the Development configuration set to only create the deployment script, not to create and deploy the changes to the database, I ran the ‘Deploy’ command. This created a deployment script, entitled ‘AdventureWorks2008.sql’, in the ‘sql\Development’ folder of the AdventureWorks2008 database project.
Database Project Configuration Settings to Create a Deployment Script Only
Deployment Script Location in Project
Since I just created both the Adventure Works database and the database project, based on the database, there are no schema changes in the deployment script. You will see ‘filler’ code for error checking and so forth, but no real executable schema changes to the database are present at this point. If you do see initial changes included in the script, usually database configuration changes, I suggest modifying the settings of the database project and/or the database to align to one another. For example, you may see code in the script to change the database’s default cursor from global to local, or vice-versa. Or, you may also see code in the script to the databases recovery model from full to simple, or vice-versa. You should decide whether the project or the database is correct, and change the other one to match. If necessary, re-run the ‘Deploy’ command and re-check the deployment script. Optionally, you can always execute the script with the changes, thus changing the database to match the project, if the project settings are correct.
Testing Deployment
After successfully testing the development configuration and the deployment script, making any configuration changes necessary to the project and/or the database, I then tested the project’s ability to successfully execute a Deploy command against the database. I changed the Development configuration’s deploy action from ‘create a deployment script (.sql)’ to from ‘create a deployment script (.sql) and deploy to the database’. I then ran the ‘Deploy’ command again, and this time the script is created and executed against the database. Since I still had not made any changes to the project, there were no schema changes made to the database. I just tested the project’s ability to create and deploy to the database at this point. Most errors at this stage are insufficient database user permissions (see example, below).
Test Deployment to the Database
View Any Definition Permission Error
Testing Changes to the Project
Finally, I tested the project’s ability to make changes to the database as part of the deployment. To do so, I created a simple post-deployment script that changes the first name of a single, existing employee. After adding the post-deployment script to the database project and adding the script’s path to the post-deployment script file, I again ran the ‘Deploy’ command again, still using the Development configuration. This time the deployment script contained the post-deployment script’s contents. When deployed, one record was affected, as indicated in VS 2010 Output tab. I verified the change was successful in the Adventure Works database table, using SSMS.
Simple Post-Deployment Script
Simple Post-Deployment Script
Post-Deployment Script Location in Project
One Row Affected by Deployment this Time
Before Post-Deployment Script was Deployed
After Post-Deployment Script was Deployed
Conclusion
We now have a SQL Server 2008 R2 database instance representing a Development environment, and a copy of the Adventure Works database, being served from that instance. We have corresponding VS 2010 database and server projects. We also have a new Development Solution configuration, targeting the Development environment. Lastly, we tested the database project’s capability to successfully build and deploy a change to the database.
In Part II of this series, I will show how to convert the VS 2010 database and server projects to SSDT.
Gary Stafford
AWS Area Principal Solutions Architect | 10x AWS Certified Pro | DevOps | Data/ML | Serverless | Polyglot Developer | Former ThoughtWorks and Accenture
Programmatic Ponderings 