Exposing a MySQL Database with RESTful Web Services

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:

• A database connection to MySQL
• A new data source using that connection
• Entity classes from the MySQL database
• RESTful web services from those entity classes

Contents

• Introduction
• Creating a Database Connection
• Setting Up a Web Application Project
• Generating Entity Classes from a Database
• Configuring the Persistence File
• Generating RESTful Web Services from Entity Classes
• Testing the RESTful Web Services
• More Exercises

To follow this tutorial, you need the following software and resources.

Software or Resource	Version Required
NetBeans IDE	Web & Java EE download bundle 6.1 or 6.0 Java download bundle 6.5
Java Development Kit (JDK)	version 6 or version 5
The latest stable MySQL (download).
The "sakila" sample MySQL database (download).
Java EE-compliant web or application server	Tomcat web server 6.0 and/or GlassFish application server v2

Both Tomcat and GlassFish can be installed, optionally, with the Web and Java EE distribution of NetBeans IDE. Alternatively, you can visit the GlassFish downloads page or the Apache Tomcat downloads page.

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:

• Introduction to Web Services
• Getting Started with RESTful Web Services on GlassFish
• Connecting to a MySQL Database

Creating a Database Connection

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.

1. 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.
2. 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.
   
   
3. Right-click on the MySQL (Connector/J) driver node and choose Connect Using... The New Database Connection dialog opens.
4. 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.

1. Choose File > New Project (Ctrl-Shift-N) from the main menu. Under Categories, select Web. Under Projects, select Web Application and click Next.
2. Type sakila in the Project Name field. Note that the Context Path becomes /sakila.
3. Specify the Project Location to any directory on your computer. You can accept the default location.
4. 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.
5. 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.

1. Right-click the sakila node and choose New > Entity Classes from Database, as shown below:

   
   

2. In the Database Tables panel, select New Data Source from the Data Source drop-down field. The Create Data Source dialog opens.
3. 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.
   
   
4. Under Available Tables, click "film" and then click Add >. The Database Tables page should now look like the image below. Click Next.
   
   
5. 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.
   
6. Under Package, type sakila. The Entity Classes page should now appear as follows:
   

7. Click Finish. The IDE creates the entity classes.

8. 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):

<persistence-unit name="sakilaPU" transaction-type="JTA">

  <jta-data-source>jndi/sakila</jta-data-source>

  <properties>

    <property name="toplink.jdbc.user" value="root"/>

    <property name="toplink.jdbc.password" value="your_root_user_password"/>

  </properties>

</persistence-unit>

    

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.

1. Right-click the package that contains the entity classes and choose New > RESTful Web Services from Entity Classes, as shown below:
   
2. In the New RESTful Web Services from Entity Classes wizard, click Add All. You should now see the following:
   
3. 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.

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

2. 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.
• Create a CRUD application using jMaki Data Table as described in TOTD #15 or Screencast #Web10.

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.

Send Us Your Feedback

See Also

For more information about using NetBeans IDE 6.0 to develop Java EE applications, see the following resources:

• Developing JAX-WS Web Service Clients
• Getting Started with JAX-WS Web Services
• Getting Started with RESTful Web Services on GlassFish
• Introduction to Developing Web Applications
• Connecting to a MySQL Database
• Advanced Web Service Interoperability
• EJB & Web Service Applications Learning Trail

To send comments and suggestions, get support, and keep informed on the latest developments on the NetBeans IDE Java EE development features, join the nbj2ee@netbeans.org mailing list.