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

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

Introduction

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

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

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

Installation and Configuration

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

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

MySQL Demo User Account

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

MySQL Workbench IDE

MySQL Workbench IDE

Configuring Demo User Login

Configuring Demo User Login

Configuring Demo User Administrative Roles

Configuring Demo User Administrative Roles

Configuring Demo User Account Limits

Configuring Demo User Account Limits

Configuring Demo User Schema Privileges

Configuring Demo User Schema Privileges

New MySQL Database Connection

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

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

New Connection Wizard - MySQL Driver

Locating the Driver in the New Connection Wizard

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

New Connection Wizard - Customize Connection

Customize the Connection in the New Connection Wizard

New Connection Wizard - Database Schema

Sakila Database Doesn’t Contain Additional Schema

Choosing a Name for the Connection

Choosing a Name for the Connection

New Database Connection for demoUser

New Database Connection for MySQL Sakila Database

New Java Class Library

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

Create New Java Class Library Project

Create New Java Class Library Project

Naming New Java Class Library

Naming New Java Class Library

Entity Classes from Database

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

Choosing Database Tables and Views

Choosing Database Tables and Views

Entity Class Options

Entity Class Options

Entity Mapping Options

Entity Mapping Options

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

New Java Class Project with Entities

Java Class Project with JPA Entity Classes

Adding Entity IDs to Views

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

Adding Id to ActorInfo Entity

Adding and Entity ID to ActorInfo Entity

Id Added to Entity Class

Entity ID Added to Entity Class

ActorInfo.java Entity Class contents:

New Java Web Application

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

New Web Application Project

New Web Application Project

Naming New Web Application

Naming New Web Application

Configuring Server and Settings

Configuring Server and Settings

Configuring Frameworks

Configuring Frameworks

New Java Web Application Project

New Java Web Application Project

RESTful Web Services from Entity Classes

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

Adding MySQL Entity Class Library

Adding MySQL Entity Class Library

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

Adding RESTful Web Service from Entities

Adding RESTful Web Service from Entity Classes

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

Choosing Entity Classes

Choosing Entity Classes

Chosen Entity Classes

Chosen Entity Classes

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

Creating Data Source for Persistence Unit

Creating Data Source for Persistence Unit

Creating Data Source and JNDI Name

Creating Data Source and JNDI Name

Creating Persistence Unit

Creating Persistence Unit

Persistence Unit (persistence.xml) contents:

Generating Classes Using Jersey Options

Generating Classes Using Jersey Options

New Java Web Application with RESTful Web Services

Java Web Application with RESTful Web Services

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

Libraries Loaded by NetBeans to Java Web Application

Libraries Loaded by NetBeans to Java Web Application

Creating RESTful Web Services Test

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

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

Configuring the REST Test Client

Configuring the REST Test Client

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

RESTful Web Services Deployed to GlassFish Server

RESTful Web Services Deployed to GlassFish Server

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

Testing RESTful Web Services from NetBeans

Testing RESTful Web Services from NetBeans Using Chrome

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

Calling the RESTful Web Services Directly

Calling the RESTful Web Services Directly

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

Fiddler2 Request Example

Fiddler2 Request Example

Conclusion

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

Links

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

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

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

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

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

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

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

  1. #1 by Hari on June 18, 2013 - 3:48 am

    How can we extend new methods on generated methods?

  2. #2 by pritchard12 on July 4, 2013 - 5:11 pm

    I modified this example, so that I’m just using a database with one table to simplify my learning process. I was getting an error each time I followed the instructions for the sakila database as well. The error is:

    javax.servlet.ServletException: java.lang.IllegalArgumentException: No [EntityType] was found for the key class [com.mysql.entities.Pets] in the Metamodel – please verify that the [Entity] class was referenced in persistence.xml

    Any advice on how to fix? I have added a com.mysql.entities.Pets in the persistence.xml file but that did not fix this issue.

    Thanks

    • #3 by smvorwerk on October 24, 2013 - 4:50 am

      pritchard12- I ran into the same error at first, what you need to do is go to your persistence.xml file and in design view, uncheck “Include all entity classes in “ServiceNameHere” Module” then click on “Add Class” and add each one of your classes individually, then recompile and test.

  3. #4 by Bruce on November 13, 2013 - 5:09 pm

    This is a great tutorial thanks! Would the resulting war file run under a Servlet container like Tomcat or do you need a full J2EE server like Glassfish?

  4. #6 by azhar on August 25, 2014 - 6:28 am

    Hello Sir,
    This is good tutorial and it help me a lot. But still i m stuck in how to insert a record into mysql using webservices.

  5. #7 by purnima on January 5, 2016 - 5:35 am

    i am able to run the whole tutorial but when i do GET test in browser it gives 500 internal server error plz help me out

  6. #8 by banzai on January 13, 2016 - 1:36 am

    hello sir, thanks for made this tutorial. I want to know about generated restful webservices from oracle db (in my database have relation table) using netbeans 8.1, because i had issue error 505 (Content may not have Container-Containee Relationship. See Raw View for content.) when i choose method GET application/xml or application/json. Please help sir, i had not idea for this.

  1. Using soapUI to Test RESTful Web Services | ProgrammaticPonderings
  2. Java web server | My Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: