6 May 2016

Showing Data from Database table in Oracle JET

Hi All,

In this blog I am going to show you how you can get the data from the database table and how you can show it in a tabular format on UI with the help of REST API, Knockout.js and Oracle JET.

To start with it, you should be familiar with basics of REST API, Knockout.js, jQuery and Oracle JET component.
 
For getting familiar with Knockout.js you can refer following URL :

http://knockoutjs.com/examples/helloWorld.html

To get familiarize with jQuery you can refer the following link :

http://www.w3schools.com/jquery/

To get familiarize with REST API you can refer the following link :

http://www.oracle.com/webfolder/technetwork/tutorials/obe/java/RESTfulWebServices/RESTfulWebservices.htm

and last but not least to get familiarize with Oracle JET component  kindly follow the below  link :

http://www.oracle.com/webfolder/technetwork/jet/uiComponents-formControls.html

Once you will be familiar with these technology then you have to get the IDE to develop the Oracle JET application. Oracle JET application can be developed on either Jdeveloper or NetBeans. I have  used NetBeans for this tutorial.

You can download the latest NetBeans IDE from the following location :

https://netbeans.org/downloads/

Download the NetBeans available with option All.

After downloading the NetBeans, install it on your local system. To enable Oracle JET development in NetBeans we need to install the Oracle JET plugin extension. To install it open your NetBeans IDE and go to Tool --> Plugin as shown below in the screenshot:


Then select Available Plugins tab and select the Oracle JET Support and click on Install button as shown below :


It will start installing the Oracle JET support in IDE. Once the installation is finished then restart the NetBeans to take effect of the new installation.

Now we are ready to go for the development of the Oracle JET application.

In order to fetch the data from database table and show it on the UI with help of Oracle JET we have to basically create two application. One for the REST API and another for the Oracle JET application.

So let's see how we can create a RESTful webservice in NetBeans IDE.

The creation of RESTful webservice in NetBeans is very easy and most of the time you don't have to write the code manually. The NetBeans IDE does the all work automatically.

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.

Now we need to create the Oracle JET application to show the data on the UI.

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: 'enabled'},
                                columns: [{headerText: 'Department Id',
                                           field: 'departmentId'},
                                          {headerText: 'Department Name',
                                           field: 'departmentName'},
                                          {headerText: 'Location Id',
                                           field: 'locationId'},
                                          {headerText: 'Manager Id',
                                           field: 'managerId'}]}">
</table>


Once you put this in your index.html page then its code will look like 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 class="oj-xl-3 oj-lg-3 oj-sm-12 oj-flex-item oj-flex">
                 
                </div>
                <div class="oj-xl-6 oj-lg-9 oj-sm-12 oj-flex-item oj-flex">
                
                   
                   <table id="table" summary="Department List" aria-label="Departments Table"
       data-bind="ojComponent: {component: 'ojTable',
                                data: datasource,
                                columnsDefault: {sortable: 'enabled'},
                                columns: [{headerText: 'Department Id',
                                           field: 'departmentId'},
                                          {headerText: 'Department Name',
                                           field: 'departmentName'},
                                          {headerText: 'Location Id',
                                           field: 'locationId'},
                                          {headerText: 'Manager Id',
                                           field: 'managerId'}]}">
</table>
                   
                   
                   
                </div>
                <!-- You will notice that each section in the page has a set of column widths defined by CSS classes.
                  ** These tell the div to take up so much space when it is in a specific media query range.
                  ** This is part of the responsive grid layout framework provided by JET. As this one is set,
                  ** the div will take up all 12 columns at a small and up media query size. When it hits extra large,
                  ** it will only take up 3 columns.
                -->
              </div>
            </div>
          </div>
        </div>
      </div>
    </div>
  </body>
</html>



Now modify the main.js with the following code : 

/**
 * 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/ojtable',
  'ojs/ojmodule', 'ojs/ojoffcanvas', 'ojs/ojnavigationlist', 'ojs/ojarraytabledatasource'],
  function (oj, ko, $) { // this callback gets executed when all required modules are loaded
    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,
                        locationId:this.locationId,
                        managerId : this.managerId,
                    });
                });
            });
           
            self.datasource = new oj.ArrayTableDataSource(
                self.data,
                {idAttribute: 'departmentName'}
        );
 
  }
 
 
  var vm = new viewModel;
 
  $(document).ready
  (
    function()
    {
      ko.applyBindings(vm, document.getElementById('table'));
    }
  ); 
 
  });



remember to provide the RESTful webservice URL in main.js file.

Once you run the index.html file you can see the page as follows:




Here you can notice one thing that Location Id and Manager Id data is not getting populated. For this we need to check RESTful webservice code and modify accordingly.

The code for Departments.java is as follows :

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
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;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (departmentId != null ? departmentId.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Departments)) {
            return false;
        }
        Departments other = (Departments) object;
        if ((this.departmentId == null && other.departmentId != null) || (this.departmentId != null && !this.departmentId.equals(other.departmentId))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "com.test.kunal.Departments[ departmentId=" + departmentId + " ]";
    }
   
}


Here you can notice that there is no variable defined for managerId and locationId, so we will modify it and put these two variable and its getter and setters. 

Then our Departments.java will look like this after modification:

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
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"),
@NamedQuery(name = "Departments.findByLocationId", query = "SELECT d FROM Departments d WHERE d.locationId = :locationId"),
 @NamedQuery(name = "Departments.findByManagerId", query = "SELECT d FROM Departments d WHERE d.managerId = :managerId")})
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;
   
    @Basic(optional = false)
    @NotNull
    @Column(name = "LOCATION_ID")
    private Short locationId;
    @Basic(optional = false)
    @NotNull
    @Column(name = "MANAGER_ID")
    private Short managerId;

    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;
    }

    public Short getLocationId() {
        return locationId;
    }

    public void setLocationId(Short locationId) {
        this.departmentId = locationId;
    }
   
     public Short getManagerId() {
        return managerId;
    }

    public void setManagerId(Short managerId) {
        this.managerId = managerId;
    }
    @Override
    public int hashCode() {
        int hash = 0;
        hash += (departmentId != null ? departmentId.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Departments)) {
            return false;
        }
        Departments other = (Departments) object;
        if ((this.departmentId == null && other.departmentId != null) || (this.departmentId != null && !this.departmentId.equals(other.departmentId))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "com.test.kunal.Departments[ departmentId=" + departmentId + " ]";
    }
   
}



Now deploy this RESTful webservice again on server and run the index.html page again. You can see the page as shown below with all the data.


So this is how we can create a Oracle JET application to fetch the data from database table and show it on the UI. This is my first JET application so please don't mind if I have done something wrong or not followed the best practices. Hope I will come with more scenario on JET like CRUD operation as soon as possible.

You can download this tutorial from the following location OracleJETApplication_V1

Till then Happy Coding :)

1 on: "Showing Data from Database table in Oracle JET "
  1. Wonderful post - very helpful. I will try this to get data from a database, but do you happen to know how I would be able to call a plsql package from a JET application?

    ReplyDelete