CRUD operation in Oracle JET

Hi All,

In this post I am going to demonstrate you how to create a oracle JET application to perform the CRUD operation in Oracle Database.

To start with, we have to create a RESTful service based on "DEPARTMENTS" table in HR Schema of Oracle Database.
 
To create the RESTful service in NetBeans go to the File and select New Project as shown below:

 Now select Java Web option on the left side and Web Application on the right side and click on Next button as shown below:

 Then give the name of the application and click on Next as shown below :

  Now select the server and Java EE version as shown below and click on Next:

Click on Finish.

Now right click on the newly created application and select New and then select New Restful services from database option.

Now select the option New Datasource.

 Now select New Database Connection option as shown below :

Now select the driver as shown below:

Click on Next and fill the database details and test the connection as follows :

Verify the Schema selected.

Click on Next and then Click on Finish.

Now you can see the all available table in HR schema as shown below.

Now select DEPARTMENTS table from left pane and shuffle it to the right and uncheck the option Include Related tables and click  on Next as shown below:

 Specify the package name and click on Next.

Click on Finish.

Now right click on the application and select Test RESTful Web Services.

It will start the Weblogic server and once it will launch the page you can see that the webservice is up and running.

Now we have a RESTful webservice build to get the data from Departments table from database.

Once you have RESTful service ready it will have two classes as follows:


package com.test.kunal;

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;

/**
 *
 * @author kunalku
 */
@Entity
@Table(name = "DEPARTMENTS")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Departments.findAll", query = "SELECT d FROM Departments d"),
    @NamedQuery(name = "Departments.findByDepartmentId", query = "SELECT d FROM Departments d WHERE d.departmentId = :departmentId"),
    @NamedQuery(name = "Departments.findByDepartmentName", query = "SELECT d FROM Departments d WHERE d.departmentName = :departmentName")})
public class Departments implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Column(name = "DEPARTMENT_ID")
    private Short departmentId;
   
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 30)
    @Column(name = "DEPARTMENT_NAME")
    private String departmentName;

    public Departments() {
    }

    public Departments(Short departmentId) {
        this.departmentId = departmentId;
    }

    public Departments(Short departmentId, String departmentName) {
        this.departmentId = departmentId;
        this.departmentName = departmentName;
    }
   
 
    public Short getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentId(Short departmentId) {
        this.departmentId = departmentId;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
}

 and another class is as follows:


package com.test.kunal.service;

import com.test.kunal.Departments;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.ws.rs.Consumes;
import javax.ws.rs.DELETE;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.PUT;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

/**
 *
 * @author kunalku
 */
@Stateless
@Path("com.test.kunal.departments")
public class DepartmentsFacadeREST extends AbstractFacade<Departments> {

    @PersistenceContext(unitName = "TestRestApplicationPU")
    private EntityManager em;

    public DepartmentsFacadeREST() {
        super(Departments.class);
    }

    @POST
    @Path("/add")
    @Consumes(MediaType.APPLICATION_JSON)
    @Produces(MediaType.APPLICATION_JSON)
    @Override
    public void create(Departments entity) {
        super.create(entity);
    }

    @PUT
    @Path("{id}")
    @Consumes(MediaType.APPLICATION_JSON)
    public void edit(@PathParam("id") Short id, Departments entity) {
        super.edit(entity);
    }

    @DELETE
    @Path("{id}")
    public void remove(@PathParam("id") Short id) {
        super.remove(super.find(id));
    }

    @GET
    @Path("{id}")
    @Produces(MediaType.APPLICATION_JSON)
    public Departments find(@PathParam("id") Short id) {
        return super.find(id);
    }

    @GET
    @Override
    @Produces(MediaType.APPLICATION_JSON)
    public List<Departments> findAll() {
        return super.findAll();
    }

    @GET
    @Path("{from}/{to}")
    @Produces(MediaType.APPLICATION_JSON)
    public List<Departments> findRange(@PathParam("from") Integer from, @PathParam("to") Integer to) {
        return super.findRange(new int[]{from, to});
    }

    @GET
    @Path("count")
    @Produces(MediaType.TEXT_PLAIN)
    public String countREST() {
        return String.valueOf(super.count());
    }

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }
   
}

Now we need to create a Oracle JET application to perform the CRUD operation.

Now select File then New Project and then select the Java Web and Create a Web application as shown below :

Name the application as shown below :

Click on Next and select the server and Java EE version as shown below :

Click on Finish.

Now right click on the application select New and then Knockout JET Module as shown below :

Click on Finish.

 Now you can see that it has created views and viewModels folder in the application with home.js and home.html as default files.

 Now here we have two option:

1.  To create a simple page 
2. to use the in-build template and css given by oracle JET. 

The simple page you can see as home.html but if you want to use the oracle template and css then download the OracleJET_QuickStartBasic and copy the required files in your application.

 you can download this from the following URL :

 http://www.oracle.com/technetwork/developer-tools/jet/downloads/index.html

Now unzip this file and go to the location where you have downloaded it.

Now copy the following three folders and index.html file and paste it inside web folder of your application as shown below:

Now  once you run the index.html file you will see the following layout.

Now we will modify it and create a table inside this page. To create the table in Oracle JET following is the code :


<table id="table" summary="Department List" aria-label="Departments Table"
                                           data-bind="ojComponent: {component: 'ojTable',
                                    data: datasource,
                                    columnsDefault: {sortable: 'none'},
                                    columns: [{headerText: 'Department Id',
                                               field: 'DepartmentId'},
                                              {headerText: 'Department Name',
                                               field: 'DepartmentName'}]}">
</table> 

We will create two fields with name "Department Id" and "Department Name" on the page to accept the value to insert or update. 

For creating the input text fields we will write the following piece of code. 

<div class="oj-flex oj-flex-items-pad">
                                        <div class="oj-flex-item deprecated-col-default-width">
                                            <label for="spinnercontrol">Department Id</label>
                                        </div>
                                        <div class="oj-flex-item deprecated-col-default-width">
                                            <input id="departmentIdInput"
                                                   data-bind="ojComponent: {
                       component: 'ojInputNumber',
                       max: 2000, min: 0, step: 1, value: inputDepartmentId}"/>
                                        </div>
                                    </div>    
                                    <div class="oj-flex oj-flex-items-pad">
                                        <div class="oj-flex-item deprecated-col-default-width">
                                            <label for="inputcontrol">Department Name</label>
                                        </div>
                                        <div class="oj-flex-item deprecated-col-default-width">
                                            <input id="departmentNameInput"
                                                   data-bind="ojComponent: {
                       component: 'ojInputText',
                       value: inputDepartmentName}"/>
                                        </div>
                                    </div>  


Now we will create three button to bind Create, Update and Delete Functionality.

<div class="oj-flex oj-flex-items-pad">
                                        <button id="addbutton"
                                                data-bind="click: addRow,
                    ojComponent: { component: 'ojButton', label: 'Add to end' }">
                                        </button>

                                        <button id="updateButton"
                                                data-bind="click: updateRow,
                    ojComponent: { component: 'ojButton', label: 'Update' }">
                                        </button>         
                                        <button id="removeButton"
                                                data-bind="click: removeRow,
                    ojComponent: { component: 'ojButton', label: 'Remove' }">
                                        </button>                                                                                                               
                                    </div>

 The complete code for you index.html will be as follows:


<!DOCTYPE html>
<html lang="en-us">
    <head>
        <title>Oracle JET Quick Start - Basic</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link rel="icon" href="css/images/favicon.ico" type="image/x-icon" />

        <!-- This is the main css file for the default Alta theme -->
        <link rel="stylesheet" href="css/libs/oj/v2.0.0/alta/oj-alta-min.css" type="text/css"/>

        <!-- This is where you would add any app specific styling -->
        <link rel="stylesheet" href="css/override.css" type="text/css"/>

        <!-- RequireJS configuration file -->
        <script data-main="js/main" src="js/libs/require/require.js"></script>
    </head>
    <body>
        <!-- template for rendering navigation items shared between nav bar and nav list -->
        <script type="text/html" id="navTemplate">
        <li><a href="#">
                <span data-bind="css: $data['iconClass']"></span>
                <!-- ko text: $data['name'] --> <!--/ko-->
            </a></li>
    </script>
    <div id="globalBody" class="oj-web-applayout-offcanvas-wrapper oj-offcanvas-outer-wrapper">
        <div class="oj-offcanvas-inner-wrapper">
            <!--
            ** This off-canvas section is used when the browser is resized to a
            ** medium or smaller media query size. It moves the App Navigation into a drawer
            -->
            <!-- off-canvas content -->
            <div id="offcanvas" class="oj-contrast-marker oj-web-applayout-offcanvas oj-offcanvas-start"
                 data-bind="ojModule: {viewName: 'navDrawer'}"></div>
            <div class="oj-web-applayout-scrollable-wrapper">
                <div class="oj-web-applayout-scrollable oj-web-applayout-page">
                    <!-- Header section which contains the Global Branding, Global Navigation,
                    ** and Application Navigation code. Template is located in /js/views/header.html
                    -->
                    <header role="banner" class="oj-web-applayout-header"
                            data-bind="ojModule: 'header'"></header>
                    <!-- This is where your main page content will be loaded -->
                    <div class="oj-web-applayout-max-width oj-web-applayout-content">
                        <div class="oj-flex">



                            <div id="tableDemo" class="oj-flex oj-flex-items-pad">
                                <div class="oj-xl-8 oj-lg-8 oj-md-6 oj-flex-item">
                                    <table id="table" summary="Department List" aria-label="Departments Table"
                                           data-bind="ojComponent: {component: 'ojTable',
                                    data: datasource,
                                    columnsDefault: {sortable: 'none'},
                                    columns: [{headerText: 'Department Id',
                                               field: 'DepartmentId'},
                                              {headerText: 'Department Name',
                                               field: 'DepartmentName'}]}">
                                    </table>
                                </div>
                                <div class="oj-xl-4 oj-lg-4 oj-md-6 oj-flex-item">
                                    <div class="oj-flex oj-flex-items-pad">
                                        <div class="oj-flex-item deprecated-col-default-width">
                                            <label for="spinnercontrol">Department Id</label>
                                        </div>
                                        <div class="oj-flex-item deprecated-col-default-width">
                                            <input id="departmentIdInput"
                                                   data-bind="ojComponent: {
                       component: 'ojInputNumber',
                       max: 2000, min: 0, step: 1, value: inputDepartmentId}"/>
                                        </div>
                                    </div>    
                                    <div class="oj-flex oj-flex-items-pad">
                                        <div class="oj-flex-item deprecated-col-default-width">
                                            <label for="inputcontrol">Department Name</label>
                                        </div>
                                        <div class="oj-flex-item deprecated-col-default-width">
                                            <input id="departmentNameInput"
                                                   data-bind="ojComponent: {
                       component: 'ojInputText',
                       value: inputDepartmentName}"/>
                                        </div>
                                    </div>            
                                    
                                   
                                    <div class="oj-flex oj-flex-items-pad">
                                        <button id="addbutton"
                                                data-bind="click: addRow,
                    ojComponent: { component: 'ojButton', label: 'Add to end' }">
                                        </button>

                                        <button id="updateButton"
                                                data-bind="click: updateRow,
                    ojComponent: { component: 'ojButton', label: 'Update' }">
                                        </button>         
                                        <button id="removeButton"
                                                data-bind="click: removeRow,
                    ojComponent: { component: 'ojButton', label: 'Remove' }">
                                        </button>                                                                                                               
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>   
                </div>
            </div>

        </div></div>
</body>
</html>

Now you have to create a main.js file to handle all the event of the button as well as the functionality which need to invoked at the page load.  The all operation we are doing through the combination of Ajax and Knockout.js in this example. We are using the ajax to invoke the RESTful service and to get the data we are using the Knockout.js. The content of the main.js will be as shown below:

/**
 * Copyright (c) 2014, 2016, Oracle and/or its affiliates.
 * The Universal Permissive License (UPL), Version 1.0
 */
/**
 * Example of Require.js boostrap javascript
 */
requirejs.config({
  // Path mappings for the logical module names
  paths:
 //injector:mainReleasePaths
  {
    'knockout': 'libs/knockout/knockout-3.4.0',
    'jquery': 'libs/jquery/jquery-2.1.3.min',
    'jqueryui-amd': 'libs/jquery/jqueryui-amd-1.11.4.min',
    'promise': 'libs/es6-promise/promise-1.0.0.min',
    'hammerjs': 'libs/hammer/hammer-2.0.4.min',
    'ojdnd': 'libs/dnd-polyfill/dnd-polyfill-1.0.0.min',
    'ojs': 'libs/oj/v2.0.0/debug',
    'ojL10n': 'libs/oj/v2.0.0/ojL10n',
    'ojtranslations': 'libs/oj/v2.0.0/resources',
    'signals': 'libs/js-signals/signals.min',
    'text': 'libs/require/text'
  }
  //endinjector
  ,
  // Shim configurations for modules that do not expose AMD
  shim: {
    'jquery': {
      exports: ['jQuery', '$']
    }
  },
  // This section configures the i18n plugin. It is merging the Oracle JET built-in translation
  // resources with a custom translation file.
  // Any resource file added, must be placed under a directory named "nls". You can use a path mapping or you can define
  // a path that is relative to the location of this main.js file.
  config: {
    ojL10n: {
      merge: {
        //'ojtranslations/nls/ojtranslations': 'resources/nls/menu'
      }
    }
  }
});

/**
 * A top-level require call executed by the Application.
 * Although 'ojcore' and 'knockout' would be loaded in any case (they are specified as dependencies
 * by the modules themselves), we are listing them explicitly to get the references to the 'oj' and 'ko'
 * objects in the callback
 */

 require(['ojs/ojcore', 'knockout', 'jquery', 'ojs/ojknockout', 'ojs/ojrouter','promise', 'ojs/ojinputtext', 'ojs/ojinputnumber','ojs/ojtable',
  'ojs/ojmodule', 'ojs/ojoffcanvas', 'ojs/ojnavigationlist', 'ojs/ojarraytabledatasource'],
function(oj, ko, $)
{  
var router = oj.Router.rootInstance;
    router.configure({
      'home': {label: 'Home', isDefault: true},
      'people': {label: 'People'},
      'library': {label: 'Library'},
      'graphics': {label: 'Graphics'},
      'performance': {label: 'Performance'}
    });
   
      function RootViewModel() {
      var self = this;
      self.router = router;

      // Shared navigation data and callbacks for nav bar (medium+ screens) and nav list (small screens)
      var navData = [
        {name: 'Home', id: 'home',
          iconClass: 'demo-home-icon-24 demo-icon-font-24 oj-navigationlist-item-icon'},
        {name: 'People', id: 'people',
          iconClass: 'demo-education-icon-24 demo-icon-font-24 oj-navigationlist-item-icon'},
        {name: 'Library', id: 'library',
          iconClass: 'demo-library-icon-24 demo-icon-font-24 oj-navigationlist-item-icon'},
        {name: 'Graphics', id: 'graphics',
          iconClass: 'demo-palette-icon-24  demo-icon-font-24 oj-navigationlist-item-icon'},
        {name: 'Performance', id: 'performance',
          iconClass: 'demo-grid-icon-16 demo-icon-font-24 oj-navigationlist-item-icon'}
      ];
      self.navDataSource = new oj.ArrayTableDataSource(navData, {idAttribute: 'id'});
      var smQuery = oj.ResponsiveUtils.getFrameworkQuery(oj.ResponsiveUtils.FRAMEWORK_QUERY_KEY.SM_ONLY);
      self.smScreen = oj.ResponsiveKnockoutUtils.createMediaQueryObservable(smQuery);
      self.navChange = function(event, ui) {
        if (ui.option === 'selection' && ui.value !== self.router.stateId()) {
          // Only toggle navigation drawer when it's shown on small screens
          if (self.smScreen())
            self.toggleDrawer();
          self.router.go(ui.value);
        }
      };
      self.drawerParams = {
        displayMode: 'push',
        selector: '#offcanvas',
      };
      // Called by navigation drawer toggle button and after selection of nav drawer item
      self.toggleDrawer = function() {
        return oj.OffcanvasUtils.toggle(self.drawerParams);
      };
      // Close the drawer for medium and up screen sizes
      var mdQuery = oj.ResponsiveUtils.getFrameworkQuery(oj.ResponsiveUtils.FRAMEWORK_QUERY_KEY.MD_UP);
      self.mdScreen = oj.ResponsiveKnockoutUtils.createMediaQueryObservable(mdQuery);
      self.mdScreen.subscribe(function() {oj.OffcanvasUtils.close(self.drawerParams);});
    }

    oj.Router.defaults['urlAdapter'] = new oj.Router.urlParamAdapter();
    oj.Router.sync().then(
      function () {
        // bind your ViewModel for the content of the whole page body.
        ko.applyBindings(new RootViewModel(), document.getElementById('globalBody'));
      },
      function (error) {
        oj.Logger.error('Error in root start: ' + error.message);
      }
             
             
             
             
             
    );
   
   
   
     function viewModel()
  {
    
    var self = this;
    self.data = ko.observableArray([]);
    $.getJSON("http://localhost:7101/TestRestApplication/webresources/com.test.kunal.departments/").
            then(function (departmentss) {
                $.each(departmentss, function () {
                  
                    self.data.push({
                        DepartmentId: this.departmentId,
                        DepartmentName: this.departmentName
                    });
                });
            });
           
            self.datasource = new oj.ArrayTableDataSource(
                self.data,
                {idAttribute: 'DepartmentId'}
        );
 


    self.addRow = function()
    {
     var dept =      {"departmentId": self.inputDepartmentId(),"departmentName" :self.inputDepartmentName()};
        $.ajax({
                 type: "POST",
                 url: "http://localhost:7101/TestRestApplication/webresources/com.test.kunal.departments/add",
                 data: JSON.stringify(dept),
                 contentType: "application/json; charset=utf-8",
                 dataType: "json",
                 processData: true,
                 success: function (data, status, jqXHR) {
                 
                 },
                 error: function (xhr) {
                     alert(xhr.responseText);
                 }
             });
             self.data.push({
                         'DepartmentId': self.inputDepartmentId(),
                         'DepartmentName': self.inputDepartmentName()
                      });
            
            };
   
    //used to update the fields based on the selected row
    self.updateRow = function()
    {
        var currentRow = $('#table').ojTable('option', 'currentRow');
        if (currentRow != null)
        {
            self.data.splice(currentRow['rowIndex'], 1, {
                         'DepartmentId': self.inputDepartmentId(),
                         'DepartmentName': self.inputDepartmentName()
                      });        
           var dept=self.inputDepartmentId();
           var deptUp =      {"departmentId": self.inputDepartmentId(),"departmentName" :self.inputDepartmentName()};
            $.ajax({
                 type: "PUT",
                 url: "http://localhost:7101/TestRestApplication/webresources/com.test.kunal.departments/"+dept,
                 data: JSON.stringify(deptUp),
                 contentType: "application/json; charset=utf-8",
                 dataType: "json",
                 processData: true,
                 success: function (data, status, jqXHR) {
                 },
                 error: function (xhr) {
                     alert(xhr.responseText);
                 }
             });
        }
    };
   
    //used to remove the selected row
    self.removeRow = function()
    {
        var currentRow = $('#table').ojTable('option', 'currentRow');

        if (currentRow != null)
        {
            var dept=self.inputDepartmentId();
            $.ajax({
                 type: "DELETE",
                 url: "http://localhost:7101/TestRestApplication/webresources/com.test.kunal.departments/"+dept,
                 processData: true,
                 error: function (xhr) {
                     alert(xhr.responseText);
                 }
             });
            self.data.splice(currentRow['rowIndex'], 1);
          
        }
       
      
    };
   
    //intialize the observable values in the forms
    self.inputDepartmentId = ko.observable();
    self.inputDepartmentName = ko.observable();
  }
  var vm = new viewModel;
 
  $(document).ready
  (
    function()
    {
      ko.applyBindings(vm, document.getElementById('tableDemo'));
      $('#table').on('ojoptionchange', currentRowListener);
    }
  );

  function currentRowListener(event, data)
  {
    if (data['option'] == 'currentRow')
    {
      var rowIndex = data['value']['rowIndex'];
      var dept = vm.data()[rowIndex];
      vm.inputDepartmentId(dept['DepartmentId']);
      vm.inputDepartmentName(dept['DepartmentName']);
    }
  };
});  

  Now when you run the application you can see the following page:



 

 Once you select the record the same will be reflected in the input text to update. if you want to update the values then you can change it here and click on the Update button. once you will click it, it will update the record in database as well as in the UI table as shown below:

The same you can see in the database as follows:

 Now if you want to create the record you can create it by clicking on "Add to end" button.

The same you can verify in the database:

If you want to delete the particular record you can select that record and click on delete. It will be deleted from the UI table as well as from the database. Let's suppose we want to delete the record with the Department Id 15. 

Once we click on the Delete button the record will deleted from database as well as the UI table. 

That's all about CRUD operation in Oracle JET. 


Happy Coding :)