Forum Stats

  • 3,780,896 Users
  • 2,254,454 Discussions
  • 7,879,489 Comments

Discussions

Building and Testing Databound Web Applications using JCS, DBCS and JDeveloper 12c

Bob Rhubart-Oracle
Bob Rhubart-Oracle Member Posts: 692 Silver Badge
edited Feb 16, 2016 4:42PM in Cloud Platform

The Cloud has become an integral part of the IT Ecosystem. Organizations are moving their huge physical IT infrastructure to the Cloud for better governance, lower TCO and better ROI. This article showcases the simplicity of using Oracle Java Cloud Service (JCS), Oracle Database Cloud Service (DCS) and JDeveloper 12c  to develop, deploy and test Web Applications


By Kumar Shahi

Highlights of this article:

  • How JDeveloper 12c eases the development of ADF Web Application
  • How JDeveloper 12c easily connects with JCS for smooth deployment
  • How SQL Developer easily connects to DCS and simplifies DB operations
  • No need to have a physical IT infrastructure to host your application as it uses JCS to host application
  • Access to diagnostic logs to have better monitoring and application testing

This article covers:

  • Creating a simple DCS for hosting Application Schema
  • Creating Data Source in JCS to have access to DB Tables and data
  • Developing simple data-bound ADF Web Application using Jdeveloper 12c
  • Deploying and Testing Web Application on JCS
  • Accessing Diagnostic Logs via Putty

Pre-requisites:

  • JDeveloper 12c (12.1.3.x)
  • Latest Oracle XE Database
  • SQL Developer 4.1 (with JDK 8)
  • Oracle Cloud Access (JCS and DCS)
  • Public/Private Key Pair to be used for account setup
  • HR Schema DDL
  • Putty

Use Case

I have taken a simple use case of creating an HR Application to show and modify employee information.

In this exercise,

  1. Create Database Cloud Service
  2. Connect DCS using SQL Developer
  3. Set up HR User in DCS, create tables and populate data
  4. Develop HR Application using JDeveloper 12c
  5. Create datasource in JCS to connect HR Schema
  6. Set up Putty to tunnel JCS connection
  7. Set up JCS Connection using JDeveloper 12c
  8. Deploy HRApplication to JCS using JDeveloper 12c
  9. Access diagnostic logs using Putty

Solution

Create Database Service

Log in to Oracle Cloud Service at https://myservices.oraclecloud.com/mycloud/faces/gDashboard.jspx

login
Supply Identity Domain,  User Name, User Password and Sign In.

This dashboard page will open:

dashboard

Click "Open Oracle Database Cloud Service" to open the page below:

dbaas

Click the “Create Service” button to create a new Oracle Cloud Database Service. The page below will open. Select Service Level.

The first option, Oracle Database Cloud Service, will be enabled only if the associated Oracle Cloud Storage Account has a container created and available.

Otherwise, choose the Oracle Database Cloud Service-Virtual Image option.

dcs_servicelvl.png

Billing Frequency needs to be specified even if you have a trial account. I have selected “Monthly."

Click “Next.” You'll see this screen, where you can specify the database version:

dbaas2

Click “Next” and select your Software Edition. I have selected Enterprise Edition.

dbaas3

Click “Next.”

dbaas4

Assign values to the required attribute:

  • Service Name – Specify Database Cloud Service Name
  • Compute Storage – Select configuration you would like to use
  • VM Public Key – Supply Public Key (created as part of prerequisite)
  • Usable Database Storage – Specify storage requirement for the database service
  • Total data file storage – Specify total data file storage
  • Administration Password – Specify Password for the SYS user
  • Confirm Password specified above
  • DB (Database) name – Automatically populated. User can modify if required.
  • PDB (Pluggable Database) Name – Automatically populated. User can modify if required.
  • Backup destination –  The “Both Cloud and Local Storage” option will  be available only if container is configured. Otherwise, user can select “Local Storage Only.”
    If backup destination is “Both Cloud and Local Storage,”  user will have to supply:
    • Cloud Storage Container – e.g., identityDomainName/containerName
    • Cloud Storage User name – cloud admin user name (normally email id)
    • Cloud Storage Password – cloud admin password.


Click “Next.” Confirm on the landing page to start creation of DBaas Service.

Database Cloud Setup Progress
It may take a few minutes to set up Database Cloud Service. Click “Service Name” to see the list of activities being performed.

Connect Database Service using SQL Developer

This requires two steps: Setting up SSH and Creating New Database Connection.

Setting up SSH

Start SQL Developer 4.x.

sqldeveloper

Open the “View” menu and click “SSH”. It will open the SSH Panel in SQL Developer.

sqldeveloper-ssh

Right click “SSH Hosts” and add “New SSH Host”:

sqldeveloper-ssh1

In the above screen, specify attribute values as given below:


          Name – Specify Name of your choice. (Tip: Specify name based on Oracle DB Cloud Service Name so that it will be easy to remember.)


          Host – Host must be the Public IP of Oracle Database Cloud Service


          Port – Leave as 22


          User Name – Must be oracle

      • Check “Use Key File” checkbox and browse “Private Key file” generated as part of prerequisite.


Check “Add a local port forward" to set Local Port Forward details as below,


          Name - Specify any Name of your choice


          Host – Must be Public IP of Database Service


          Port – Port specified for your Database Service (e.g., 1521)

      • Check “Automatically assign local port”


Now, right click on this new SSH Host and click “Connect.”

Creating New Database Connection

Go to “Connections” and right click “New Connection”:

sqldeveloper-newcon

You'll see this screen, where connection details will be supplied:

sqldeveloper-newcon1

In the above image, specify properties as given below:

Connection Name – Specify any logical name

User Name – The Administrator User Name specified while setting up Oracle Cloud Database Service, e.g. SYS.

Password – Password specified for the Administrator

Select Connection Type – SSH

Role – SYSDBA

Port Forward – When you select Connection Type = “SSH,” Port Forward will list the “SSH Host Name” you created. Select SSH Host Name set up as part of "SQL Developer SSH Setup."

Specify SID – Generally, ORCL (unless you changed it)

Click the “Test” button. You'll see “Success.”

Click “Save” to save this connection and use it just like another DB connection.

Setting Up HR Schema

Open the SQL worksheet and run the commands below:

hrschema.png

Create a new Database Connection using c##hr user. Follow the same steps as you followed in "Creating New Database Connection," above, except specify Role = Basic.

Set up HR Schema using HR_30.sql, which can be downloaded from the location below. Recompile Triggers if it throws any error.

Download HR Schema from:
http://www.oracle.com/technetwork/developer-tools/datamodeler/sample-models-scripts-224531.html
Extract zip file and run SQL to set up HR Schema.
Scripts to load data can be found in the attachment of this article. Alternatively, you can export data from your local HR Schema and use those scripts.


























Loading data into tables

Disable Trigger SECURE_EMPLOYEES (it validates against normal office hours to insert/update employee data). Run scripts in the following sequence:

regions.sql

countries.sql

locations.sql

jobs.sql


Disable constraint DEPT_MGR_FK from DEPARTMENTS Table before running departments.sql:

departments.sql

employees.sql

job_history.sql

Enable constraint DEPT_MGR_FK from DEPARTMENTS Table again.


Creating HRApp Web Application


Open JDeveloper 12c. Select "New"->"Applications"->"Fusion Web Application", Click OK.


1.png

Specify Application Name "HRApplication" and Application Package Prefix "com.hrapp":


2.png

Continue with default options until you reach the page depicted below and Click "Finish". It will create HRApplication ADF Web Application.

3.png

Create Business Objects using HR Schema. Right Click on "Model" Project and select "New":


Select "Business Components from Table" and click "Next".  It will open "Create Database Connection."

Remember, the connection above is made to a local instance of HR Schema installed on my local machine.























Create a new connection with the name "hrconn," with access credentials as below:

5.png

In the next step, the newly created connection will be automatically selected. Select "OK":

6.png

Hit "Auto Query" to list available entities.


Select Employee and Departments table from available entities and move them to selected pane as given below.


Rename default package name "com.hrapp.model"(As displayed in below image) to "com.hrapp.entities".


Select each table name and rename their Object Name to EmployeesEO and DepartmentsEO respectively and select "Next".

7.png


Move all available entities to selected pane.


Rename default view package to "com.hrapp.views" as shown below.


Select each entity and change their name from,


     "DepartmentsEOView" to DepartmentsVO

      and "EmployeesEOView" to EmployeesVO

     then click "Next".


8.png


Continue with default selection until you reach the "Application Module" page. Rename AppModule Name "HRAppModule" and the package name to "com.hrapp.services" and then Select "Finish".

The Model Project will look like the figure, below, where all entities, View Objects and an App Module will be created. There is a default DataControl also created to access services.


10.png

Create Web Page to display Employee Data and Modify information.


Select "View Controller" Project ->Web Content" -> "New":

11.png

Select "JSF Page" from the dialog and Click "OK."


You'll see the popup below. Specify File Name = "empDetail.jspx", Document Type="JSP XML" and Page Template = "Oracle Three Column Layout." Click "OK."

12.png

It will create empDetail.jspx page.


Select the "End" section from the "Structure" panel and delete it.


Expand "HRAppModuleDataControl" and drag "DepartmentsVO1" to the "Facet Start" section and create as ADF Form.


Remove "Employee ID" from the display list and Select "Navigation Button" check box from the dialog box.


Click "OK."

13.png


Select "Facet:Center" and add "Panel Splitter" to "Facet:Center."


Expand "Panel Splitter -> Start."


Drag "DepartmentVO1"-> "EmployeeVO3" to "Panel Splitter->First" section, and drop as Read-Only Table.


Drag "DepartmentVO1"-> "EmployeeVO3" to "Panel Splitter->Second" section, and drop as ADF Form.


It will look like this:

center.png

Select "Single Row" to allow single row selection in table.

Check "Enable Sorting" to enable Sorting of data based on each columns ascending/descending.

Check "Enable Filtering" to enable filtering records using column filter.

14.png

Surround Departments ADF Form, Employees ADF Form and Employees ADF Read-only Table with ADF Panel Box.


Change Panel Box names to,


"Departments" for Departments ADF Form,

"Update Employees Information" for Employees ADF Form

and "Employees List" for Employees ADF Read-only Table respectively.


Select af:PageTemplate from empDetails.jspx - Structure Panel. In the Property Inspector window, change "Start Column Size" to 350.


Select "Save All."


Create Datasource for Web Application


Log in to JCS.


Open the Service Console, as shown below,


Open the WebLogic Server Console from here.


In the left panel, click "Services," then "Data Sources." Click “Lock & Edit” to enable “New” Button.

jcsdsn

Then click “New->Generic Data Source.”

ds1.1.png

Name = use any logical name

JNDI Name = This could be anything (I have specified “jdbc/hrds” and Oracle Database Cloud Service Name)

Database Type = Oracle

Click “Next.”

dsn2.png

On the next screen, "JDBC Datasource Properties," simply click "Next."

ds2.png

The "Transaction Options" screen will open. Click "Next;"

ds3.png

Enter details as mentioned in below screen,

Database Type = Oracle

Database Driver = As specified above. You can choose these setting based on your DB and driver.

On the next screen, “Simply click “Next”. It will open the screen below:

Database Name = ORCL, unless you changed it while creating database Cloud Service Instance

Host Name = Enter your Database Cloud Service name

Port = 1521

Database User name = c##hr (HR Schema user name)

Password = Welcome#1 (specified for HR Schema)

Click “Next.”

dsn3.png

On the next page, click “Test Configuration.” It should be a success.

If it doesn't show successful then re-verify the credentials supplied for database connection. Also make sure that network connection is there to connect to Database Cloud Service.







dsn4.png

Select Managed Server Name "kumarjav_server_1" and Click “Finish."

dsn5.png

Set Up SSH for Java Cloud Service


Make sure you have set up Oracle Java Cloud Service.

Start Putty by clicking on putty.exe

jcsssh

Specify Host Name – Public IP of Oracle Java Cloud Service Instance

Make sure Connection Type = SSH

Specify Saved Session name = Any name (it's best to use a meaningful name based on IP/Service connected)

Expand “Connections from left panel and select “Data,” as shown below:

jcsssh1

Login details > Auto-login username = <span style="background: tran

Comments

  • 910288
    910288 Member Posts: 1

    Abhishek,

    Good article. However there are a few things I would like to point:

    1. Using DCS for Database Cloud Service is misleading as we have Developer Cloud Service and Documents Cloud Service as well with the same acronym. You can use DBCS for clarity.

    2. You are assuming the reader has not created the DBCS service and guiding through the steps, this is great. But the same assumption should be valid for JCS as well and you should guide through the steps for JCS as well. Directing users to open Service Console directly for JCS needs to have a background of service creation.

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 37,768 Red Diamond

    Some other thing you should explain

    1) why do you install the hr schema into the orcl main database instead of using the pluggable db pdb1?

    2) why do you use an xa driver to connect to the db?

    Adf are not compatible with using xa drivers (http://docs.oracle.com/cd/E24382_01/web.1112/e16182/deployment_topics.htm#ADFFD23083 for an older version) or has this changed for 12.1.3?

    3) why do you use a local xe db sms a cloud db? Why don't you use the cloud db to build the application?

    Timo

  • Christopher Bridge-Oracle
    Christopher Bridge-Oracle Member Posts: 53 Employee
    edited Feb 9, 2016 5:50AM

    Hi Kumar,

    Interesting article.  Although it's not always clear if you're working locally or with the Cloud service(s). I guess you  develop/test the app using the local JDeveloper 12c and the local integrated WebLogic Server, then deploy to the JCS 12c WebLogic Server.

    BTW - You appear to have missed (or your screen shots don't match up and use localhost) the definition of "CloudServer" - which is I believe the JCS-based Application Server which you ultimately demonstrate deploying you HR demo app to. I understand there's a tendency (security) to want to obfuscate the Cloud Service instance IP addresses etc. in screen shots which takes extra time in writing up. In the screen shot I think you initially gave it the name "WLS-160-4-5-30" but later it is referred to as "CloudServer".

    Update:

    These (possible created since this article was written) resources are useful: