Oracle Database 23ai - Application Development in ReactJS, NodeJS, Streamlit, Python, Java, JSON, ORDS and DevOps with GitHub
In this part 4 article of the Oracle Database 23ai series, we will see how to expose the REST API using ORDS. We will also learn how to develop enterprise-grade applications with various programming languages and understand various new and emerging developer technologies. Compare the advantages and purposes where one differentiates from another. We will also learn how to build Oracle Database 23ai applications with React.js, Node.js, Python, Java, and Oracle APEX. We will enhance our skills to do basic DevOps with source code and manage the GitHub repository where multiple development teams can work together.
Assumption: You have already set up an Oracle Database Environment. If not, please refer to my article on Installing Oracle Database 23ai in part 1 of this 4 article series.
Table of contents for this article
01. Register Schema with ORDS in Oracle APEX
Login to Oracle APEX workspace environment and select SQL Workshop > RESTful Services
Click on the Register Schema with ORDS button. For this article, we will need a sample schema, so let's check on Install Sample Schema and Save Schema Attributes.
Expand Modules > oracle.example.hr > employees > /GET and copy the Full URL we will need this url later. Please note that the Full URL will vary depending on the Schema Alias and public IP address
Copy and paste the REST URL into a web browser or any REST client.
At this stage, we are good to move to the next step in our application development. It is also highly recommended that you secure your REST services, which is not covered in this article.
2. React Basics for Development
ReactJS, also known as?React , is a popular JavaScript library for building user interfaces. It is also referred to as a front-end JavaScript library. It was developed by Facebook and is widely used for creating dynamic and interactive web applications. In this article, we’ll explore the key concepts of React.
Step1: Install NPM
npm is the world's largest Software Registry.The registry contains over 800,000 code packages.Open -source developers use npm to share software. Many organizations also use npm to manage private development. npm is free to use.
please refer this guide on NPM installation .
Assumption: You have already installed Visual Studio Code or any other editor. I am using VSCode throughout this article. Open the integrated terminal.
Step 2: create a basic react application. From your VsCode Terminal
mkdir article4
cd article4
npx create-react-app firstapp
This has created a complete folder structure with node_modules, public and src directory with basic code that is required to run a sample application.
open a new terminal and run the following
npm start
-- Output --
Compiled successfully!
You can now view firstapp in the browser.
Local: https://localhost:3000 On Your Network: https://x.y.z.z:3000
Note that the development build is not optimized.
To create a production build, use npm run build.
Understanding react props with a sample application, React Props are like function arguments in JavaScript and attributes in HTML. To send props into a component, use the same syntax as HTML attributes: for example
import React from 'react';
import ReactDOM from 'react-dom/client';
function Car(props) {
return <h2>I am a { props.brand }!</h2>;
}
const myElement = <Car brand="Ford" />;
const root = ReactDOM.createRoot(document.getElementById('root'));
root.render(myElement);
Now, let us create a simple react application that prints name, age and study in a browser. For that, we will create a folder named "components" under the "src" folder and name the javascript file Greet.js
// code under src/components folder Greet.js
import React from "react";
export const Greet = (props) => {
console.log(props)
return (
<div>
Hello, My Name is {props.name} my age is {props.age},
and I study {props.subject}
</div>
)
}
Update our App.js to call Greet.js passing props
import './App.css';
import { Greet } from './components/Greet';
function App() {
return (
<div className="App">
<header className="App-header">
{
<Greet name="John" age="22" subject="computers"/>
}
</header>
</div>
);
}
export default App;
Run the application or view the refreshed application as shown below.
npm start
3. ReactJS application to show an individual employee detail
Let us create an Employee.js file under the src/components folder and make a REST API call to our Oracle Database 23ai Get Employee Detail Service as shown below.
Axios is Promise based HTTP client for the browser and node.js
-- Install axios
cd firstapp
npm install axios
This should update package.json as shown below
under src/components create Employee.js
this essentially has 4 sections explained below
Section 1: Imports
Section 2: A Function with constructors
Section 3: UseEffects to make a REST API Call, please replace with your REST service URL
The useEffect Hook allows you to perform side effects in your components. some examples of side effects are: fetching data, directly updating the DOM and timers. useEffect accepts two arguments. The second argument is optional. useEffect(<function>, <dependency>)
Section 4: Return the employee name, hiredate, job and salary, View Chrome Browser console for any errors or to get the data element in JSON
So, let us now look into output in the browser. Changing the ID will change the Data fetched from the REST service.
04. ReactJS application to show list of all the Employees
This time, let's show all the employees coming from the REST service and add a stylesheet to make the data table look pretty. under the root folder/src where we have our App.js we can also see that we have index.css, edit this file and add some style elements as shown below.
-- Please download this complete source code from my GitHub repo
.custom-table {
width: 100%;
border-collapse: collapse;
}
.custom-header {
background-color: #004085;
color: white;
padding: 10px;
}
.custom-row:nth-child(odd) {
background-color: #f2f2f2;
}
.custom-row:hover {
background-color: #ddd;
}
under components folder create AllEmployee.js as shown below
Section 1: Import useEffects and useState, create constructor and fetch Data from REST Service
Section 2: return the result set from JSON, for this let us examine the JSON from the chrome browser console log
We would now need to make minor changes in our App.js that is shown below
import logo from './logo.svg';
import './App.css';
import AllEmployees from "./components/AllEmployees";
function App() {
return (
<div className="App">
<AllEmployees />
</div>
);
}
export default App;
View the output in the web browser
05. React.js v/s Node.js comparison
Purpose and Usage
Node.js:
React.js:
Architecture
Node.js:
React.js:
Performance
Node.js:
React.js:
Scalability
Node.js:
React.js:
06. Installing Oracle DB instant client for application development
In this section, we will see how to connect and display results from Oracle Database 23ai using Node.js and Oracle Instant client.
Since i am using MacOS I will download the .dmg file, please choose the one related to your operating system
after downloading and extracting run the instant client shell script
% cd /Volumes/instantclient-basic-macos.x64-19.16.0.0.0dbru
% ./install_ic.sh
/Downloads/instantclient_19_16
Creating dir: /Downloads/instantclient_19_16...
instantclient-basic-macos.x64-19.16.0.0.0dbru % cd /Downloads/instantclient_19_16
% pwd
/Users/<username>/Downloads/instantclient_19_16
Install Node.js (please read instructions here )
Check the node version, Install Node.js (14.6 or later versions) by downloading and installing the macOS installer package.
% node -v
v22.2.0
07. Node.js Application Development
Install node-oracledb, for this let us create a new folder firstnodeapp and create a file package.json as shown below
-- shows content of package.json
{
"name": "Demo",
"version": "1.0.0",
"private": true,
"description": "Demo app",
"keywords": [
"myapp"
],
"author": "You",
"license": "MIT",
"dependencies": {
"oracledb": "^6.0"
}
}
npm install
This will create node_modules folder and package-lock.json file
Create index.js (Download from GitHub) , this will have 2 sections
Section 1: Create connection with Database and get result set
Section 2: Iterate over result set, show errors
Run the Node application
node index.js
If you plan to develop web application using Node.js lets move to next section 08
08 Node.js Web Application Deployment
let us now create a new Javascript simplewebapp.js (Download from GitHub)
Section 1: Enable node-oracledb Thick mode
Section 2: Async function will create Oracle Database 23ai connection and Create HTTP server and listen on port httpPort
领英推荐
Section 3: Handle Database Request.
Section 4: Display result set build HTML with response.write
Section 5: Report Errors
Run the code
madhusudhanrao@MadhuMac firstnodeapp % node simplewebapp.js
Running in thin mode
Server is running at https://localhost:8080
09. Python Application Development
Lets, now take python example, Install python-oracledb
madhusudhanrao@MadhuMac firstnodeapp % python3 --version
Python 3.10.4
python3 -m pip install oracledb cryptography --upgrade --user
Create emplist.py (Download from GitHub )
article4 % cd firstpythondbapp
firstpythondbapp % python3 employees.py
('KING', 7839, 5000.0, datetime.datetime(1981, 11, 17, 0, 0))
('BLAKE', 7698, 2850.0, datetime.datetime(1981, 5, 1, 0, 0))
('CLARK', 7782, 2450.0, datetime.datetime(1981, 6, 9, 0, 0))
('JONES', 7566, 2975.0, datetime.datetime(1981, 4, 2, 0, 0))
('SCOTT', 7788, 3000.0, datetime.datetime(1982, 12, 9, 0, 0))
('FORD', 7902, 3000.0, datetime.datetime(1981, 12, 3, 0, 0))
('SMITH', 7369, 800.0, datetime.datetime(1980, 12, 17, 0, 0))
('ALLEN', 7499, 1600.0, datetime.datetime(1981, 2, 20, 0, 0))
('WARD', 7521, 1250.0, datetime.datetime(1981, 2, 22, 0, 0))
('MARTIN', 7654, 1250.0, datetime.datetime(1981, 9, 28, 0, 0))
('TURNER', 7844, 1500.0, datetime.datetime(1981, 9, 8, 0, 0))
('ADAMS', 7876, 1100.0, datetime.datetime(1983, 1, 12, 0, 0))
('JAMES', 7900, 950.0, datetime.datetime(1981, 12, 3, 0, 0))
('MILLER', 7934, 1300.0, datetime.datetime(1982, 1, 23, 0, 0))
10 Java Database Application Development
Download and Install JDK (please follow the instructions shown here )
Check for Java version
% java -version
openjdk version "11.0.23" 2024-04-16
OpenJDK Runtime Environment Homebrew (build 11.0.23+0)
OpenJDK 64-Bit Server VM Homebrew (build 11.0.23+0, mixed mode)
Download Oracle Database 23ai JDBC driver and Companion Jars Downloads
In my case i will download ojdbc11-full.tar.gz and extract the zip file, place all these files in a folder as shown below.
I have created a folder firstjavaapp and under it a folder by name ojdbc11-full folder as shown below
Now our Java files will be placed under firstjavaapp
Set classpath
-- replace <code-path> as per your folder path --
export CLASSPATH=".:/<code-path>/firstjavaapp/ojdbc11-full/ojdbc11.jar:/<code-path>/firstjavaapp/ojdbc11-full/orai18n.jar:/<code-path>/firstjavaapp/ojdbc11-full/xmlparserv2.jar:/<code-path>/firstjavaapp/ojdbc11-full/rsi.jar:/<code-path>/firstjavaapp/ojdbc11-full/simplefan.jar:/<code-path>/firstjavaapp/ojdbc11-full/ucp11.jar:/<code-path>/firstjavaapp/ojdbc11-full/xmlparserv2_sans_jaxp_services.jar:/<code-path>/firstjavaapp/ojdbc11-full/ons.jar"
Create StatementSample.java which will basically lists all employees.
Constructor and main method
StartDemo will basically set username, password connection parameters and create and get Oracle Database 23ai connection
Employee Class inside the main class that will have the database table column objects
Execute statement and print on console
Compile the Java code as as shown below
firstjavaapp % javac StatementSample.java
-- This will create class files as shown below
StatementSample.class
StatementSample$Employee.class
Run the Java code as java <source code name without .java extension>
firstjavaapp % java StatementSample
Output
11. Oracle APEX Application Development (Low-code approach)
Based on Database Table let us quickly create an Application that can do CRUD operation
Run the page and login
Our Application page is now ready
This also creates a form for Create, Read, Update, Delete operations
12. DevOps with GitHub
These are essential concepts of any development. My assumption is that you already have a GitHub account and you have installed a GitHub desktop. Let us take it from there: login to your GitHub account and create a folder as shown
Create a Basic Repository. You can make it public or private.
Make not of SSH or HTTP github cloning options, if you use GitHub Desktop it makes it easier to manage
The GitHub Desktop creates a local folder if it is not already available
Let us now create a file readme.txt under this Repository.
By clicking on the Fetch button, you can view the changes done in GitHub, most probably by your teammates.
You can also use the Pull Origin button to get the latest version available.
Once we have the latest version, we can make changes or view the code differences in case of conflicts (the local version differing from the one on GitHub). Once the conflicts are resolved and new code is developed, the same can be re-uploaded back.
While working locally create the required folders and files in the local repository directory
GitHub Desktop will now show the new files added or changed
Click on Commit to main button and click on Push Origin button, now the new folders will be visible in GitHub
Creating Pull Request:
All the changes now are available only at your repository, in case you can fork from someone else repository, that is, taken their code into your repository then after your work is completed, you can push back the code into their repository by using a "Pull Request" button
Merging the code:
The person who reviews your pull request can review their version of code and the version of code that you are pushing; if they find it does not have any CONFLICTS, then they can Merge your code into their code, so now other developers can also download or FORK your code from the main repository.
This brings a great discipline in code development, version control and quality checking, which is essential for any application development
Auto generating basic database application code:
Login to Oracle APEX or SQL Web Developer and create the tables
CREATE TABLE TASKS (
ID NUMBER GENERATED ALWAYS AS IDENTITY
( START WITH 1 CACHE 20 ) ,
NAME VARCHAR2 (4000) ,
IS_COMPLETED VARCHAR2 (1)
);
CREATE UNIQUE INDEX TASKS_PK ON TASKS ( ID ASC );
ALTER TABLE TASKS ADD CONSTRAINT TASKS_PK PRIMARY KEY ( ID )
USING INDEX TASKS_PK;
Download the source code and create application as shown
% npm create @oracle/database-app
Need to install the following packages:
@oracle/[email protected]
Ok to proceed? (y) y
> npx
> create-database-app
What would you like your application's name to be? (node:5189) [DEP0040] DeprecationWarning: The `punycode` module is deprecated. Please use a userland alternative instead.
? What would you like your application's name to be? mydbapp
? Which template would you like to use for your project? node-react-todo
? Which database connection type would you like to choose? Cloud Wallet Path
In order to unzip and copy the Wallet file, please ensure that the read permissions have been given to the terminal.
Please input your Cloud Wallet Path: /<your-folder>/<your-adb-wallet>/<wallet-filename>.zip
? Please input your wallet password: ********
? What's your database username? <DB-Username>
? What's your database password? *************
Run Application
14. Accessing Oracle Database with Python and Streamlit server
Install python-oracledb
if you are using Oracle Database then just replace username, password and public ip in the below code as shown
#Author Madhusudhan Rao
#Just connects to Oracle Database and shows the resultset in a table
import oracledb
import streamlit as st
import pandas as pd
import numpy as np
un = '<DB-username>'
pw = '<DB-password>'
cs = '<public-ip>/FREEPDB1'
st.title('???? Department Table')
with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
with connection.cursor() as cursor:
sql = """select deptno, dname, loc from dept"""
df = pd.DataFrame(cursor.execute(sql))
df.columns = ['Dept No','Name','Location']
st.dataframe(df)
Conclusion:
Developers and Architects can choose their own programming language, be it traditional development with PHP, Perl, Python, Python with Streamlit web application, Java, .Net, Node.js, or React.js, or use Oracle APEX for creating enterprise-grade applications. Every technology has its own pros and cons, depending upon the business and enterprise architecture requirements.
Thanks for reading, liking and reposting
Regards, Madhusudhan Rao
A great read, Madhusudhan Rao. Looking forward to applying these insights.