Introduction
When implementing a Relational Database Management System (RDBMS), many enterprise software developers tend to favor Oracle 11g or Microsoft SQL Server relational databases, depending on their technology stack. However, there are several excellent alternative relational databases, including MySQL. In fact, MySQL is the world’s most popular open source database software, according to Oracle.
MySQL is available on over 20 platforms and operating systems including Linux, Unix, Mac and Windows, according to the MySQL website. Like Oracle and Microsoft’s flagship RDBMS, MySQL Server comes in at least four flavors, ranging from the free Community Edition, demonstrated here, to a full-featured, enterprise-level Cluster Carrier Grade Edition. Support for MySQL, like Oracle and Microsoft, extends beyond just technical support. MySQL provides JDBC, ODBC, .NET drivers for Java and .NET development, as well as other languages. MySQL is supported by many popular IDE’s, including MySQL’s own RDBMS IDE, MySQL Workbench. Lastly, like Oracle and Microsoft, MySQL provides extensive documentation, tutorials, and even sample databases, built using recommended architectural patterns.
In this post, we will use JDBC to map JPA entity classes to tables and views within a MySQL database. We will then build RESTful web services, EJB classes, which communicate with MySQL through the entities. We will separate the JPA entities into a Java Class Library. The class library will be referenced by the RESTful web services. The RESTful web services, part of a Java Web Application, will be deployed to GlassFish, where they are accessed with HTTP methods and tested.
Installation and Configuration
If you’ve worked with Microsoft SQL Server or particularly Oracle 11g, you’ll have a minimal learning curve with MySQL. Basic installation, configuration, and integration within your Java applications is like Oracle and Microsoft. Start by downloading and installing the latest versions of MySQL Server, MySQL Workbench, MySQL JDBC Connector/J Driver, and MySQL Sakila sample database. If on Linux, you could use the command line, or a native application management application, like Synaptic Package Manager, to perform most of the installations. To get the latest software and installation and configuration recommendations, I prefer to download and install them myself from the MySQL web site. All links are included at the end of this post.
For reference when following this post, I have installed MySQL Server 5.5.x on 64-bit Ubuntu 12.10 LTS, running within a Windows version of Oracle VM VirtualBox. I will be using the latest Linux version of NetBeans IDE 7.3 to develop the demonstration project. I will host the project on Oracle’s GlassFish Open Source Application Server 3.1.2.2, running on Ubuntu. Lastly, I will be referring to the latest JDK 1.7, in NetBeans, for the project.
MySQL Demo User Account
Once MySQL is installed and running, I suggest adding a new MySQL demo user account, to the Sakila database for this demonstration, using MySQL Workbench. For security, you should limit the user account to just those permissions necessary for this demonstration, as detailed in the following screen-grabs. You can also add the user from the command line, if you are familiar with administering MySQL in that way.
MySQL Workbench IDE
Configuring Demo User Login
Configuring Demo User Administrative Roles
Configuring Demo User Account Limits
Configuring Demo User Schema Privileges
New MySQL Database Connection
To begin development in NetBeans, first create a new JDBC database connection to the MySQL Sakila database. In the Services tab, right-click on the Databases item and select New Connection… Use the new demo user account for the connection.
Note in the first screen-grab below, that instead of using the default NetBeans JDBC MySQL Connector/J driver version, I have downloaded and replaced it with the most current version, 5.1.24. This is not necessary, but I like to use the latest drivers to avoid problems.
Locating the Driver in the New Connection Wizard
Make sure to test your connection before finishing, using the ‘Test’ button. It’s frustrating to track down database connection issues once you start coding and testing.
Customize the Connection in the New Connection Wizard
Sakila Database Doesn’t Contain Additional Schema
Choosing a Name for the Connection
New Database Connection for MySQL Sakila Database
New Java Class Library
Similar to an earlier post, create new Java Class Library project in NetBeans. Select New Project -> Java -> Java Class Library. This library will eventually contain the JPA entity classes, mapped to tables and views in the MySQL Sakila database. Following standard n-tier design principles, I prefer separate the data access layer (DAL) from the service layer. You can then reuse the data access layer for other types of data-consumers, such as SOAP-based services.
Create New Java Class Library Project
Naming New Java Class Library
Entity Classes from Database
Next, we will add entity classes to our project, mapped to several of the MySQL Sakila database’s tables and views. Right-click on the project and select New -> Entity Classes from Database… In the next window, choose the database connection we made before. NetBeans will then load all the available tables and views from the Sakila database. Next, select ‘actor_info(view)’, ‘film_actor’, and ‘film_list(view)’. Three related tables will also be added automatically by NetBeans. Not the warning at the bottom of the window about the need to specify Entity IDs. We will address this next.
Choosing Database Tables and Views
Entity Class Options
Entity Mapping Options
When selecting ‘Entity Classes from Database…’, NetBeans adds the ‘EclipseLink (JPA 2.0)’ global library to the project. This library contains three jars, including EclipseLink 2.3.x, Java Persistence API (JPA) 2.0.x, and state model API for JPQL queries. There is a newer EclipseLink 2.4.x library available from their web site. The 2.4.x version has many new features. You can download and replace NetBeans’ EclipseLink (JPA 2.0) library by creating a new EclipseLink 2.4.x library, if you want to give its new features, like JPA-RS, a try. It is not necessary for this demonstration, however.
Java Class Project with JPA Entity Classes
Adding Entity IDs to Views
To eliminate warnings displayed when we built the entities, Entity ID’s must be designated for the two database views we selected, ‘actor_info(view)’ and ‘film_list(view)’. Database views (virtual tables), do not have a primary key defined, which NetBeans requires for the entity classes. NetBeans will guide you through adding the ID, if you click on the error icon shown below.
Adding and Entity ID to ActorInfo Entity
Entity ID Added to Entity Class
ActorInfo.java Entity Class contents:
| package com.mysql.entities; | |
| import java.io.Serializable; | |
| import javax.persistence.Basic; | |
| import javax.persistence.Column; | |
| import javax.persistence.Entity; | |
| import javax.persistence.Id; | |
| import javax.persistence.Lob; | |
| import javax.persistence.NamedQueries; | |
| import javax.persistence.NamedQuery; | |
| import javax.persistence.Table; | |
| import javax.xml.bind.annotation.XmlRootElement; | |
| @Entity | |
| @Table(name = "actor_info") | |
| @XmlRootElement | |
| @NamedQueries({ | |
| @NamedQuery(name = "ActorInfo.findAll", query = "SELECT a FROM ActorInfo a"), | |
| @NamedQuery(name = "ActorInfo.findByActorId", query = "SELECT a FROM ActorInfo a WHERE a.actorId = :actorId"), | |
| @NamedQuery(name = "ActorInfo.findByFirstName", query = "SELECT a FROM ActorInfo a WHERE a.firstName = :firstName"), | |
| @NamedQuery(name = "ActorInfo.findByLastName", query = "SELECT a FROM ActorInfo a WHERE a.lastName = :lastName")}) | |
| public class ActorInfo implements Serializable { | |
| private static final long serialVersionUID = 1L; | |
| @Basic(optional = false) | |
| @Column(name = "actor_id") | |
| @Id | |
| private short actorId; | |
| @Basic(optional = false) | |
| @Column(name = "first_name") | |
| private String firstName; | |
| @Basic(optional = false) | |
| @Column(name = "last_name") | |
| private String lastName; | |
| @Lob | |
| @Column(name = "film_info") | |
| private String filmInfo; | |
| public ActorInfo() { | |
| } | |
| public short getActorId() { | |
| return actorId; | |
| } | |
| public void setActorId(short actorId) { | |
| this.actorId = actorId; | |
| } | |
| public String getFirstName() { | |
| return firstName; | |
| } | |
| public void setFirstName(String firstName) { | |
| this.firstName = firstName; | |
| } | |
| public String getLastName() { | |
| return lastName; | |
| } | |
| public void setLastName(String lastName) { | |
| this.lastName = lastName; | |
| } | |
| public String getFilmInfo() { | |
| return filmInfo; | |
| } | |
| public void setFilmInfo(String filmInfo) { | |
| this.filmInfo = filmInfo; | |
| } | |
| } |
New Java Web Application
Next, we will create the RESTful Web Services. Each service will be mapped to one of the corresponding JPA entity we just created in the Java class library project. Select New Project -> Java Web -> Web Application.
New Web Application Project
Naming New Web Application
Configuring Server and Settings
Configuring Frameworks
New Java Web Application Project
RESTful Web Services from Entity Classes
Before we will build the RESTful web services, we need to add a reference to the previous Java class library project, containing the JPA entity classes. In the Java web application’s properties dialog window, under Categories -> Libraries -> Compile, add a link to the Java class library project’s .jar file.
Adding MySQL Entity Class Library
Next, right-click on the project and select New -> RESTful Web Services from Entity Classes…
Adding RESTful Web Service from Entity Classes
In the preceding dialogue window, add all the ‘Available Entity Classes’ to the ‘Selected Entity Classes’ column.
Choosing Entity Classes
Chosen Entity Classes
After clicking next, you will prompted to configure the Persistence Unit and the Persistence Unit’s Data Source. Please refer to my earlier post for more information on the Persistence Unit. This data source will also be used by GlassFish, once the project is deployed, to connect to the Sakila MySQL database. The Persistence Unit will use the JNDI name to reference the data source.
Creating Data Source for Persistence Unit
Creating Data Source and JNDI Name
Creating Persistence Unit
Persistence Unit (persistence.xml) contents:
| <?xml version="1.0" encoding="UTF-8"?> | |
| <persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"> | |
| <persistence-unit name="MySQLDemoServicePU" transaction-type="JTA"> | |
| <jta-data-source>jdbc/mysql_sakila</jta-data-source> | |
| <class>com.mysql.entities.Actor</class> | |
| <class>com.mysql.entities.ActorInfo</class> | |
| <class>com.mysql.entities.Film</class> | |
| <class>com.mysql.entities.FilmActor</class> | |
| <class>com.mysql.entities.Language</class> | |
| <exclude-unlisted-classes>true</exclude-unlisted-classes> | |
| <properties/> | |
| </persistence-unit> | |
| </persistence> |
Generating Classes Using Jersey Options
Java Web Application with RESTful Web Services
As part of constructing the RESTful Web Services, notice NetBeans has added several Jersey (JAX-RS) libraries to the project. These libraries also reference Jackson (JSON Processor), Jettison (JSON StAX), MOXy (JAXB), and Grizzly (NIO) APIs.
Libraries Loaded by NetBeans to Java Web Application
Creating RESTful Web Services Test
Finally, we will test the RESTful Web Services, and indirectly the underlying entity classes mapped to the MySQL Sakila database. NetBeans makes this easy. To begin, right-click on the ‘RESTful Web Services’ folder in the Java web application project and select ‘Test RESTful Web Services’. NetBeans will automatically generate all the necessary files and links to test each of the RESTful web services’ operations.
As part of creating the tests, NetBeans will deploy the web application to GlassFish. When configuring the tests in the ‘Configure REST Test Client’ dialog window, make sure to use the second option, ‘Web Test Client in Project’. The first option only works with Microsoft’s Internet Explorer, an odd choice for a Java-based application running on Linux.
Configuring the REST Test Client
Highlighted below in red are the components NetBeans will install on the GlassFish application server. They include the RESTful web services application, a .war file. Each of the RESTful web service are Stateless Session Beans, installed as part of the application. In deployment also includes a JDBC Resource and a JDBC Connection Pool, which connects the application to the MySQL Sakila database. The Resource is automatically associated with the Connection Pool.
RESTful Web Services Deployed to GlassFish Server
After creating the necessary files and deploying the application, NetBeans will open a web browser. allowing you can test the services. Each of the RESTful web services is available to test by clicking on the links in the left-hand navigation menu. NetBeans has generated a few default operations, including ‘{id}’, ‘{from/to}’, and ‘count’, each mapped to separate methods in the service classes. Also notice you can choose to display the results of the service calls in multiple formats, including XML, JSON, and plain text.
Testing RESTful Web Services from NetBeans Using Chrome
We can also test the RESTful Web Services by calling the service URLs, directly. Below, is the results of a my call to the Actor service’s URL, from a separate Windows client machine.
Calling the RESTful Web Services Directly
You can also use applications like Fiddler, cURL, Firefox with Firebug, and Google Chrome’s Advanced REST Client and REST Console to test the services. Below, I used Fiddler to call the Actor service, again. Note the response contains a JSON payload, not XML. With Jersey, you can request and receive JSON from the services without additional programming.
Fiddler2 Request Example
Conclusion
Using these services, you can build any number of server-side and client-side data-driven applications. The service layer is platform agnostic, accessible from any web-browser, mobile device, or native desktop application, on Windows, Linux, and Apple.
Links
MySQL Server: http://www.mysql.com/downloads/mysql
MySQL Connector/J JDBC driver for MySQL: http://dev.mysql.com/downloads/connector/j
MySQL Workbench: http://www.mysql.com/downloads/workbench
MySQL Sakila Sample Database: http://dev.mysql.com/doc/sakila/en/sakila-installation.html
NetBeans IDE: http://www.netbeans.org
EclipseLink: http://projects.eclipse.org/projects/rt.eclipselink
Programmatic Ponderings 
#1 by Hari on June 18, 2013 - 3:48 am
How can we extend new methods on generated methods?
#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.
#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?
#5 by Gary A. Stafford on November 13, 2013 - 10:39 pm
Not a 100%, but I believe you would need Apache TomEE to run the project (http://tomee.apache.org/index.html). However, installing the project is easy with tools like AntDeploy (http://wiki.apache.org/tomcat/AntDeploy) or Apache Tomcat Maven Plugin (http://tomcat.apache.org/maven-plugin-2.0/index.html). I use Maven. I would run a ‘mvn clean install tomcat7:redeploy -e’.
#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.
#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
#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.