Lab 3: REST Development

Purpose

The REST Development in SQL Developer 4.1 provides users with the full capabilities of REST. REST endpoints can be defined along with basic operations including create, query, update and delete. More complex SQL and PL/SQL operations can be defined and mapped. The results can be displayed in JSON and other formats.

In this lab you will be going through the following:

1. Define Resource Module, Resource Template and Resource Handler
2. Adding a Query to Resource Handler
3. Uploading a Resource Module
4. Testing a RESTful Service

Time to Complete

30 min

Background

This Hands-On-Lab series will show you how to use the RESTful Services feature in Oracle SQL Developer 4.1. You will be installing Oracle REST Data Services bundled in SQL Developer. Once installed, you will be creating, editing, validating, uploading and testing the RESTful Service.

The series include the following labs. These labs build on one another so it must be done in the following order.

1. Installing and Administering Oracle REST Data Services (ORDS) with SQL Developer
2. AutoREST Enable a Schema
3. REST Development
4. Insert, Update and Delete a Restful Service

An Oracle VM Virtual Box virtual machine is set up with the required software and files to execute this Hands-On-Lab

Installed Software :

• Oracle Database 12.1.0.2
• Oracle SQL Developer 4.1.3
• Browser Support :
  • Mozilla Firefox with JSONView extension (to view the JSON formatted in the browser)
• RESTful Services Testing Tool :
  • WizTools.org REST Client release 3.5

What Do You Need?

Before starting this tutorial, you should have:

• Access to Oracle VM Virtual Box virtual machine in which all of the software and relevant files for this Hands-On-Lab are made available.
  
• Completed Lab 1: Installing and Administering REST Data Services with SQL Developer in the series.
• Completed Lab 2: AutoREST Enable Database Objects in the series.

To create a new REST development connection, perform the following steps:

1. Click REST Data Services > Development under View menu.

   

2. The REST Development navigator will appear. Click Connect icon to create a connection.

   

3. Click + icon to create a new connection.

   

4. Enter the below values for the RESTful services connection and click OK.

   Connection Name	ords_dev	The name that you provide that uniquely identifies your connection.
   Username	ords_dev	The ORDS development user name.
   http	select	Specify which protocol you will be using http or https (secure socket layer).
   Hostname	localhost	The host where ORDS is running.
   Port	9090	The port number ORDS is listening.
   Server Path	/ords	The context root where ORDS is deployed.
   Schema/Workspace	/hr/	REST Enabled Schema. Schema must be REST enabled before using REST Development.

   

5. Click OK.

   

6. You will be prompted to enter Username and Password. Enter the following values and click OK.

   Username	ords_dev
   Password	oracle

   

7. Once you are connected, the Modules and Privileges nodes are displayed on the RESTful Services navigator.  In the next section, you will learn how to create your first RESTful service.

   

Perform the following steps to create your first RESTful Service:

1. Right click Modules and select New Module.

   

2. The RESTful Services Wizard appears which will assist you to define a resource module, resource template, and a resource handler. Enter the following values in the wizard and click Next.

   Field	Value	Description
   Module Name	demo	Required field.  The name of the resource module.  The module name is case sensitive and must be a unique module name. The Resource Module groups related resource templates.
   URI Prefix	demo/	Identify the resource module by specifying a Universal Resource Identifier (URI) prefix. Specifying the URI Prefix with the value: demo/ means that all URIs starting with demo/ will be serviced by this resource module. Notice that the Example below the URI Prefix displays the URI which consists of the protocol, host, post, server path, and schema that you have previously specified in the RESTful Services Connection dialog. As you type the URI Prefix, the Example is automatically updated.
   Publish - Make this RESful Service available for use	(check)	Indicate if the module is to be publicly available.  Check-mark the box.
   Pagination Size	25	The default pagination size for a resource handler HTTP operation GET method.  The number of rows to return on each page of a JSON format result set based on a database query.
   Origins Allowed	(leave blank)	A list of origins that are permitted to access the resource templates.

   

3. The RESTful Services Wizard - Step 2 of 4 displays the Resource Template attributes. Enter the following values in the wizard.

   Field	Value	Description
   URI Pattern	employees/	Specify the URI pattern for this resource template. Notice that the Example below the URI template changes as you type the URI template.
   Priority	Low	Prioritize the order of how the resource template should be evaluated.
   ETag	Secure Hash	Include a HTTP entity tag and use the secure hash technique which will generate the version id to uniquely identify the resource version.

   The resource template groups the resource handlers that consist of the HTTP operation method: GET, DELETE, POST and PUT. Only one resource handler per HTTP operation method type is allowed. For example, you cannot have two HTTP GET resource handlers for the same resource template. But you can have one GET and one PUT resource handlers.

   Click Next to define your resource handler for this resource template.

   

4.  The RESTful Services Wizard - Steps 3 of 4 displays the Resource Handler attributes.  You will now define a HTTP GET method for this resource handler. Enter the following values in the wizard and click Next.

   Field	Value	Description
   Method	GET	The HTTP operation method. The possible values are GET, DELETE, POST, and PUT.
   Source Type	Collection Query	The source type that will be specified in the SQL worksheet.
   Data Format	JSON	The resulting data format. By selecting JSON here, the results will be returned in a JSON format.
   Pagination Size	25	The number of rows returned from a query.

   

5. The RESTful Services Wizard - Step 4 of 4 displays the RESTful Summary. Review the summary and click Finish to create your resource module, resource template, and resource handler.

   

6. The demo module is displayed in the RESTful Services navigator. In the next section, you will learn adding a query to your resource handler.

   

To add a query to your resource handler, perform the following steps:

1. Expand demo by clicking + symbol beside it in the RESTful Services navigator.

   

2. Expand employees by clicking + symbol beside it in the RESTful Services navigator.

   

3. Click GET under employees in the RESTful Services navigator. The resource handler editor displays on the right side with its name as GET employees/ .

   

4. You can see three tabs on the GET employees/ resource handler editor as follows:

   Tab Name	Description
   SQL Worksheet	You can specify your query or PL/SQL code in the SQL Worksheet for your resource handler. The SQL Worksheet enables you to connect to your schema, execute your query or PL/SQL, and view the results.  There are many rich features in the SQL Worksheet that are not covered in this lab.  Refer to the SQL Developer On-Line documentation for details on the SQL Worksheet.
   Parameters	You can specify parameters for your query or PL/SQL.
   Details	You can edit your resource handler\u2019s security and other attributes. A URI example of your restful service is also displayed.

   

5. You will be connecting to hr in the SQL Worksheet to test your query. In the resource handler editor's SQL Worksheet tab, locate the connection drop-down menu on the right-side of SQL Worksheet toolbar and select hr.

   

6. If the Connection Information dialog appears prompting for the password, enter oracle and click OK.

   

7. Enter the below query in SQL Worksheet and click Run Statement icon (green triangle) in the tool bar to test the query.
   select * from employees

   

8. The Query Result tab displays the results.

   

Perform the following steps to view the details of your resource handler:

1. Select Details tab in the resource handler editor.

   

2.  Select the red pushpin icon to pin this resource handler editor so that it does not get automatically removed when selecting a different resource handler.

   

3.  The Details tab for the resource handler GET method shows all of its attributes that are editable. In the Examples, the resource template URI is displayed. You will be using this URI to test your RESTful service later in this lab.

   

The next step is to upload the resource module so that the RESTful services definition is updated on the server. Perform the following steps to upload the resource module:

1. On the RESTful services navigator, right-click demo and select Upload.

   

2. An informational message appears that your resource module is uploaded.  Click OK.

   

You are now ready to test the RESTful service using a browser. Perform the following steps to test your RESTful service:

1. Go to the Details tab and click on the Clipboard to copy the URI.

   

2. In the web browser, paste the URI that is copied from the Details tab:
   
   http://localhost:9090/ords/hr/demo/employees/

   

3. The employees data is displayed in the browser in JSON format.

   

4. The number of items returned on each page is based on the set pagination size which is 25. Scroll the page down to view the items. The bottom of the page has the first and next elements which contain a URI pointing to the first page and next page.  

   

Congratulations! You have successfully completed lab 3 from this series.

• Lab 4: Insert, Update, and Delete Data Using RESTful Services
  

• Curriculum Developer: Anjani Pothula

• Other Contributors:Ashley Chen