Building Oracle JET CRUD Applications Interacting with an Oracle Database

As the name implies, a CRUD (Create, Read, Update, Delete) application is supposed to not only access the underlying data, but also to allow the user to perform the entire set of operations to manipulate that data, including create, update, and delete operations.

This article illustrates how you can build a CRUD application that supports both data access and data manipulation operations. To build the article sample, you will use Oracle JET, Oracle Database, Oracle REST Data Services, and Oracle SQL Developer.

What You Will Learn

This article provides an example to bring together the software components outlined above in order to build an Oracle JET CRUD application interacting with a dataset in Oracle Database. In particular, you'll walk through a set of step-by-step instructions, showing you how to

• Prepare a dataset in the underlying database to be used in an Oracle JET application
• Configure Oracle REST Data Services to turn an Oracle Database instance into a RESTful API service.
• Test an Oracle REST Data Services service's endpoints from the command line using the cURL tool
• Build an Oracle JET application that interacts with an Oracle Database instance via an Oracle REST Data Services RESTful service.
• Perform insert, update, and delete operations in an Oracle JET application against data stored in the underlying database

The quickest way to implement the tasks above is to take advantage of an existing Oracle JET application, modifying its functionality as needed. So, the example application discussed in the rest of this article is based on the OracleJET-CommonModel-CRUD sample covered in detail in the "Creating a CRUD Application Using Oracle JET" section of the JavaScript Extension Toolkit (JET) Developing Applications with Oracle JET guide.

The key point is that the OracleJET-CommonModel-CRUD sample supports every type of CRUD operation, and it includes a mock REST server that simulates (mocks) a RESTful service. So, by using this sample as the starting point for another demo CRUD application, you get a ready-to-use view, and you need to make some minor changes to the viewModel to make it work with a real RESTful service.

Preparing a Dataset in the Underlying Database

This article's sample uses the data from the departments relational table that can be found in the HR sample schema in an Oracle Database instance. Because the set of data operations to be discussed here is not limited to access operations only but also includes data modification operations, such as cerate and update, it would be a good idea to create a separate database schema with a departments table copied from the HR schema. This can be done with the following commands in Oracle SQL Developer or in another SQL tool of your choice, when you are logged in with DBA privileges:

 
     
CREATE USER hr1 IDENTIFIED BY pswd;

GRANT CONNECT, RESOURCE TO hr1;

GRANT SELECT ON hr.departments TO hr1;

ALTER USER hr1 quota unlimited on USERS;

Then, you can connect using the just-created account and create a copy of the hr.departments table, as follows:

 
  
CONN h1/pswd;

CREATE TABLE departments AS SELECT * FROM hr.departments;

ALTER TABLE departments ADD CONSTRAINT DEPT_ID_PK PRIMARY KEY(department_id);

So, now you have a departments database table that will be used as a data source in this article's sample application. Although this is simply a regular relational table, you can easily turn it into a RESTful data source with the help of Oracle REST Data Services. As a result, you will be able to access and manipulate data in this table, employing Oracle JET's built-in mechanisms that are used for consuming data via the Common Model and Collection API.

The following section discusses how you can configure a RESTful service on the hr1.departments table, using the RESTful Services feature in Oracle SQL Developer.

Turning a Database Table into a RESTful Data Source Using Oracle REST Data Services

Oracle REST Data Services comes with both Oracle Database and Oracle SQL Developer. In a nutshell, Oracle REST Data Services allows your application to access and manipulate your Oracle Database resources via REST, mapping HTTP(S) operations (GET, POST, PUT, DELETE, and so on) to database transactions. You can easily enable REST access to database objects using the Oracle REST Data Services feature in Oracle SQL Developer.

In general, you'll need to complete the following steps before you can access a database schema object, such as a table or view, via REST:

• Install Oracle REST Data Services using Oracle SQL Developer. This can be done with Oracle SQL Developer's Oracle REST Data Services Installation Wizard, as described in detail in the "Installing and Administering REST Data Services with SQL Developer" hands-on-lab.
• Enable Oracle REST Data Services queries to access necessary database objects. In this particular example, you'll need to enable Oracle REST Data Services access to the hr1.departments database table. For details, refer to the "Configuring a RESTful Service upon a Database Table" section of my preceding Oracle Technology Network article, "Oracle JET and Oracle Database—a Beautiful Combination." Also, you can check out the "AutoREST Enable Database Objects" hands-on-lab for more details.
• Run an Oracle REST Data Services process. You can run it using the Oracle REST Data Services Run Wizard, which can be launched by clicking Tools and selecting the REST Data Services>Run menu item in Oracle SQL Developer. Once you have completed the wizard, you should see the running Oracle REST Data Services process in the Processes pane in Oracle SQL Developer.

Once you have enabled Oracle REST Data Services queries to the hr1.departments table, you might want to test REST endpoints related to it. This can be easily done with the cURL command-line tool. Thus, to test a GET endpoint, you might issue the following command from a system terminal:

 

$ curl -i http://yourdatabasehost:9090/ords/hr1/departments/10

If everything went well, you should get output that looks as follows:

 
     
HTTP/1.1 200 OK
Content-Type: application/json
ETag: "VOKy9X8AhWVZRMuJcpCVhxoS/+pYK0BAPT0jNrD/IJB4xRSzKhVamEqrQq94DhSK+B/Z1D
BZsvS0pEJypNRsPQ=="
Transfer-Encoding: chunked

{"department_id":10,"department_name":"Administration","manager_id":200
,"location_id":1700,"links":[{"rel":"self","href":"http://localhost:909
0/ords/hr1/departments/10"},{"rel":"edit","href":"http://localhost:9090
/ords/hr1/departments/10"},{"rel":"describedby","href":"http://localhos
t:9090/ords/hr1/metadata-
catalog/departments/item"},{"rel":"collection","href":"http://localhost
:9090/ords/hr1/departments/"}]}

In the following example, you test a POST operation, creating a new record in the underlying hr1.departments table:

 
   
$ curl -i -H "Content-Type: application/json" -X POST -d "{ 
\"department_id\":12, \"department_name\": \"Advertisement \", 
\"manager_id\":202, \"location_id\":1700 }" 
"http://localhost:9090/ords/hr1/departments/" 

If the create operation above was successful, the output produced should look like this:

 
    
HTTP/1.1 201 Created
Content-Type: application/json
Content-Location: http://localhost:9090/ords/hr1/departments/12
ETag:
"ttn42msyo7PXMlQhOzEcYh53GteYTTHD652ANSxf2tX2eqnOm4M+FxmW8wcdjGrhVO2ALY
4jYfo2XzHvVg9+qQ=="
Location: http://localhost:9090/ords/hr1/departments/12
Transfer-Encoding: chunked

{"department_id":12,"department_name":"Advertisement 
","manager_id":202,"location_id":1700,"links":[{"rel":"self","href":"ht
tp://localhost:9090/ords/hr1/departments/12"},{"rel":"edit","href":"htt
p://localhost:9090/ords/hr1/departments/12"},{"rel":"describedby","href
":"http://localhost:9090/ords/hr1/metadata-
catalog/departments/item"},{"rel":"collection","href":"http://localhost
:9090/ords/hr1/departments/"}]}

Now, once again, for testing purposes, you might want to delete the just-created row, using the following command:

 
 
$ curl -i -X DELETE "http://localhost:9090/ords/hr1/departments/12"
HTTP/1.1 200 OK
Content-Type: application/json
Transfer-Encoding: chunked

{"rowsDeleted":1}

Modifying the OracleJET-CommonModel-CRUD Sample to Fetch from Oracle REST Data Services

As mentioned, the OracleJET-CommonModel-CRUD sample is used as the starting point in this article. So, now that you have tested REST endpoints to be used in the article's sample, you can move on to the copy of OracleJET-CommonModel-CRUD you've unpacked on your system, and you can modify it to fetch from this Oracle REST Data Services service.

In fact, to make your OracleJET-CommonModel-CRUD application fetch data from the Oracle REST Data Services service created as described above, you need to make minor adjustments to the existing code in only two application files: main.js and app.js, as described below.

In main.js, edit the first parameter of the getJSON function, changing it to the URL of the Oracle REST Data Services service you just created for working with hr1.departments data. This URL might look as follows: http://yourdatabasehost:9090/ords/hr1/departments/. Then, make sure to change the value of the id parameter passed to the MockRESTServer constructor to department_id, because this is the name of the id field used in the hr1.departments table. Also, you need to change the url and idUrl parameters passed to the MockRESTServer constructor. The following code snippet shows the changes to be made:

 

...

    $.getJSON("http://yourdatabasehost:9090/ords/hr1/departments/",
          function (data) {
            new MockRESTServer(data, {id:"department_id", 
              url:/^http:\/\/yourdatabasehost:9090\/ords\/hr1\/departments(\?limit=([
\d]*))?$/i,
              idUrl:/^http:\/\/ 
yourdatabasehost:9090\/ords\/hr1\/departments\/([\d]+)$/i});   

...

In app.js, you need to change the value of the self.serviceURL variable, and edit the parseDept and parseSaveDept functions; both are located within the viewModel function. The changes to be made are highlighted in bold in the following snippet:

 
     
   function viewModel() {
 ...
      self.serviceURL = 'http://yourdatabasehost:9090/ords/hr1/departments';

...

      function parseDept(response) {
        if (response['Departments']) {
           var innerResponse = response['Departments'][0];
           if (innerResponse.links.Employees == undefined) {
                 var empHref = '';
            } else {
                 empHref = innerResponse.links.Employees.href;
            }
            return {DepartmentId: innerResponse['department_id'], 
DepartmentName: innerResponse['department_name']};
         }
         return {DepartmentId: response['department_id'], 
DepartmentName: response['department_name'], 
LocationId:response['location_id'], ManagerId:response['manager_id']};
      }

      function parseSaveDept(response) {
         return {DepartmentId: response['department_id'], 
DepartmentName: response['department_name'], 
LocationId:response['location_id'], ManagerId:response['manager_id']};
      }

...

Also note that the code related to the Employees links has been excluded from both parseDept and parseSaveDept.

This is what your OracleJET-CommonModel-CRUD application page should look like when loaded after you have made the above changes.

Figure 1. The sample page after changing the data source to the Oracle REST
Data Services service defined on the hr1.departments database table.

Figure 1: The sample page after changing the data source to the Oracle REST Data Services service defined on the hr1.departments database table.

So, now your application can fetch data from the database using the Oracle REST Data Services service you defined on a database table. Using CRUD terms, the application can now perform a read operation, consuming a RESTful service. Your next step is to make it perform data manipulation operations, such as create and update, consuming that same RESTful service.

Customizing the URL Operations

In the "Turning a Database Table into a RESTful Data Source Using Oracle REST Data Services" section earlier in this article, you saw how to perform data manipulation operations against a RESTful data service with the cURL command-line tool, explicitly specifying the type of HTTP method, the URL, and the payload (if applicable). Now, how can we do all this in our application?

Actually, Oracle JET does not require you to explicitly specify a type of HTTP request and a payload when it comes to a CRUD operation; Oracle JET can do that for you implicitly. The only thing you have to specify explicitly is the service URL.

It is important to note, however, that the Oracle JET Common Model's oj.Model and oj.Collection objects allow you to customize the data service URLs, explicitly providing the data needed to prepare a request to the underlying RESTful service. For that, you can define a customURL callback function, which will override the default behavior.

As you will learn in the next section, this function can be used as a good position from which you can debug what is happening when a certain operation is performed, getting an understanding of how the Common Model and Collection API works under the hood.

Actually, you don't have to write it from scratch. The Oracle JET site's cookbook has the Customizations—Custom URL recipe, which might give you a good grasp of what such a callback might look like. Adapting the code from the cookbook recipe for this particular example, you might write the following function and insert it into the viewModel function in app.js:

 
     
     function getUrl(operation, collection, options) {
           var retObj = {};
           retObj['type'] = getVerb(operation);
           if (operation === "delete" || operation === "update" || 
operation === "create") {
               retObj['url'] = self.serviceURL + "/" + collection.id;
            }
            else {
                   retObj['url'] = self.serviceURL;
            }
            retObj['headers'] = {};
            retObj['headers']['testopt'] = 'value';
            return retObj;
      };

As you might notice, the above function refers to a getVerb function, which you also have to insert into viewModel somewhere before the above function. The getVerb function might look like this:

 
    
     function getVerb(verb) {
           if (verb === "read") {
               return "GET";
           }
           if (verb === "update") {
               return "PUT";
           }
           if (verb === "delete") {
               return "DELETE";
           }
           if (verb === "create") {
               return "PUT"
           }
      };

Then, you can move on to the oj.Model.extend and oj.Collection.extend definitions in the viewModel function and edit them as follows:

 
     
      var Department = oj.Model.extend({
              //urlRoot: self.serviceURL,
              customURL: getUrl,
              parse: parseDept,
              parseSave: parseSaveDept,
              idAttribute: 'DepartmentId'
       });

       var myDept = new Department();

       var DeptCollection = oj.Collection.extend({
              //url: self.serviceURL + "?limit=50",
              customURL: getUrl,
              model: myDept,
              comparator: "DepartmentId"
       });

As you can see, in both cases you used the customURL property, setting it to getUrl, which is used as the customURL callback in this particular example.

Getting into the Details with a Debugger

If you're new to Oracle JET, it can be quite a challenge to get a grasp of how the Common Model and Collection API works. This is where a technique known as debugging might help a lot. According to the Oracle JET documentation, when it comes to debugging Oracle JET web applications, you can use your favorite browser's debugging facilities. Thus, for example, if you are a Mozilla Firefox user, you might take advantage of Firebug, which includes a powerful JavaScript debugger.

With the help of a debugger, you can trace your application's execution flow, discovering what pieces of code are executed when a certain task is performed. By setting a breakpoint, you can pause execution on any line and then drill down arbitrarily into underlying libraries to examine the execution flow. You can also explore the values of variables at each execution step.

Turning to our example, let's take a closer look at the getUrl function defined within the viewModel function, as described in the previous section. Looking through getUrl, you might notice that the object defined within it contains the URL and the type of HTTP request method to be used, but it does not contain request payloads. It is fairly clear that a PUT operation used either to update a department or create a new department assumes a request payload. And if you don' t want to rely on the default behavior, can you explicitly specify a payload to send to the REST service? The answer to this question can be easily gleaned from the ojmodel.js script, as you will see in a moment.

Assuming you have already loaded the application page in your browser, activate the browser's debugger and set a breakpoint at the last line of the getUrl function in app.js:

 
    
return retObj

Then, click the Reload button in the browser to make the application scripts run in the debugging mode. The execution should stop on the line where you set the breakpoint, as shown in Figure 2:

Figure 2: Setting a breakpoint within a customURL callback function allows you to quickly get into your
Oracle JET app's underlying code to follow the execution flow.

From then on, you can continue execution one line at a time by pressing F10 (Step Over). After just a few steps with F10, you'll be taken to the following line of code in ojmodel.js:

 
     
  if (!result.hasOwnProperty('data')) {

This is shown in Figure 3:

Figure 3: Getting into the ojmodel.js script to reveal all possible properties of the return object defined in a
customURL callback.

This line of code reveals that the return object defined in a customURL callback function can have a property called "data," which allows you to manually compose a request payload. (If you recall, this property was not used in the Customizations—Custom URL recipe.) So, you can return to the getUrl function and add the data property to the return object, as shown in the following listing:

 
     
     function getUrl(operation, collection, options) {
         var retObj = {};
         retObj['type'] = getVerb(operation);
         if (operation === "delete" || operation === "update" || operation === "create") {
            retObj['url'] = self.serviceURL + "/" + collection.id;
            retObj['data'] = JSON.stringify({"DepartmentId":collection.id, 
"DepartmentName":collection.attributes.DepartmentName, 
"LocationId":collection.attributes.LocationId,"ManagerId":collection.attributes.ManagerId});
         }
         else {
            retObj['url'] = self.serviceURL;
         }
         retObj['headers'] = {};
         retObj['headers']['testopt'] = 'value';
         return retObj;
     };

Don't forget to save the app.js file after you have made the changes.

After that, you can turn the debugger off.

Unmocking CRUD Handlers

As you learned in previous sections, you can participate in forming Ajax requests that Oracle JET makes when accessing REST services through the Common Model by supplying a customURL callback function. With this feature you can, for example, assign each CRUD operation to a certain HTTP method and explicitly compose a request payload. Looking through the customURL callback used in our example, you can get a clear vision of what a request sent to the REST service will look like when a certain type of CRUD operation is performed. So, let's perform one to be sure that things work as expected.

For example, to test out an update operation, you can simply click any department record shown in the application page in your browser. Then, in the Change Department Name dialog box, change the department name and click the Change button. (The nice thing about using an existing application as a template is that you don't have to create everything from scratch.)

Figure 4: Testing an update operation.

If you now look at the department record shown on the page, you will see that the department has a new name, indicating that the view data has been successfully changed. That doesn't automatically mean, however, that this change has been applied to the corresponding entry in the database. To check that, you don't even need to use a SQL tool. You can just click the Reload button in your browser to make your application reload data from the hr1.departments table.

What you will see is that the reloaded page does not reflect the change you made, meaning that the underlying data has remained unchanged.

The point is that the OracleJET-CommonModel-CRUD application used in our article sample as a template uses a mock server that only simulates data manipulation CRUD operations, such as update, but does not actually perform them against the underlying data.

This issue can be easily fixed by removing CRUD operation handlers in the MockRESTServer.js script that implements the mock server and can be found in the js/rest application folder. Thus, to unmock the update operation, you need to remove the following code in MockRESTServer.js (you can just comment it out):

 

// update
    $.mockjax({
        url: self.idUrl,
        urlParams: ['id'],
        type: 'PUT',
        response: function (settings) {
            var id = settings.urlParams.id, data;
            // Find id in data list
            var data = self.getData();
            for (var i = 0; i < data.length; i++) {
                var x = data[i];
                if (x[self.idField] == id) {
                    // Update data fields
                    data[i] = JSON.parse(settings.data);
                    this.responseText = data[i];
                }
            }

        }
    });

After making these changes, reload the application page in your browser and perform an update operation again.

You'll end up with the same result: no changes in the underlying dataset. This time, however, the Oracle REST Data Services server generates the following error messages, indicating that a request from your application has been received and processed. You can see these messages by checking logs on the Oracle REST Data Services side.

 
   
  Oracle REST Data Services - Log window

  [DEPARTMENT_ID, in, class java.math.BigDecimal], 
  [DEPARTMENT_NAME, in, class java.lang.String], 
  [LOCATION_ID, in, class java.math.BigDecimal], 
  [MANAGER_ID, in, class java.math.BigDecimal]]
  
  with values: {1=10, 2=null, 3=null, 4=null}
  Oct 04, 2017 9:05:43 PM
  
  INFO: ORA-01407: cannot update ("HR1"."DEPARTMENTS"."DEPARTMENT_NAME") to NULL

  java.sql.SQLException: ORA-01407: cannot update ("HR1"."DEPARTMENTS"."DEPARTMENT_NAME") to NULL

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)

...

At first glance, it's not easy to determine the cause of problem. What can be seen, however, is that the values to be used for updating the record fields have been set to null for some reason.

Looking into Ajax Options

Of course, there might be several different ways in which you can try to determine the cause of the above problem. One simple way is to look into the parameters sent with the request from your Oracle JET application to the Oracle REST Data Services service. Once again, this can be easily done with the help of debugging.

Open app.js for editing and add the following code to the viewModel function:

 
    
    oj.ajax = function (ajaxOptions) {
        return $.ajax(ajaxOptions);
    };

Note: In fact, defining oj.ajax allows you to override the underlying Ajax call. For details, refer to the "Integrating REST Services" section in the JavaScript Extension Toolkit (JET) Developing Applications with Oracle JET guide, and refer to Customizations > Custom oj.ajax in the Oracle JET site's cookbook. In this particular example, however, the only reason you define oj.ajax is to simplify obtain, during debugging, the request's Ajax options passed to it as the parameter.

Then, activate your browser debugger and set a breakpoint to the return line of the viewModel function. After that, launch an update operation. Once the execution stops at the breakpoint, move on to the debugger's Watch window. In this window, find and expand the ajaxOptions object, as shown in Figure 5.

Figure 5: Looking into the request's Ajax options.

Within ajaxOptions, you should notice a property called "data." Looking at it reveals that the names of the data fields do not match those in the hr1.departments table. Obviously, that is the cause of the problem.

So, return to app.js and edit the getUrl function within viewModel, as follows:

 
   
     function getUrl(operation, collection, options) {
 
...

            retObj['data'] = JSON.stringify({"department_id":collection.id, 
"department_name":collection.attributes.DepartmentName, 
"location_id":collection.attributes.LocationId,"manager_id":collection.attributes.ManagerId});

...

         return retObj;
     };

Once the above changes have been made, do not forget to click the Reload button in your browser. Then, you can repeat an update operation. This time it should work as expected.

Adjusting the Create Operation

Now that you have made the update operation work properly, let's move on to the create operation. To make it work, you'll need to unmock its handle in the MockRESTServer.js script, as discussed in the "Unmocking CRUD Handlers" section earlier in this article. Also, make sure to edit the following function within the viewModel function in app.js:

 
     
     self.addDepartment = function (formElement, event) {
         var id = $("#newDepartId").val();
         var recordAttrs = {DepartmentId: id, DepartmentName: $("#newDepartName").val(), ManagerId: null, LocationId: 
null};
         //links: {Employees: {rel: 'child', href: self.serviceURL + '/' + id + '/Employees'}}};
         this.DeptCol().create(recordAttrs, {
         'contentType': 'application/json',
         success: function (response) {
         //console.log('Success in Create');
         },
         error: function (jqXHR, textStatus, errorThrown) {
         console.log('Error in Create: ' + textStatus);
         }
       });
      };

After reloading, the create operation should work properly as well.

Figure 6: Testing the create operation.

After clicking the Add Department button, a new department record should appear not only on the application page, but also in the underlying hr1.departments table.

Conclusion

The example in this article illustrated how you can turn your Oracle Database instance into a RESTful API service with the help of Oracle REST Data Services, so that it can be then used as a data source for an Oracle JET CRUD application. From an Oracle JET application standpoint, you built REST endpoints for the Common Model and Collection API, including those endpoints that support data manipulation operations, such as update and create.