Exposing a MySQL Database with RESTful Web Services
This tutorial shows you how to use NetBeans IDE to expose a MySQL database as a RESTful web service endpoint. You will deploy the "sakila" sample database on GlassFish v2 using the RESTful Web Services plugin. In this tutorial, you learn to create:
This tutorial also requires the RESTful Web Services plugin. In the IDE, go to the Plugin Manager, under the Tools menu, and check whether the RESTful Web Services Plugin is installed, under the Installed tab. If it is not, install it from the Available Plugins tab, as shown below:
Introduction
MySQL is the world's most popular open source database software, with over 100 million copies of its software downloaded or distributed throughout its history. With superior speed, reliability, and ease of use, MySQL has become the preferred choice of corporate IT Managers because it eliminates the major problems associated with downtime, maintenance, administration and support.
The IDE supports rapid development of RESTful web services from databases, by the creation and exposure of entity classes. RESTful web services are services based on HTTP header operations.
Building web services using the RESTful approach is emerging as a popular
alternative to using SOAP-based technologies for deploying services on the
internet, due to its lightweight nature and the ability to transmit data
directly over HTTP.
To celebrate the announcement that Sun Microsystems is acquiring MySQL, Arun Gupta originally created this demonstration, showing how a MySQL database could be exposed as a RESTful web service using the IDE.
Before you use this tutorial, you should see the following:
The IDE contains the MySQL Connector/J JDBC Driver, allowing you to connect to a MySQL database. The goal of this exercise is to create that connection.
Start your MySQL database.
On Windows, type the command mysqld-nt --user root --password=your_root_user_password. You must be in the MySQL bin directory or the bin directory must be on your path. If you get an error that mysql could not connect to localhost, start the MySQL service (Control Panel > Administrative Tools > Services).
On UNIX systems, type the command ./bin/mysqld_safe from the MySQL directory.
Start the IDE. Open the Services tab. Expand Databases > Drivers. Look for the MySQL driver. If it is not there, right-click the Drivers node, choose New Driver and fill in the dialog with the path to your MySQL Connector/J driver.
Right-click on the MySQL (Connector/J) driver node and choose Connect Using... The New Database Connection dialog opens.
Leave the Name and Driver fields with default values. For Database URL, type jdbc:mysql://localhost:sakila. For User Name, type root, and for Password type your "root" user password. The dialog should resemble the following. Click OK when done.
Setting Up a Web Application Project
The goal of this exercise is to create a new Web Application project. This project will contain entity classes and RESTful web services.
Choose File > New Project (Ctrl-Shift-N) from the main menu. Under Categories, select Web.
Under Projects, select Web Application and click Next.
Type sakila in the Project Name field. Note that the Context Path becomes /sakila.
Specify the Project Location to any directory on your computer. You can accept the default location.
Under Server, select GlassFish. GlassFish is a Java EE5-certified application server and is bundled with the Web and Java EE insallation of NetBeans IDE. If you use a server other than GlassFish, such as the bundled Tomcat web server, you may need to configure the server, which is outside the scope of this tutorial.
Leave the Set as Main Project option selected and click Finish. The IDE creates the Sakila project folder. The project folder contains all of your sources and project metadata, such as the project's Ant build script. The HelloWeb project opens in the IDE. The welcome page, index.jsp, opens in the Source Editor in the main window. You can view the project's file structure in the Files window (Ctrl-2), and its logical structure in the Projects window (Ctrl-1).
Generating Entity Classes from a Database
The goal of this exercise is to generate entity classes from the sakila database.
Right-click the sakila node and
choose New > Entity Classes from Database,
as shown below:
In the Database Tables panel, select New Data Source from the Data Source drop-down field. The Create Data Source dialog opens.
Type jndi/sakila in the JNDI Name field. From the Database Connection drop-down field, select jdbc:mysql://localhost/sakila [root on Default schema], as shown below. Click OK. The dialog closes and you return to the Database Tables panel.
Under Available Tables, click "film" and then click Add >. The Database Tables page should now look like the image below. Click Next.
The Entity Classes page opens. Click Create Persistence Unit... Accept all the defaults shown below and click Create. You return to the Entity Classes page.
Under Package, type sakila. The Entity Classes page should now appear as follows:
Click Finish. The IDE creates the entity classes.
Look in the Projects window. You should now see
the following:
Configuring the Persistence File
In Generating Entity Classes from a Database, you created a perstence unit. The configuration of this persistence unit is stored in the file persistence.xml. In your Projects view, you can find persistence.xml in your project, in the Configuration Files node.
You need to change this persistence file to pass the username and password. Open the XML tab. Edit the file to include toplink.jdbc.user and toplink.jdbc.password properties. It matches the following snippet (the parts you add or change are boldface):
Generating RESTful Web Services from Entity Classes
The goal of this exercise is to generate
RESTful web services from the entity classes that
we generated in the previous section.
Right-click the package that contains the entity classes
and choose New > RESTful Web Services from Entity Classes, as shown below:
In the New RESTful Web Services from Entity Classes wizard, click
Add All. You should now see the following:
Click Next. The Generated Classes panel opens. Accept all defaults and click Finish. The IDE now creates the RESTful web services.
The RESTful Web Services node in the Projects window
displays all the RESTful web services in your project.
The value between the square brackets, such as [/films/],
is the value for the URI template. You can also navigate to the
source file by doubling clicking on this node. This view also displays
all the HTTP methods and Sub-resource locator methods. Again, you
can navigate to the methods by double clicking on the nodes.
Now that our entity classes and RESTful web services have been
generated, let's test our application. The IDE provides a useful
utility for testing RESTful web services. We will make use of it
in the next section.
Testing the RESTful Web Services
The goal of this exercise is to try out our application.
Right-click the project node and choose Test RESTful Web Services, as shown below:
The server starts and the application is deployed.
Finally, the browser should display your application, with a link for
each of the web services:
On the left-hand side is the set of root resources. Here they
are named languages and films.
Click Films and then click Test or the URL "http://localhost:8080/sakila/resources/films/". The result is the RESTful representation of the Film table. The default representation shows 10 records from the table where each entry returns the id of the film and a reference to the detailed entry.
You can view more entries by appending /?max=n to the URL. For example, "http://localhost:8080/sakila/resources/films/?max=40" shows the first 40 entries. Additional fields from the table can be displayed by adding getter methods to the "converter.FilmRefConverter" class, such as:
@XmlElement
public String getTitle() {
return entity.getTitle();
}
The getTitle method returns the film title in addition to the fields already returned. The different columns in the table can be viewed by going to the "Services" tab and expanding the sakila database connection created earlier:
The modified output (with film title included) looks like this:
More Exercises
Here are a few more ideas for you to explore:
Create RESTful representations of other tables using the steps described above.
Display the data from different tables in a jMaki-wrapped Yahoo or Dojo data table as explained in TOTD #10.
Display the data retrieved from the database in a JSP page as described in Hello JPA World.
A JRuby-on-Rails application using MySQL is explained here. TOTD #9 explains how JDBC connection pooling in GlassFish can be used for a JRuby-on-Rails application using MySQL.
The key message here is MySQL can be very easily used with GlassFish and NetBeans IDE makes it possible.
To send comments and suggestions, get support, and keep informed on the latest
developments on the NetBeans IDE Java EE development features, join
the mailing list.
