Posts Tagged Database

Building a Microservices-based REST API with RestExpress, Java EE, and MongoDB: Part 3

Develop a well-architected and well-documented REST API, built on a tightly integrated collection of Java EE-based microservices.

Virtual-Vehicles Architecture

Note: All code available on GitHub. For the version of the code that matches the details in this blog post, check out the master branch, v1.0.0 tag (after running git clone …, run a git checkout tags/v1.0.0 command).

Previous Posts

In Part One of this series, we introduced the microservices-based Virtual-Vehicles REST API example. The vehicle-themed Virtual-Vehicles microservices offers a comprehensive set of functionality, through a REST API, to application developers. In Part Two, we installed a copy of the Virtual-Vehicles project from GitHub. In Part Two, we also gained a basic understanding of how RestExpress works. Finally, we discovered how to get the Virtual-Vehicles microservices up and running.

Part Three

In part three of this series, we will take the Virtual-Vehicles for a test drive (get it? maybe it was funnier the first time…). There are several tools we can use to test the Virtual-Vehicles API. One of my favorite tools is Postman.  We will explore how to use Postman, along with the Virtual-Vehicles API documentation, to test the Virtual-Vehicles microservice’s endpoints, which compose the Virtual-Vehicles API.

Testing the API

There are three categories of tools available to test RESTful APIs, which are GUI-based applications, command line tools, and testing frameworks. Postman, Advanced REST ClientREST Console, and SmartBear’s SoapUI and SoapUI NG Pro, are examples of GUI-based applications, designed specifically to test RESTful APIs. cURL and GNU Wget are two examples of command line tools, which among other capabilities, can test APIs. Lastly, JUnit is an example of a testing framework that can be used to test a RESTful API. Surprisingly, JUnit is not only designed to manage unit tests. Each category of testing tools has their pros and cons, depending on your testing needs. We will explore all of these categories in this post as we test the Virtual-Vehicles REST API.

JUnit

JUnit is probably the best known of all Java unit testing frameworks. JUnit’s website describes JUnit as ‘a simple, open source framework to write and run repeatable tests. It is an instance of the xUnit architecture for unit testing frameworks.’ Most Java developers turn to JUnit for unit testing. However, JUnit is capable of other forms of testing, including integration testing. In his post, ‘Unit Testing with JUnit – Tutorial’, Lars Vogel states ‘an integration test has the target to test the behavior of a component or the integration between a set of components. The term functional test is sometimes used as a synonym for integration test. This kind of tests allow you to translate your user stories into a test suite, i.e., the test would resemble an expected user interaction with the application.’

Testing the Virtual-Vehicles RESTful API’s operations with JUnit would be considered integration (functional) testing. At a minimum, to complete requests, we call one microservice, which in turn authenticates the JWT by calling another microservice. If authenticated, the first microservice makes a request to its MongoDB database. As Vogel stated, whereas a unit test targets a small unit of code, such as a method, the request/response operation is integration between a set of components. testing an API call requires several dependencies.

The simplest example of testing the Virtual-Vehicles API with JUnit, would be to test an HTTP GET request to return a single instance of a vehicle. The code below demonstrates how this might be done. Notice the request depends on helper methods (not included, for brevity). To request the vehicle, assuming we already have a registered client, we need a valid JWT. We also need a valid vehicle ObjectId. To obtain these two pieces of data, we call helper methods, which in turn makes the necessary request to retrieve a JWT and vehicle ObjectId.

Below are the results of the above test, run in NetBeans IDE, using the built-in support for JUnit.

JUnit Test Results

JUnit can also be run from the command line using the Maven goal, surefire:test:

Running JUnit from Command Line

cURL

One of the best-known command line tools for calling for all types of operations centered around calling a URL is cURL. According to their website, ‘curl is a command line tool and library for transferring data with URL syntax, supporting…HTTP, HTTPS…curl supports SSL certificates, HTTP POST, HTTP PUT, FTP uploading, HTTP form based upload, proxies, HTTP/2, cookies, user+password authentication (Basic, Plain, Digest, CRAM-MD5, NTLM, Negotiate, and Kerberos), file transfer resume, proxy tunneling and more.’ I prefer the website’s  briefer description, cURL ‘groks those URLs’.

Using cURL, we could make an HTTP PUT request to the Vehicle microservice’s /vehicles/{oid}.{format} endpoint. With cURL, we have the ability to add the JWT-based Authorization header and the raw request body, containing the modified vehicle object. Below is an example of that cURL command, which can be run from a terminal prompt.

The response body contains the expected modified vehicle object in JSON-format, along with a 201 Created response status.

cURL  HTTP PUT Vehicle

The cURL commands may be incorporated into many types of automated testing processes. These might be as simple as a bash script. The script could a series of automated tests, including the following: register an API client, use the API key to create a JWT, use the JWT to create a new vehicle, use the new vehicle’s ObjectId to modify that same vehicle, delete that vehicle, confirm the vehicle is removed using the count operation and returns a test results report to the user.

cURL Commands from Chrome
Quick tip, instead of hand-coding complex cURL commands, containing form data, URL parameters, and Headers, use Chrome. First, open the Chrome Developer Tools (f12). Next, using the Postman – REST Client for Chrome, available in the Chrome App Store, execute your HTTP request. Finally, in the ‘Network’ tab of the Developers tools, find and right-click on the request and select ‘Copy as cURL’. You have a complete cURL command equivalent of your Postman request, which you can paste directly into the command line or insert into a script. Below is an example of using the Postman – REST Client for Chrome to generate a cURL command.

Using Postman in Chrome to get cURL

The generated command is a bit verbose. Compare this command to the cURL command, earlier.

Wget

Similar to cURL, GNU Wget provides the ability to call the Virtual-Vehicles API’s endpoints. According to their website, ‘GNU Wget is a free software package for retrieving files using HTTP, HTTPS and FTP, the most widely-used Internet protocols. It is a non-interactive command line tool, so it may easily be called from scripts, cron jobs, terminals without X-Windows support, etc.’ Again, like cURL, we can run Wget commands from the command line or incorporate them into scripted testing processes. The Wget website contains excellent documentation.

Using Wget, we could make the same HTTP PUT request to the Vehicle microservice /vehicles/{oid}.{format} endpoint. Like cURL, we have the ability to add the JWT-based Authorization header and the raw request body, containing the modified vehicle object.

The response body contains the expected modified vehicle object in JSON-format, along with a 201 Created response status.

Wget HTTP PUT Vehicle

cURL Bash Testing

We can combine cURL and Wget with several of the tools bash provides, to develop fairly complex integration tests. The bash-based script below just scratches the surface as a complete set of integration tests. However, the tests demonstrate an efficient multi-stage test approach to handling the complex nature of RESTful service request requirements. The tests build upon each other.

After setting up some variables and doing a quick health check on one service, the tests register a new API client by calling the Authentication service. Next, they use the new client’s API key to obtain a JWT. The tests then use the JWT to authenticate themselves and create a new vehicle. Finally, they use the new vehicle’s id and the JWT to verify the existence for the new vehicle.

Although some may consider using bash to test somewhat primitive, the following script demonstrates the effectiveness of bash’s  curl, grep, sed, awk, along with regular expressions, to test our RESTful services. Note how we grep certain values from the response, such as the new client’s API key, and then use that value as a parameter for the following test request, such as to obtain a JWT.

Since these tests are just a bash script, they can from the command line, or easily called from a continuous integration tool, Such as Jenkins CI or Hudson.

Running Integration Tests

Postman

Postman, like several similar tools, is an application designed specifically for test API endpoints. The Postman website describes Postman as tool that allows you to ‘build, test, and document your APIs faster.’  There are two versions of Postman in the Chrome Web Store. They are Postman – REST Client, the in-browser extension, which we mentioned above, and Postman, the standalone application. There is also Postman Interceptor, which helps you send requests that use browser cookies through the Postman application.

Postman and similar applications, have add-ons and extensions to extend their features. In particular, Postman, which is free, offers the Jetpacks paid extension. Jetpacks add the ability to ‘write and run tests inside Postman, extract data from responses, chain requests together and test requests with thousands of variations’. Jetpacks allow you to move beyond basic one-off API request-based testing, to automated regression and performance testing.

Using Postman
Let’s use the same HTTP PUT example we used with cURL and Wget, and see how we would perform the same task with Postman. In the first screen grab below, you can see all elements of the HTTP request, including the RESTful API’s URL, URI including the vehicle’s ObjectId (/vehicles/{ObjectId}.{format}), HTTP method (PUT), Authorization Header with JWT (Bearer), and the raw request body. The raw request body contains a JSON representation of the vehicle we want to update. Note how Postman saves the request in history so we can easily replay it later.

Postman HTTP PUT of Vehicle

In the next screen-grab, we see the response to the HTTP PUT request. Note the response body, response status, timing, and response headers.

Postman HTTP POST of Vehicle Response

Looking at the response body in Postman, you easily see the how RestExpress demonstrates the RESTful principle we discussed in Part Two of the series, HATEOAS (Hypermedia as the Engine of Application State). Note the link to this vehicle’s ‘self’ href) and the entire vehicles collection (‘up’ href).

Postman Collections
A great feature of Postman with Jetpacks is Collections. Collections are sets of requests, which can be saved, recalled, and shared. The Collection Runner runs requests in a collection, in the order in which you set them. Ordered collections are ideal for the Virtual-Vehicles API. The screen grab below shows a collection of requests, arranged in the order we would execute them to test the Virtual-Vehicles API, as it applies to specifically to vehicle CRUD operations:

  1. Execute HTTP POST request to register the new API client, passing the application name and a shared secret in the request
    Receive the new client’s API key in response
  2. Execute HTTP GET to request, passing the new client’s API key and the shared secret in the request
    Receive the new JWT in response
  3. Execute HTTP POST request to create a new vehicle, passing the JWT in the header for authentication (used for all following requests)
    Receive the new vehicle object in response
  4. Execute HTTP PUT request to modify the new vehicle, using the vehicle’s ObjectId
    Receive the modified vehicle object in response
  5. Execute HTTP GET to request the modified vehicle, to confirm it exists in the expected state
    Receive the vehicle object in response
  6. Execute HTTP DELETE request to delete the new vehicle, using the vehicle’s ObjectId
  7. Execute HTTP GET to request the new vehicle and to confirm it has been removed
    Receive a 404 Not Found status response, as expected

Postman Ordered Series of REST Calls

Using saved collections for testing the Virtual-Vehicles API is a real-time saving. However, the collections cannot easily be re-run without hand-editing or some advanced scripting. In the simple example above, we hard-coded a JWT and vehicle ObjectId in the requests. Unfortunately, the JWT has an expiration of only 10 hours by default. More immediately, the ObjectId is unique. The earlier collection test run created, then deleted, the vehicle with that ObjectId.

Negative Testing
You may also perform negative testing with Postman. For example, do you receive the expected response when you don’t include the Authorization Header with JWT in a request (401 Unauthorized status)? When you include a JWT, which has expired (401 Unauthorized status)? When you request a vehicle, whose ObjectId is incorrect or is not found in the database (400 Bad Request status)? Do you receive the expected response when you call an actual service, but an endpoint that doesn’t exist (405 Method Not Allowed)?

Negative Testing in Postman

Postman Test Automation

In addition to manually viewing the HTTP response, to verify the results of a request, Postman allows you to write and run automated tests for each request. According to their website, a ‘Postman test is essentially JavaScript code which sets values for the special tests object. You can set a descriptive key for an element in the object and then say if it’s true or false’. This allows you to write a set of response validation tests for each request.

Below is a quick example of testing the same HTTP POST request, used to create the new API client, above. In this example, we:

  1. Test that the Content-Type response header is present
  2. Test that the HTTP POST successfully returned a 201 status code
  3. Test that the new client’s API key was returned in the response body
  4. Test that the response time was less than 200ms

Postman Test Editor Example

Reviewing Postman’s ‘Tests’ tab, above, observe the four tests have run successfully. Using the Postman’s testing feature, you can create even more advanced tests, eliminating the need to manually validate responses.

This post demonstrates a small subset of the features Postman and other similar applications provide for testing RESTful API. The tools and processes you use to test your RESTful API will depend on the stage of development and testing you are in, as well as the existing technology stacks you build, and on which you host your services.

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

1 Comment

Building a Microservices-based REST API with RestExpress, Java EE, and MongoDB: Part 2

Develop a well-architected and well-documented REST API, built on a tightly integrated collection of Java EE-based microservices.

Virtual-Vehicles Architecture

Note: All code available on GitHub. For the version of the code that matches the details in this blog post, check out the master branch, v1.0.0 tag (after running git clone …, run a ‘git checkout tags/v1.0.0’ command).

Previous Post

In Part One of this series, we introduced the microservices-based Virtual-Vehicles REST API example. The vehicle-themed Virtual-Vehicles microservices offers a comprehensive set of functionality, through a REST API, to application developers. The developers, in turn, will use the Virtual-Vehicles REST API’s functionality to build applications and games for their end-users.

In Part One, we also decided on the proper amount and responsibility of each microservice. We also determined the functionality of each microservice to meet the hypothetical functional and nonfunctional requirements of Virtual-Vehicles. To review, the four microservices we are building, are as follows:

Virtual-Vehicles REST API Resources

Microservice Purpose (Business Capability) Functions
Authentication
Manage API clients and
JWT authentication
  • Create a new API client (public)
  • Read, filter, sort, count, paginate API clients (admin)
  • Read a single API client (public)
  • Update an existing API client (public)
  • Delete an existing API client (admin)
  • Create new JWT (public)
  • Validate a JWT (internal)
  • Service health ping (admin)
Vehicle
Manage virtual vehicles
  • Create a new vehicle (public)
  • Read, filter, sort, count, paginate vehicles (admin)
  • Read a single vehicle (public)
  • Update an existing vehicle (public)
  • Delete an existing vehicle (admin)
  • Validate a JWT (internal)
  • Service health ping (admin)
Maintenance
Manage maintenance on vehicles
  • Create a new maintenance record (public)
  • Read, filter, sort, count, paginate maintenance records (admin)
  • Read a single maintenance record (public)
  • Update an existing maintenance record (public)
  • Delete an existing maintenance record (admin)
  • Validate a JWT (internal)
  • Service health ping (admin)
Valet Parking
Manage a valet service to park for vehicles
  • Create a new valet parking transaction (public)
  • Read, filter, sort, count, paginate valet parking transactions (admin)
  • Read a single valet parking transaction (public)
  • Update an existing valet parking transaction (public)
  • Delete an existing valet parking transaction (admin)
  • Validate a JWT (internal)
  • Service health ping (admin)

To review, the first five functions for each service are all basic CRUD operations: create (POST), read (GET), readAll (GET), update (PUT), delete (DELETE). The readAll function also has find, count, and pagination functionality using query parameters. Unfortunately, RestExpress does not support PATCH for updates. However, I have updated RestExpress’ PUT HTTP methods to return the modified object in the response body instead of the nothing (status of 201 Created vs. 200 OK). See StackOverflow for an explanation.

All services also have an internal authenticateJwt function, to authenticate the JWT, passed in the HTTP request header, before performing any operation. Additionally, all services have a basic health-check function, ping (GET). There are only a few other functions required for our Virtual-Vehicles example, such as for creating JWTs.

 Part Two Introduction

In Part Two, we will build our four Virtual-Vehicles microservices. Recall from our first post, we will be using RestExpress. RestExpress composes best-of-breed open-source tools to enable quickly creating RESTful microservices that embrace industry best practices. Those best-of-breed tools include Java EE, Maven, MongoDB, and Netty, among others.

In this post, we will accomplish the following:

  • Create a default microservice project in NetBeans using RestExpress MongoDB Maven Archetype
  • Understand the basic structure of a default RestExpress microservice project
  • Review the changes made to the default RestExpress microservice project to create the Virtual-Vehicles example
  • Compile and run the individual microservices directly from NetBeans

I used NetBeans IDE 8.0.2 on Linux Ubuntu 14.10 to build the microservices. You may also follow along in other IDE’s, such as Eclipse or IntelliJ, on Mac or Windows. We won’t cover installing MongoDB, Maven, and Java. I’ll assume if your building enterprise applications, you have the basics covered.

Using the RestExpress MongoDB Maven Archetype

All the code for this project is available on GitHub. However, to understand RestExpress, you should go through the exercise of scaffolding a new microservice using the RestExpress MongoDB Maven Archetype. You will also be able to use this default microservice project to compare and contrast to the modified versions, used in the Virtual-Vehicles example. The screen grabs below demonstrate how to create a new microservice project using the RestExpress MongoDB Maven Archetype. At the time of this post, the archetype version was restexpress-mongodb version 1.15.

New Maven Project from Archetype Step 1

New Maven Project from Archetype Step 2

New Maven Project from Archetype Step 2

Default Project Architecture

Reviewing the two screen grabs below (Project tab), note the key components of the RestExpress MongoDB Maven project, which we just created:

  • Base Package (com.example.vehicle)
    • Configuration class reads in environment properties (see Files tab) and instantiates controllers
    • Constants class contains project constants
    • Relationships class defines linking resource which aids service discoverability (HATEOAS)
    • Main executable class
    • Routes class defines the routes (endpoints) exposed by the service and the corresponding controller class
  • Model/Controllers Packages (com.example.vehicle.objectid and .uuid)
    • Entity class defines the data entity – a Vehicle in this case
    • Controller class contains the methods executed when the route (endpoint) is called
    • Repository class defines the connection details to MongoDB
    • Service class contains the calls to the persistence layer, validation, and business logic
  • Serialization Package (com.example.vehicle.serialization)
    • XML and JSON serialization classes
    • Object ID and UUID serialization and deserialization classes

New Maven Project from Archetype Project View

New Maven Project from Archetype Projects View

Again, I strongly recommend reviewing each of these package’s classes. To understand the core functionality of RestExpress, you must understand the relationships between RestExpress microservice’s Route, Controller, Service, Repository, Relationships, and Entity classes. In addition to reviewing the default Maven project, there are limited materials available on the Internet. I would recommend the RestExpress Website on GitHub, RestExpress Google Group Forum, and the YouTube 3-part video series, Instant REST Services with RESTExpress.

Unit Tests?
Disappointingly, the current RestExpress MongoDB Maven Archetype sample project does not come with sample JUnit unit tests. I am tempted to start writing my own unit tests if I decided to continue to use the RestExpress microservices framework for future projects.

Properties Files

Also included in the default RestExpress MongoDB Maven project is a Java properties file (environment.properties). This properties file is displayed in the Files tab, as shown below. The default properties file is located in the ‘dev’ environment config folder. Later, we will create an additional properties file for our production environment.

Default Properties File

Ports
Within the ‘dev’ environment, each microservice is configured to start on separate ports  (i.e. port = 8581). Feel free to change the service’s port mappings if they conflict with previously configured components running on your system. Be careful when changing the Authentication service’s port, 8587, since this port is also mapped in all other microservices using the authentication.port property (authentication.port = 8587). Make sure you change both properties, if you change Authentication service’s port mapping.

Base URL
Also, in the properties files is the base.url property. This property defines the URL the microservice’s endpoints will be expecting calls from, and making internal calls between services. In our post’s example, this property in the ‘dev’ environment is set to localhost (base.url = http://localhost). You could map an alternate hostname from your hosts file (/etc/hosts). We will do this in a later post, in our ‘prod’ environment, mapping the base.url property to Virtual-Vehicles (base.url = http://virtual-vehicles.com). In the ‘dev’ environment properties file, MongoDB is also mapped to localhost (i.e. mongodb.uri = mongodb://virtual-vehicles.com:27017/virtual_vehicle).

Metrics Plugin and Graphite
RestExpress also uses the properties file to hold configuration properties for Metrics Plugin and Graphite. The Metrics Plugin and Graphite are both first class citizens of RestExpress. Below is the copy of the Vehicles service environment.properties file for the ‘dev’ environment. Note, the Metrics Plugin and Graphite are both disabled in the ‘dev’ environment.

Choosing a Data Identification Method

RestExpress offers two identification models for managing data, the MongoDB ObjectId and a Universally Unique Identifier (UUID). MongoDB uses an ObjectId to identify uniquely a document within a collection. The ObjectId is a special 12-byte BSON type that guarantees uniqueness of the document within the collection. Alternately, you can use the UUID identification model. The UUID identification model in RestExpress uses a UUID, instead of a MongoDB ObjectId. The UUID also contains createdAt and updatedAt properties that are automatically maintained by the persistence layer. You may stick with ObjectId, as we will in the Virtual-Vehicles example, or choose the UUID. If you use multiple database engines for your projects, using UUID will give you a universal identification method.

Project Modifications

Many small code changes differentiate our Virtual-Vehicles microservices from the default RestExpress Maven Archetype project. Most changes are superficial; nothing changed about how RestExpress functions. Changes between the screen grabs above, showing the default project, and the screen grabs below, showing the final Virtual-Vehicles microservices, include:

  • Remove all packages, classes, and code references to the UUID identification methods (example uses ObjectId)
  • Rename several classes for convenience (dropped use of word ‘Entity’)
  • Add the Utilities (com.example.utilities) and Authentication (com.example.authenticate) packages

Final Virtual-Vehicles Microservices Projects

Final Virtual-Vehicles Microservices Projects

MongoDB

Following a key principle of microservices mentioned in the first post, Decentralized Data Management, each microservice will have its own instance of a MongoDB database associated with it. The below diagram shows each service and its corresponding database, collection, and fields.

Virtual-Vehicles Database Diagram
From the MongoDB shell, we can observe the individual instances of the four microservice’s databases.

MongoDB Virtual-Vehicles Databases

In the case of the Vehicle microservice, the associated MongoDB database is virtual_vehicle. This database contains a single collection, vehicles. While the properties file defines the database name, the Vehicles entity class defines the collection name, using the org.mongodb.morphia.annotations classes annotation functionality.

Looking at the virtual_vehicle database in the MongoDB shell, we see that the sample document’s fields correspond to the Vehicle entity classes properties.

MongoDB vehicles Document
Each of the microservice’s MongoDB databases are configured in the environments.properties file, using the mongodb.uri property. In the ‘dev’ environment we use localhost as our host URL (i.e. mongodb.uri = mongodb://localhost:27017/virtual_vehicle).

Authentication and JSON Web Tokens

The three microservices, Vehicle, Valet, and Maintenance, are almost identical. However, the Authentication microservice is unique. This service is called by each of the other three services, as well as also being called directly. The Authentication service provides a very basic level of authentication using JSON Web Tokens (JWT), pronounced ‘jot‘.

Why do we want authentication? We want to confirm that the requester using the Virtual-Vehicles REST API is the actual registered API client they are who they claim to be. JWT allows us to achieve this requirement with minimal effort.

According to jwt.io, ‘a JSON Web Token is a compact URL-safe means of representing claims to be transferred between two parties. The claims in a JWT are encoded as a JSON object that is digitally signed using JSON Web Signature (JWS).‘ I recommend reviewing the JWT draft standard to fully understand the structure, creation, and use of JWTs.

Virtual-Vehicles Authentication Process
There are different approaches to implementing JWT. In our Virtual-Vehicles REST API example, we use the following process for JWT authentication:

  1. Register the new API client by supplying the application name and a shared secret (one time only)
  2. Receive an API key in response (one time only)
  3. Obtain a JWT using the API key and the shared secret (each user session or renew when the previous JWT expires)
  4. Include the JWT in each API call

In our example, we are passing four JSON fields in our set of claims. Those fields are the issuer (‘iss’), API key, expiration (‘exp’), and the time the JWT was issued (‘ait’). Both the ‘iss’ and the ‘exp’ claims are defined in the Authentication service’s environment.properties file (jwt.expire.length and jwt.issuer).

Expiration and Issued date/time use the JWT standard recommended “Seconds Since the Epoch“.  The default expiration for a Virtual-Vehicles JWT is set to an arbitrary 10 hours from the time the JWT was issued (jwt.expire.length = 36000). That amount, 36,000, is equivalent to 10 hours x 60 minutes/hour x 60 seconds/minute.

Decoding a JWT
Using the jwt.io site’s JT Debugger tool, I have decoded a sample JWT issued by the Virtual-Vehicles REST API, generated by the Authentication service. Observe the three parts of the JWT, the JOSE Header, Claims Set, and the JSON Web Signature (JWS).

Decoded JWT

The JWT’s header indicates that our JWT is a JWS that is MACed using the HMAC SHA-256 algorithm. The shared secret, passed by the API client, represents the HMAC secret cryptographic key. The secret is used in combination with the cryptographic hash function to calculate the message authentication code (MAC). In the example below, note how the API client’s shared secret is used to validate our JWT’s JWS.

Sequence Diagrams of Authentication Process
Below are three sequence diagrams, which detail the following processes: API client registration process, obtaining a new JWT, and a REST call being authenticated using the JWT. The end-user of the API self-registers their application using the Authentication service and receives back an API key. The API key is unique to that client.

Register API Client (Authentication Service)

The end-user application then uses the API key and the shared secret to receive a JWT from the Authentication service.

Requesting a JWT (Authentication Service)After receiving the JWT, the end-user application passes the JWT in the header of each API request. The JWT is validated by calling the Authentication service. If the JWT is valid, the request is fulfilled. If not valid, a ‘401 Unauthorized’ status is returned.

API Call with JWT Passed in Header (Vehicle Service)JWT Validation
The JWT draft standard recommends how to validate a JWT. Our Virtual-Vehicles Authentication microservice uses many of those criteria to validate the JWT, which include:

  1. API Key – Retrieve API client’s shared secret from MongoDB, using API key contained in JWT’s claims set (secret is returned; therefore API key is valid)
  2. Algorithm – confirm the algorithm (‘alg’), found in the JWT Header, which used to encode JWT, was ‘HS256’ (HMAC SHA-256)
  3. Valid JWS – Use the client’s shared secret from #1 above, decode HMAC SHA-256 encrypted JWS
  4. Expiration – confirm JWT is not expired (‘exp’)

Inter-Service Communications

By default, the RestExpress Archetype project does not offer an example of communications between microservices. Service-to-service communications for microservices is most often done using the same HTTP-based REST calls used to by our Virtual-Vehicles REST API. Alternately, a message broker, such as RabbitMQ, Kafka, ActiveMQ, or Kestrel, is often used. Both methods of communicating between microservices, referred to as ‘inter-service communication mechanisms’ by InfoQ, have their pros and cons. The InfoQ website has an excellent microservices post, which discusses the topic of service-to-service communication.

For the Virtual-Vehicles microservices example, we will use HTTP-based REST calls for inter-service communications. The primary service-to-service communications in our example, is between the three microservices, Vehicle, Valet, and Maintenance, and the Authentication microservice. The three services validate the JWT passed in each request to a CRUD operation, by calling the Authentication service and passing the JWT, as shown in the sequence diagram, above. Validation is done using an HTTP GET request to the Authentication service’s .../jwts/{jwt} endpoint. The Authentication service’s method, called by this endpoint, minus some logging and error handling, looks like the following:

Primarily, we are using the java.net and java.io packages, along with the org.restexpress.Request class to build and send our HTTP request to the Authentication service. Alternately, you could use just the org.restexpress package to construct request and handle the response. This same basic method structure shown above can be used to create unit tests for your service’s endpoints.

Health Ping

Each of the Virtual-Vehicles microservices contain a DiagnosticController in the .utilities package. In our example, we have created a ping() method. This simple method, called through the .../utils/ping endpoint, should return a 200 OK status and a boolean value of ‘true’, indicating the microservice is running and reachable. This route’s associated method could not be simpler:

The ping health check can even be accessed with a simple curl command, curl localhost:8581/vehicles/utils/ping.

In a real-world application, we would add additional health checks to all services, providing additional insight into the health and performance of each microservice, as well as the service’s dependencies.

API Documentation

A well written RESTful API will not require extensive documentation to understand the API’s operations. Endpoints will be discoverable through linking (see Response Body links section in below example). API documentation should provide HTTP method, required headers and URL parameters, expected response body and response status, and so forth.

An API should be documented before any code is written, similar to TDD. The API documentation is the result of a clear understanding of requirements. The API documentation should make the coding even easier since the documentation serves as a further refinement of the requirements. The requirements are an architectural plan for the microservice’s code structure.

Sample Documentation
Below, is a sample of the Virtual-Vehicles REST API documentation. It details the function responsible for creating a new API client. The documentation provides a brief description of the function, the operation’s endpoint (URI), HTTP method, request parameters, expected response body, expected response status, and even a view of the MongoDB collection’s document for a new API client.

Virtual-Vehicles API Guide

You can download a PDF version of the Virtual-Vehicles RESTful API documentation on GitHub or review the source document on Google Docs. It contains general notes about the API, and details about every one of the API’s operations.

Running the Individual Microservices

For development and testing purposes, the easiest way to start the microservices is in NetBeans using the Run command. The Run command executes the Maven exec goal. Based on the DEFAULT_ENVIRONMENT constant in the org.restexpress.util Environment class, RestExpress will use the ‘dev’ environment’s environment.properties file, in the project’s /config/dev directory.

Alternately, you can use the RestExpress project’s recommended command from a terminal prompt to start each microservice from its root directory (mvn exec:java -Dexec.mainClass=test.Main -Dexec.args="dev"). You can also use this command to switch from the ‘dev’ to ‘prod’ environment properties (-Dexec.args="prod").

You may use a variety of commands to confirm all the microservices are running. I prefer something basic like sudo netstat -tulpn | grep 858[0-9]. This will find all the ports within the ‘dev’ port range. For more in-depth info, you can use a command like ps -aux | grep com.example | grep -v grep

Microservices Running

Part Three: Testing our Services

We now have a copy of the Virtual-Vehicles project pulled from GitHub, a basic understanding of how RestExpress works, and our four microservices running on different ports. In Part Three of this series, we will take them for a drive (get it?). There are many ways to test our service’s endpoints. One of my favorite tools is Postman. we will explore how to use several tools, including Postman, and our API documentation, to test our microservice’s endpoints.

Postman Preview

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

5 Comments

Building a Microservices-based REST API with RestExpress, Java EE, and MongoDB: Part 1

Develop a well-architected and well-documented REST API, built on a tightly integrated collection of Java EE-based microservices.

Generic API Architecture

Microservices

Microservices are a popular and growing trend in software development. According to Wikipedia, microservices are “a software architecture style, in which complex applications are composed of small, independent processes communicating with each other using language-agnostic APIs. These services are small, highly decoupled and focus on doing a small task.

Martin Fowler and James Lewis (ThoughtWorks) have done an exemplary job capturing the essence of microservice architecture in their March 2014 post, microservices. Fowler has also discussed these principles in several presentations, including the January 2015 goto; Conference, Keynote: Microservices by Martin Fowler.

Additionally, noted technical consultant and speaker, Adrian Cockcroft (Battery Ventures), has made significant contributions to the definition of microservices, such as in his December 2014 dockercon14 | eu presentation, State of the Art in Microservices.

Lastly, Zhamak Dehghani (ThoughtWorks), delivered an in-depth discussion of microservices, including customer perspectives, in her October 2014 presentation, Real-World Microservices: Lessons from the Frontline.

Some of the major characteristics of microservices and REST cited by these experts, include:

  • Organized Around Business Capabilities
  • Single Responsibility
  • Loose Coupling / High Cohesion
  • Smart Endpoints and Dumb Pipes
  • Decentralized Data Management
  • Hypermedia as the Engine of Application State (HATEOAS)

As we develop this post’s example, I will demonstrate how all of the above characteristics are implemented.

REST API

A REST API is the mash-up of two common software concepts, Representational State Transfer (REST) and an application programming interface (API). Although even Wikipedia doesn’t have an exact definition of a REST API, they come close to their discussion of REST. According to Wikipedia“Web service APIs that adhere to the REST architectural constraints are called RESTful APIs. HTTP-based RESTful APIs are defined with these aspects: base URI, an Internet media type for the data, standard HTTP methods, hypertext links to a reference state, and hypertext links to reference related resources.

An important nuance and differentiator from SOA-based APIs, RESTful APIs do not require XML-based Web service protocols (SOAP and WSDL) to support their interfaces (Wikipedia).

The author of the WebConcepts channel does an excellent job capturing the essence of REST APIs in REST API concepts and examples. Two additional presentations I strongly recommend are REST+JSON API Design – Best Practices for Developers and Designing a Beautiful REST+JSON API, both by Les Hazlewood, CTO of Stormpath. Stormpath is a leader in the commercial REST API space.

Microservices-Based REST API

A microservices-based REST API is a REST API, whose HTTP requests call an orchestrated collection or collections of language-agnostic and platform-agnostic microservices. The combination of these two trends, microservices, and a REST API, offers a simple, reliable, and scalable solution for providing flexible functionality to an end-user, in a technology-agnostic manner.

REST API Example

There is a fast-growing volume of reference materials describing the characteristics, benefits, and general architecture of microservices and REST APIs. However, in researching these topics, I have found a shortage of practical examples or tutorials on building microservices-based REST API solutions.

Undoubtedly, the complexity of even the simplest microservices-based solution limits the number of available cases. A minimally viable solution require planning, coding, testing, and documentation. The addition of cross-cutting features such as security, logging, monitoring, and orchestration, creates an enormous task to build a practical microservices-based example.

In the following series of posts, we will use many of the characteristics of a modern microservice architecture as described by Fowler, Lewis, Cockcroft, and Dehghani. We will combine these microservice characteristics with the best practices of good REST API design, as described by Hazlewood and WebConcepts, to build a minimally viable microservices-based REST API.

In a future post, we will create an application, which leverages the microservices-based solution, through the REST API. Additionally, we will demonstrate how to ensure high-availability of the individual microservices and data sources.

Vehicle for Learning

In a similar vein to the publicly available Twitter, Facebook, and Google REST APIs, we will build the Virtual-Vehicles REST API. The Virtual-Vehicles REST API will constitute a collection of vehicle-themed microservices. Collectively, the microservices will offer a comprehensive set of functionality to the end-user, an application developer. They, in turn, will use the functionality of the Virtual-Vehicles REST API to build applications and games for their end-users.

Technology Choices

There are a seemingly infinite number of technology choices for building microservices and REST APIs. Your choice of development languages, databases, application servers, third-party libraries, API gateway, logging, monitoring, automated testing, ORM or ODM, and even the IDE, all define your technology stack.

For the Virtual-Vehicles solution, we will use the following key technologies:

What is RestExpress?

According to their website, RestExpress, composes best-of-breed open-source tools to enable quickly creating RESTful microservices that embrace industry best practices. Built from the ground-up for container-less, microservice architectures, RestExpress is the easiest way to create RESTful APIs in Java. RestExpress is an extremely lightweight, fast, REST engine and API for Java. RestExpress is a thin wrapper on Netty IO HTTP handling. RestExpress lets you create performant, stand-alone REST APIs rapidly. RestExpress provides several Maven archetypes, which we will use as a basis for our microservices.

RestExpress will also drive our technology decisions to use Java EE, Maven, MongoDB, and Netty.

Virtual-Vehicles Microservices

Adhering to the first few microservice architectural principles listed above, organized around business capabilities, single responsibility, and high cohesion, we first must determine the proper number of microservices, and their individual responsibilities. In the case of our solution, we will break down Virtual-Vehicles’ business capabilities into the following microservices:

Virtual-Vehicles Services

Microservice Purpose (Business Capability)
Authentication Service Manage API clients and JWT authentication
Vehicle Service Manage virtual vehicles
Maintenance Service Manage maintenance on vehicles
Valet Parking Service Manage a valet service to park for vehicles
Sales Service Manage the buying and selling of vehicles
Registration Service Manage registration of vehicles to owners
Auction Service Manage a virtual car auction
Car Show Service Manage a virtual car show
Interaction Service Manage interaction of users with vehicles

For simplicity in this post’s example, we will only be exploring the (4) services shown above in bold.

This segmentation of service functionality is unlike what we might encounter in traditional monolithic, n-tier applications, and SOA-based architecture. Traditional applications were built around application-centric functionality or business’ organizational structure. Microservices, however, are client-centric and built around business capabilities.

REST API Functionality

The next decision we need to make is required functionality. What are the operational requirements of each business segment, represented by the microservices? Additionally, what are the nonfunctional requirements, such as monitoring, logging, and authentication. Requirements are translated into functionality, which is translated into the available resources exposed via the service’s RESTful endpoints.

For the Virtual-Vehicles microservices solution, based on a hypothetical set of business and non-functional requirements, we will expose the following resources. Collectively, they will compose the REST API:

Virtual-Vehicles REST API Resources

Microservice Purpose (Business Capability) Functions
Authentication Service Manage API clients and
JWT authentication
  • Create a new API client (public)
  • Read, filter, sort, count, paginate API clients (admin)
  • Read a single API client (public)
  • Update an existing API client (public)
  • Delete an existing API client (admin)
  • Create new JWT (public)
  • Validate a JWT (internal)
  • Service health ping (admin)
Vehicle Service Manage virtual vehicles
  • Create a new vehicle (public)
  • Read, filter, sort, count, paginate vehicles (admin)
  • Read a single vehicle (public)
  • Update an existing vehicle (public)
  • Delete an existing vehicle (admin)
  • Validate a JWT (internal)
  • Service health ping (admin)
Maintenance Service Manage maintenance on vehicles
  • Create a new maintenance record (public)
  • Read, filter, sort, count, paginate maintenance records (admin)
  • Read a single maintenance record (public)
  • Update an existing maintenance record (public)
  • Delete an existing maintenance record (admin)
  • Validate a JWT (internal)
  • Service health ping (admin)
Valet Parking Service Manage a valet service to park for vehicles
  • Create a new valet parking transaction (public)
  • Read, filter, sort, count, paginate valet parking transactions (admin)
  • Read a single valet parking transaction (public)
  • Update an existing valet parking transaction (public)
  • Delete an existing valet parking transaction (admin)
  • Validate a JWT (internal)
  • Service health ping (admin)

Reviewing the table above, note the first five functions for each service are all basic CRUD operations: create (POST), read (GET), readAll (GET), update (PUT), delete (DELETE). The readAll function also has find, count, and pagination functionality using query parameters.

All services also have an internal authenticateJwt function, to authenticate the JWT, passed in the HTTP request header, before performing any operation. Additionally, all services have a basic health-check function, ping (GET). There are only a few other functions required for our Virtual-Vehicles example, such as for creating JWTs.

I’ve labeled each function as to suggested user scope. Scopes include public, admin, and internal. As a consumer of the REST API, you may only want to expose certain functionality to your general end-user (public). Additional functionality may be reserved for an administrative user (admin) or only yourself as a developer (internal). Creating a new vehicle might be a common end-user feature. However, the ability to permanently delete one or more vehicles may be reserved for an admin-level user, or not exposed at all.

REST API Patterns

We will not spend a lot of time discussing patterns for building REST APIs. There are many useful materials available on the Internet regarding industry-standard patterns for REST API resource URI construction. The two presentations I recommend above by Les Hazlewood, CTO of Stormpath, are excellent. Also, Microservices.ioRestApiTutorial.com, swagger.io, and raml.org websites offer solid overviews of REST patterns and RESTful standards.

A common RESTful anti-pattern, which is hard to avoid as a OOP developer, is the temptation to use verbs versus nouns and method-like names, in resource URIs. Remember, we are not designing an end-user application. We are building an API, used by API consumers (application developers), to build a variety of platform and language-agnostic applications. Functions like paintCar, changeOil, or parkVehicle are not something the API should define. The Vehicle microservice exposes the update operation, which allows an application developer to change the car’s paint color in their paintCar method. Similarly, the valet service exposes the create operation, which allows the application developer to create a function to park the vehicle (or car, or truck, in a garage, or parking lot, etc.). A good REST API allows for maximum end-user flexibility.

Part Two

In Part Two, we will install a copy of the Virtual-Vehicles project from GitHub. In Part Two, we will gain a basic understanding of how RestExpress works. Finally, we will discover how to get the Virtual-Vehicles microservices up and running.

Virtual-Vehicles Architecture

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

1 Comment

Instant Oracle Database and PowerShell How-to Book

Recently, I finished reading Geoffrey Hudik‘s Instant Oracle Database and PowerShell How-to ebook, part of Packt Publishing‘s Instant book series. Packt’s Instant book series promises short, fast, and focused information; Hudik’s book delivers. It’s eighty pages deliver hundreds of pages worth of the author’s knowledge in a highly-condensed format, perfect for today’s multi-tasking technical professionals.

Hudik’s book is ideal for anyone experienced with the Oracle Database 11g platform, and interested in leveraging Microsoft’s PowerShell scripting technology to automate their day-to-day database tasks. Even a seasoned developer, experienced in both technologies, will gain from the author’s insight on overcoming several PowerShell and .NET framework related integration intricacies.

As a busy developer, I was able to immediately start implementing many of the book’s recipes to improve my productivity. I especially enjoyed the way the book builds upon previous lessons, resulting in a useful collection of foundational PowerShell scripts and modules. Building on top of these, save time when automating a new task.

image

Getting started with the book’s examples required a few free downloads from Oracle and Microsoft. Starting with a Windows 7 Enterprise laptop, I downloaded and installed Oracle Database 11g Express Edition (Oracle Database XE) Release 2,
Oracle Developer Tools for Visual Studio, and Oracle SQL Developer IDE. I also made sure my laptop was up-to-date with the latest Visual Studio 2012, .NET framework, and PowerShell updates.
If you are new to administering Oracle databases or using Oracle SQL Developer IDE, Oracle has some excellent interactive tutorials online to help you get started.

, , , , ,

Leave a comment

Database First Development with Entity Framework 5 in Visual Studio 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.

HealthTracker EDMX Diagram

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:

  1. Create the new SQL Server database;
  2. Create the database objects;
  3. Create a new C# Class Library Project in Visual Studio 2012 Solution;
  4. Add a new ADO.NET Entity Data Model to project;
  5. Create a new Database Connection;
  6. Import the database objects into the EDM;
  7. Modify the EDM to accommodate the scalar-valued functions;
  8. Populate the database with sample data;
  9. 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

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

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

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

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

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

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

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

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

Entity Data Model Wizard - Choose Your Data 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

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

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

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 the of the Entity Data Model

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

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

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

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

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

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

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=&quot;data source=gstafford-windows-laptop\DEVELOPMENT;initial catalog=HealthTracker;persist security info=True;user id=DemoLogin;password=DemoLogin123;MultipleActiveResultSets=True;App=EntityFramework&quot;" 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

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.

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

13 Comments

First Impressions of Code First Development with Entity Framework 5 in Visual Studio 2012

Build a Data Access Layer (DAL) in Visual Studio 2012 using the recently-released Entity Framework 5 with Code First Development. Leverage Entity Framework 5’s eagerly anticipated enum support, as well as Code First Migrations and Lazy Loading functionality. 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 August of this year (2012), along with the release of Visual Studio 2012, Microsoft announced the release of Entity Framework 5 (EF5). According to Microsoft, “Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.

EF5 offers multiple options for development, including Model First, Database First, and Code First. Code First, first introduced in EF4.1, allows you to define your model using C# or VB.Net POCO classes. Additional configuration can optionally be performed using attributes on your classes and properties or by using a fluent API. Code First will then create a database if it doesn’t exist, or Code First will use an existing empty database, adding new tables, according to Microsoft.

Exploring Code First

I started by looking at EF5’s Code First development to create a new database. I chose to create a Data Access Layer (DAL) for a health tracker application. This was loosely based on a mobile application I developed some time ago. You can track food intake, physical activities, and how much water you drink per day.

To further explore EF5’s new enum support feature, I substituted two of the database tables, containing static ‘system’ data, for enumeration classes. Added in EF5, Enumeration (enum) support, was the number one user-requested EF feature. Also added to EF5 were spatial data types and my personal favorite, table-valued functions (TVF).

Once the new database was created, I tested another newer EF feature, Code First Migrations. Code First Migrations, introduced in EF4.3, is a feature that allows a database created by Code First to be incrementally changed as your Code First model evolves.

Creating the HealthTracker DAL

Final View of Both Projects in Solution

Final View of Both Projects in Solution

Here are the steps I followed to create my EF5 Entity Framework DAL project:

1. Create a new C# Class Library, ‘HealthTracker.DataAccess’, in a Solution, ‘HealthTracker’. Target the .NET Framework 4.5.

New Class Library

New Class Library

2. Create (4) POCO (Plain Old CLR Object) classes: Person, Activity, Meal, and Hydration, shown below. Note the virtual properties. According to Microsoft, this enables the Lazy Loading feature of Entity Framework. Lazy Loading means that the contents of these properties will be automatically loaded from the database when you try to access them.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
        public virtual List<Meal> Meals { get; set; }
        public virtual List<Activity> Activities { get; set; }
        public virtual List<Hydration> Hydrations { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Activity
    {
        public int ActivityId { get; set; }
        public DateTime Date { get; set; }
        public ActivityType Type { get; set; }
        public string Notes { get; set; }
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Hydration
    {
        public int HydrationId { get; set; }
        public DateTime Date { get; set; }
        public int Count { get; set; }
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Meal
    {
        public int MealId { get; set; }
        public DateTime Date { get; set; }
        public MealType Type { get; set; }
        public string Description { get; set; }
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}

3. Add data annotations to the POCO classes. Annotations are used to specify validation for fields in the data model. Data annotations can include required, min, max, string length, and so forth. Annotations are part of the System.ComponentModel.DataAnnotations namespace. As an alternative to annotations, you can use the Code First Fluent API.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Person
    {
        public int PersonId { get; set; }

        [Required]
        [StringLength(100,
        ErrorMessage = "Name must be less than 100 characters."),
        MinLength(2,
        ErrorMessage = "Name must be less than 2 characters.")]
        public string Name { get; set; }

        public virtual List<Meal> Meals { get; set; }
        public virtual List<Activity> Activities { get; set; }
        public virtual List<Hydration> Hydrations { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Activity
    {
        public int ActivityId { get; set; }

        [Required]
        public DateTime Date { get; set; }

        [Required]
        [EnumDataType(typeof(ActivityType))]
        public ActivityType Type { get; set; }

        [StringLength(100,
        ErrorMessage = "Note must be less than 100 characters.")]
        public string Notes { get; set; }

        [Required]
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Hydration
    {
        public int HydrationId { get; set; }

        [Required]
        public DateTime Date { get; set; }

        [Required]
        [Range(0, 20,
        ErrorMessage = "Hydration amount must be between 0 - 20.")]
        public int Count { get; set; }

        [Required]
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public class Meal
    {
        public int MealId { get; set; }

        [Required]
        public DateTime Date { get; set; }

        [Required]
        [EnumDataType(typeof(MealType))]
        public MealType Type { get; set; }

        [StringLength(100,
        ErrorMessage = "Description must be less than 100 characters.")]
        public string Description { get; set; }

        [Required]
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
    }
}

4. Create (2) enum classes: MealType and ActivityType, shown below.

using System;
using System.Collections.Generic;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public enum ActivityType
    {
        Treadmill,
        Jogging,
        WeightTraining,
        Biking,
        Aerobics,
        Other
    }
}
using System;
using System.Collections.Generic;
using System.Linq;

namespace HealthTracker.DataAccess.Classes
{
    public enum MealType
    {
        Breakfast,
        MidMorning,
        Lunch,
        MidAfternoon,
        Dinner,
        Snack,
        Brunch,
        Other
    }
}

5. Add Entity Framework (System.Data.Entity namespace classes) to 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 - Add Entity Framework

Manage NuGet Packages – Add Entity Framework

6. Add a DbContext class, ‘HealthTrackerContext’, shown below. According to Microsoft, “DbContext represents a combination of the Unit-Of-Work and Repository patterns and enables you to query a database and group together changes that will then be written back to the store as a unit.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Entity;
using HealthTracker.DataAccess.Classes;

namespace HealthTracker.DataAccess
{
    public class HealthTrackerContext : DbContext
    {
        public DbSet<Activity> Activities { get; set; }
        public DbSet<Hydration> Hydrations { get; set; }
        public DbSet<Meal> Meals { get; set; }
        public DbSet<Person> Persons { get; set; }
    }
}

7. Add ‘Code First Migrations’ by running the Enable-Migrations command in the NuGet Package Manager Console. This allowed me to make changes to POCO classes, while keeping the database schema in sync. This only needs to be run once.

Enable-Migrations -ProjectName HealthTracker.DataAccess -Force -EnableAutomaticMigrations -Verbose
Package Manager Console - Code First Migrations

Package Manager Console – Code First Migrations

This creates the following class in a new Migrations folder, called Configuration.cs

namespace HealthTracker.DataAccess.Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<HealthTracker.DataAccess.HealthTrackerContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }

        protected override void Seed(HealthTracker.DataAccess.HealthTrackerContext context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }
}

Running the Update-Database command in Package Manager Console updates the database when you have made changes.

Update-Database -ProjectName HealthTracker.DataAccess -Verbose -Force

I found the Code First Migrations feature a bit confusing at first. It took several tries to work out the correct command to use to add Code First Migrations to my data access project, and the command to call later, to update the database based on my project.

Dependency Graph

Here is the way the class relationships should look using Visual Studio 2012’s powerful new Dependency Graph feature:

Visual Studio 2012 Dependency Graph of HealthTracker

Visual Studio 2012 Dependency Graph of HealthTracker

Testing the Project

To test the entities, I created a simple console application, which references the above EF5 class library. The application takes a person’s name as input. It then adds and/or updates Activities, Meals, and Hydrations, for that Person, depending on whether or not that Person already exists in the database.

1. Create a new C# Console Application, ‘HealthTracker.Console’.

New Console Application

New Console Application

2. Add a reference to the ‘HealthTracker.DataAccess’ project.

Add Reference to HealthTracker.DataAccess Project

Add Reference to HealthTracker.DataAccess Project

3. Create the Examples class and add the code below to the Main method class. As seen in the Examples class, using Micorsoft’s LINQ to Entities with the new .NET Framework 4.5, makes querying the entities very easy.

using System;

namespace HealthTracker.ConsoleApp
{
    class Program
    {
        private static string _newName = String.Empty;

        static void Main()
        {
            GetNameInput();
            var personId = Examples.FindPerson(_newName);

            if (personId == 0)
            {
                Examples.CreatePerson(_newName);
                personId = Examples.FindPerson(_newName);
            }

            Examples.CreateActivity(personId);
            Examples.CreateMeals(personId);
            Examples.UpdateOrAddHydration(personId);

            Console.WriteLine("Click any key to quit.");
            Console.ReadKey();
        }

        private static void GetNameInput()
        {
            Console.Write("Input Person's Name: ");
            var readLine = Console.ReadLine();
            if (readLine != null) _newName = readLine.Trim();
            if (_newName.Length > 2) return;
            Console.WriteLine("Name to short. Exiting program.");
            GetNameInput();
        }
    }
}
using System;
using System.Globalization;
using System.Linq;
using HealthTracker.DataAccess;
using HealthTracker.DataAccess.Classes;

namespace HealthTracker.ConsoleApp
{
    public class Examples
    {
        private static readonly DateTime Today = DateTime.Now.Date;

        /// <summary>
        /// Example of finding a Person in database.
        /// </summary>
        /// <param name="name">Name of the Person</param>
        /// <returns>Person's unique PersonId</returns>
        public static int FindPerson(string name)
        {
            using (var db = new HealthTrackerContext())
            {
                var personId = db.Persons.Where(person => person.Name == name)
                    .Select(person => person.PersonId).FirstOrDefault();

                if (personId == 0)
                    Console.WriteLine("Person, {0}, could not be found...", name);
                else
                    Console.WriteLine("PersonId {0} retrieved...", personId);

                return personId;
            }
        }

        /// <summary>
        /// Example of Adding a new Person
        /// </summary>
        /// <param name="name">Name of the Person</param>
        public static void CreatePerson(string name)
        {
            using (var db = new HealthTrackerContext())
            {
                // Add a new Person
                db.Persons.Add(new Person { Name = name });
                db.SaveChanges();
                Console.WriteLine("New Person, {0}, added...", name);
            }
        }

        /// <summary>
        /// Example of updating existing Hydration count.
        /// Else adding new Hydration if it doesn't exist.
        /// </summary>
        /// <param name="personId">Person's unique PersonId</param>
        public static void UpdateOrAddHydration(int personId)
        {
            using (var db = new HealthTrackerContext())
            {
                var existingHydration = db.Hydrations.FirstOrDefault(
                    hydration => hydration.PersonId == personId
                        && hydration.Date == Today);

                if (existingHydration != null && existingHydration.HydrationId > 0)
                {
                    existingHydration.Count++;
                    db.SaveChanges();
                    Console.WriteLine("Existing Hydration count increased to {0}...",
                        existingHydration.Count.ToString(CultureInfo.InvariantCulture));
                    return;
                }

                db.Hydrations.Add(new Hydration
                {
                    PersonId = personId,
                    Date = Today,
                    Count = 1
                });
                db.SaveChanges();
                Console.WriteLine("New Hydration added...");
            }
        }

        /// <summary>
        /// Example of adding two Meals.
        /// </summary>
        /// <param name="personId">Person's unique PersonId</param>
        public static void CreateMeals(int personId)
        {
            using (var db = new HealthTrackerContext())
            {
                db.Meals.Add(new Meal
                {
                    PersonId = personId,
                    Date = Today,
                    Type = MealType.Breakfast,
                    Description = "(2) slices toast, (1) glass orange juice"
                });

                db.Meals.Add(new Meal
                {
                    PersonId = personId,
                    Date = Today,
                    Type = MealType.Lunch,
                    Description = "(1) protein shake, (1) apple"
                });

                db.SaveChanges();
                Console.WriteLine("Two new Meals added...");
            }
        }

        /// <summary>
        /// Example of adding an Activity
        /// </summary>
        /// <param name="personId">Person's unique PersonId</param>
        public static void CreateActivity(int personId)
        {
            using (var db = new HealthTrackerContext())
            {
                db.Activities.Add(new Activity
                {
                    PersonId = personId,
                    Date = Today,
                    Type = ActivityType.Treadmill,
                    Notes = "30 minutes, 500 calories"
                });

                db.SaveChanges();
                Console.WriteLine("New Activity added...");
            }
        }
    }
}

The console output below demonstrates the addition a new Person, Susan Jones, with an associated Activity, two Meals, and a Hydration.

Console Output - Adding New Person

Console Output – Adding New Person

The console output below demonstrates inputting Susan Jones a second time. Observe what happened to the output.

Console Output - Updating Existing Person

Console Output – Updating Existing Person

Below is a view of the newly created SQL Express ‘HealthTracker.DataAccess.HealthTrackerContext’ database. Note the four database tables, which correspond to the four POCO classes in the project. Why did the database show up in SQL Express or LocalDB? Read the ‘Where’s My Data?‘ section of this tutorial by Microsoft. Code First uses SQL Express or LocalDB by default, but you add a connection to your project to target SQL Server.

View of HealthTracker SQL Express Database

View of HealthTracker SQL Express Database

You can switch your connection SQL Server at anytime, even after the SQL Express or LocalDB database has been created, and updated using Code First Migrations. That’s what I chose to do after completing this post’s example. I prefer doing my initial development with a temporary SQL Express or LocalDB database while the entities and database schema are still evolving. Once I have the model a fairly stable point, I create the SQL Server database, and continue from there evolving the schema.

Conclusion

Having worked with earlier editions of Entity Framework’s Database First and Model First development, in addition to other ORM packages, I was impressed with EF5’s new features as well as the Code First development methodology. Overall, I feel EF5 is another big step toward making Entity Framework a top-notch ORM, on par with other established products on the market.

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

5 Comments

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

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

Console Output of Demonstration

Introduction

(Update 07/10/2015: All code now on GitHub)

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 the 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 the JDBC statements to return data: Statement, PreparedStatement, and CallableStatement.
  • Demonstrate how to call stored procedures with input and/or output parameters.
  • Demonstrate how to return either single values or a result set from a database using a stored procedure.

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 a 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 blogs 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 an additional permission to call or ‘Execute’ the database queries.

Add Execute Permission to User

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

View of New Stored Procedures in Database

View of New Stored Procedures in Database

Data Sources, Connections, and Properties

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

New Java Project in NetBeans

New Java Project in NetBeans

Add JDBC Driver 4.0 Jar File

Add JDBC Driver 4.0 Jar File

Examples

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

Example 1: A Simple Statement

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

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

        try {
            String sql = "SELECT ROUND(AVG([Weight]), 2)";
            sql += " FROM Production.Product";
            sql += " WHERE ([Weight] &gt; 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] &gt; 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 lone double number value.

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

	SELECT ROUND(AVG([Weight]), 2)
	FROM Production.Product
	WHERE ([Weight] &gt; 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 parameters 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] &gt; 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 start with a particular sequence of character (i.e. ‘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&lt;String&gt; of employee names
     */
    public List&lt;String&gt; getEmployeesByLastNameCS(String lastNameStartsWith) {

        CallableStatement cstmt = null;
        ResultSet rs = null;
        List&lt;String&gt; employeeFullName = new ArrayList&lt;&gt;();

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

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

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

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

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

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

	SELECT p.Name AS [Product], p.ProductNumber, p.Color, p.Size, m.Name AS [Model]
	FROM Production.ProductModel AS m INNER JOIN
		Production.Product AS p ON m.ProductModelID = p.ProductModelID
	WHERE (p.Color = @productColor) AND (p.Size = @productSize)
	ORDER BY [Model], [Product]
END
GO
    /**
     * CallableStatement example, (2) input parameters, db returns ResultSet
     *
     * @param color
     * @param size
     * @return List&lt;Product&gt; of Product objects
     */
    public List&lt;Product&gt; getProductsByColorAndSizeCS(String color, String size) {

        CallableStatement cstmt = null;
        ResultSet rs = null;
        List&lt;Product&gt; productList = new ArrayList&lt;&gt;();

        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 the 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&lt;String&gt; 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() &gt; 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&lt;Product&gt; productList =
                examples.getProductsByColorAndSizeCS(color, size);
        timer.setEndTime(System.nanoTime());
        System.out.println("GetProductsByColorAndSizeCS");
        System.out.println("Duration (ms): " + timer.getDuration());
        if (productList.size() &gt; 0) {
            System.out.println("Products found (color: '" + color
                    + "', size: '" + size + "'): " + productList.size());
            System.out.println("First product: "
                    + productList.get(0).getProduct()
                    + " (" + productList.get(0).getProductNumber() + ")");
        } else {
            System.out.println("No products found with color '" + color
                    + "' and size '" + size + "'");
        }
        System.out.println("");

        examples.closeConnection();
    }
}
Results with Plans Cached

Results of Running the Application

SQL Statement Performance

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

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

9 Comments