2-Day-Java-Developers-Guide 20c
2-Day-Java-Developers-Guide 20c
2-Day-Java-Developers-Guide 20c
20c
F16925-01
February 2020
Oracle Database 2 Day + Java Developer's Guide, 20c
F16925-01
This software and related documentation are provided under a license agreement containing restrictions on
use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your
license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify,
license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means.
Reverse engineering, disassembly, or decompilation of this software, unless required by law for
interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If
you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on
behalf of the U.S. Government, then the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,
any programs embedded, installed or activated on delivered hardware, and modifications of such programs)
and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government end
users are "commercial computer software" or “commercial computer software documentation” pursuant to the
applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use,
reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/or
adaptation of i) Oracle programs (including any operating system, integrated software, any programs
embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle
computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the
license contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloud
services are defined by the applicable contract for such services. No other rights are granted to the U.S.
Government.
This software or hardware is developed for general use in a variety of information management applications.
It is not developed or intended for use in any inherently dangerous applications, including applications that
may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you
shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its
safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this
software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of
their respective owners.
Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are
used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,
and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered
trademark of The Open Group.
This software or hardware and documentation may provide access to or information about content, products,
and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly
disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise
set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be
responsible for any loss, costs, or damages incurred due to your access to or use of third-party content,
products, or services, except as set forth in an applicable agreement between you and Oracle.
Contents
Preface
Audience vi
Related Documents vi
Conventions vi
iii
4.3 Github Repository Details 4-5
4.4 Import the Application in JDeveloper 4-5
4.5 Compile Applications in JDeveloper 4-5
4.6 Compile Using Maven and Run the Application in any Java EE Container 4-5
6 Search by Employee ID
6.1 Employee Java Bean 6-1
6.2 Add the code to a Servlet to process the request 6-2
6.3 Create a New HTML for Search by Employee Id 6-3
8 Best Practices
Index
iv
List of Tables
1-1 Architecture of the Web Application 1-2
1-2 Components Required for the Application 1-3
4-1 Github Repository Details 4-5
v
Preface
Preface
This preface discusses the intended audience and conventions of the Oracle
Database 2 Day + Java Developer's Guide.. It also includes a list of related Oracle
documents that you can refer to for more information.
Audience
This guide is intended for application developers using Java to access and modify data
in Oracle Database. This guide illustrates how to perform these tasks using a simple
Java Database Connectivity (JDBC) application. This guide uses the Oracle
JDeveloper integrated development environment (IDE) to create the application. This
guide can be read by anyone with an interest in Java programming, but it assumes at
least some prior knowledge of the following:
• Java
• Oracle PL/SQL
• Oracle databases
Related Documents
For more information, see the following documents in the Oracle Database
documentation set:
• Oracle Database JDBC Developer’s Guide
• Oracle Database Java Developer’s Guide
• Oracle Universal Connection Pool Developer’s Guide
Conventions
The following text conventions are used in this document:
Convention Meaning
boldface Boldface type indicates graphical user interface elements associated
with an action, or terms defined in text or the glossary.
italic Italic type indicates book titles, emphasis, or placeholder variables for
which you supply particular values.
monospace Monospace type indicates commands within a paragraph, URLs, code
in examples, text that appears on the screen, or text that you enter.
vi
1
Aims and Objectives of This Book
Java is a popular language among developers that is used to build various enterprise
solutions.
This guide will help you understand all Java products used to build a Java application.
You will learn how to model a Java Web application using MVC design pattern, Oracle
JDBC Thin driver, Universal Connection Pool (UCP), and Java in the Database ( using
embedded OJVM).
In the next few chapters, you will create a Java web application — ‘HR Web
application’. This application will help the HR team of AnyCo Corporation to lookup or
modify details of a specific employee, or all employees, delete an employee, or apply a
salary raise to all employees.
The application will have two users
hrstaff
hradmin
Each user will have a different set of roles and privileges.
This Chapter contains the following topics:
Architecture of the Web Application
Components of the Application
Tasks for Day 1
Tasks for Day 2
1-1
Chapter 1
Components and Repositories
View to the user during logging in, or any other flow. When you request for data or an
update to the data, the Controller invokes the Model that represents the data in terms
of tables or views, and renders the data. The Model represents the user data usually
stored in an Oracle Database or any other database.
The Controller then passes on this data to the View to show it to the user in a
presentable format.
Figure 1-1
1-2
Chapter 1
Objectives and Tasks for Day 1
1-3
Chapter 1
Objectives and Tasks for Day 2
2 Overview of the HR Web Application: This chapter will discuss the HR Web
application in depth and familiarize you with the flows of the Web application,
packages and files that you will create as a part of the Application.
3 Getting Started with the Application: You will understand the pre-requisites for
building the application and how to get the environment ready. It starts with
subscribing to the Oracle Database Service in the cloud or installing the Oracle
Database 12c Release 2 on premises. Later, you will install JDeveloper, an IDE to
build the application. You will use either web Logic Server that is integrated in the
JDeveloper or Tomcat Java EE container to deploy and run the application.
The chapter will also help you download any other tools, such as Maven, that will help
you build the application.
4 List All Employees: This chapter will help you how to put all components together
and build an initial functionality to connect to the Oracle Database, and retrieve
employee details from the database.
1 Search By Employee ID: This chapter provides details on how to implement the
‘Search by Employee ID’ functionality.
2 Update an Employee Record: In this chapter, you will learn how to update employee
records. This is two step process. You will first search the employee’s records, based
on first name. Once you retrieve the required results, you can update the salary, job
ID, firstname, lastname and other details.
3 Delete an Employee Record: In this Chapter, you will learn how to delete an
employee record, in a two-step process.
4 Increase Salary to All Employees: You will understand how to provide increment
to the salary of the employees listed in the table, using ‘Java in the database’.
5 Creating Application Users: This chapter shows how to create ‘hradmin’ and
‘hrstaff’ users in Tomcat and JDeveloper.
6 Summary: This chapter will summarize all that you have learnt over the two days. It
will also provide appropriate references and links for enhancing your use of the
Application.
1-4
2
Brief Introduction to JDBC, UCP, and Java
in the Database
Oracle Database is a relational database that you can use to store, modify and use
data.
The Java Database Connectivity (JDBC) standard is used by Java applications to
access and manipulate data in relational databases.
JDBC is an industry-standard application programming interface (API) that lets you
access a RDBMS using SQL from Java. JDBC complies with the Entry Level of the
JDBC escape standard. Each vendor implements the JDBC Specification with its own
extensions.
Universal Connection Pool (UCP) is a connection pool used to cache the database
connection objects to reuse the connections, thus improving the performance.
Java in the Database (OJVM) helps group SQL operations with Java data logic and
load them into the database for in-place processing.
This chapter introduces you to the JDBC driver, Universal Connection Pool (UCP) and
Java in the Database (OJVM) with Oracle Database 20c Release.
• Java Database Connectivity Driver (JDBC)
• Universal Connection Pool (UCP)
• Java in the Database (Oracle JVM)
The following sections describe Oracle support for the JDBC standard:
• Oracle JDBC Thin Driver
• Oracle JDBC Packages
2-1
Chapter 2
Java Database Connectivity Driver (JDBC)
JDBC Thin Driver communicates with the server using SQL*Net to access the
database.
See Also:
Oracle Database JDBC Developer’s Guide
Action Item 1: Change the DB_URL to point to your database. If you need help, refer
to the DataSourceSample.java on Github.
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DatabaseMetaData;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.OracleConnection;
2-2
Chapter 2
Universal Connection Pool
See Also:
Oracle Universal Connection Pool Developer’s Guide
Import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
/*
* The sample demonstrates UCP as client side connection pool.
*/
public static void main(String args[]) throws Exception {
// Get the PoolDataSource for UCP
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
2-3
Chapter 2
Universal Connection Pool
pds.setInitialPoolSize(5);
2-4
Chapter 2
Java in the Database (OJVM)
System.out.println("---------------------");
while (resultSet.next())
System.out.println(resultSet.getString(1) + " "
+ resultSet.getString(2) + " ");
}
}
}
}
Rem NAME
Rem ServersideConnect.sql
Rem
Rem DESCRIPTION
Rem SQL for invoking the method which gets a server side connection to
rem Reads the content of the Java source from ServersideConnect.java
rem then compiles it
connect username/pwd
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED ServersideConnect_src AS
@ ServersideConnect.java
/
show error
rem A wrapper (a.k.a. Call Spec), to invoke Java
rem function in the database from SQL, PL/SQL, and client applications
CREATE OR REPLACE PROCEDURE ServersideConnect_proc AS
LANGUAGE JAVA NAME 'ServersideConnect.jrun ()';
/
rem running the sample
connect username/pwd
SET SERVEROUTPUT ON SIZE 10000
CALL dbms_java.set_output (10000);
execute ServersideConnect_proc;
InternalT2Server.java
import java.sql.Connection;
2-5
Chapter 2
Java in the Database (OJVM)
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.pool.OracleDataSource;
/*
* Displays employee_id and first_name from the employees table.
*/
static public void printEmployees(Connection connection)
throws SQLException {
ResultSet resultSet = null;
Statement statement = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT employee_id, first_name"
+ " FROM employees order by employee_id");
2-6
Chapter 2
Java in the Database (OJVM)
while (resultSet.next()) {
System.out.println("Emp no: " + resultSet.getInt(1) + " Emp name: "
+ resultSet.getString(2));
}
}
catch (SQLException ea) {
System.out.println("Error during execution: " + ea);
ea.printStackTrace();
}
finally {
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
}
}
}
2-7
3
Overview of the HR Web Application
The HR Web Application is intended to give you access to information related to all
employees of AnyCo Corporation.
The two types of users that will be able to access this application are:
• HRStaff
• HRAdmin
The HRStaff and HRAdmin accounts have different privileges.
HRStaff has read only access to the application and does not have privileges to
update/delete an employee record. HRStaff can only List the employees and Search
by Employee ID.
The HRAdmin, has complete control on the application and has read and write
privileges. HRAdmin is the only user who has access to all functionalities of the
application such as update/delete an employee record, or provide salary increment for
all employees.
This Chapter has the following sections:
• Functionalities of he HR Web Application
• Packages
Through the hradmin user, you can perform the following functions:
The hradmin user has full control of the application and has both read and update
privileges.
3-1
Chapter 3
Functionalities of the HR Web Application
3-2
4
Getting Started with the Application
To develop a Java application that connects to Oracle Database 12c Release 2 (12.2),
you must ensure that certain components are installed as required. This chapter
covers the following topics:
• What You Need to Install
• Verifying the Oracle Database 12c Release 2 (12.2) Installation
• Installing Oracle JDeveloper or any Java IDE (Eclipse, NetBeans, Intellij)
4-1
Chapter 4
What You Need to Install
Note:
Using Java Applications and IDEs with Oracle Database Cloud
Service(DBCS) OTN page provides instructions to create a database
instance and also try to connect to it using JDBC and UCP code samples
provided in the beginning of the guide
4-2
Chapter 4
What You Need to Install
For more information, refer to the following Oracle Database 12c Release 2 (12.2)
installation guides and release notes:
• Oracle Database Installation Guide for Linux
• Oracle Database Installation Guide for Microsoft Windows
If the database is locally installed, use the Run SQL Command Line to unlock the
account as follows:
1. To access the Run SQL Command Line, from the Start menu, select Programs
(or All Programs), then Oracle Database 12c Release 2 (12.2), and then click
Run SQL Command Line. Log in as a user with DBA privileges, for example:
> CONNECT SYS AS SYSDBA;
Enter password: password
2. Run the following command:
> ALTER USER HR ACCOUNT UNLOCK;
or,
> ALTER USER HR IDENTIFIED BY HR;
3. Test the connection as follows:
> CONNECT HR
Enter password: password
You should see a message indicating that you have connected to the database.
Note:
For information about creating and using secure passwords with Oracle
Database 12c Release 2 (12.2), refer to Oracle Database Security Guide.
In addition, some of the constraints and triggers present in the HR schema are not in
line with the scope of the Java application created in this guide. You must remove
these constraints and triggers as follows using the following SQL statements:
DROP TRIGGER HR.UPDATE_JOB_HISTORY;
DROP TRIGGER HR.SECURE_EMPLOYEES;
DELETE FROM JOB_HISTORY;
4.1.2 JDK 8
You will need JDK8 — a Java Development Kit to create and compile Java
applications.
4-3
Chapter 4
What You Need to Install
Note:
Oracle Database 12c Release 2 (12.2) JDBC driver supports JDK8. Refer
http://www.oracle.com/technetwork/java/javase/downloads/index.html for
information about installing Java.
See Also:
http://www.oracle.com/technetwork/java/javase/downloads/index.html
4-4
Chapter 4
Verifying the Oracle Database 12c Release 2 (12.2) Installation
4-5
Chapter 4
Compile Using Maven and Run the Application in any Java EE Container
HR web application can also be easily compiled using the Maven commands. If you
are using Oracle maven repository, then make sure to have the settings.xml file with
all the required details available for easy access. After you download the
HRWebApp_workspace and add the required code, use the commands below to
clean, compile and package the source code.
Execute the following commands:
mvn –s settings.xml clean
4-6
5
List All Employees
HR web Application has several functionalities. “List All” is the functionality where the
details of employees such as Employee_id, First_name, Last_Name, Email,
Phone_number, Job_id, Salary etc., are retrieved from the “Employees” table and
shown on a web page. See the screenshot below that shows “List All” functionality.
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
3. Declare an Employee class. Add an open parenthesis ({) and closing parenthesis
(}). Place the cursor in between the parenthesis:
public class Employee {
4. Declare the following variables for each one of the attributes of an employee.
5-1
Chapter 5
Creating a Java Bean Entity for an Employee
5. Create a constructor for Employee that takes ResultSet as the input and throws
SQLExceptio. In this constructor, set all the values for the attributes of the Employee.
2. Create the getter and setter methods for the First_Name of an employee
3. Create the getter and setter methods for the Last_Name of an employee
5. Create the getter and setter methods for Phone Number of an employee
public String getPhone_Number() {
5-2
Chapter 5
Creating a Java Bean Interface for a JDBC Connection
return Phone_Number; }
this.Phone_Number = Phone_Number;
}
6. Create the getter and setter methods for JobId of an employee
public String getJob_Id() { return Job_Id;}public void setJob_Id(String
Job_Id) { this.Job_Id = Job_Id;}
3. Declare an interface EmployeeBean class. Add an open parenthesis ({) and closing
parenthesis (}). Place the cursor in between the parenthesis
public interface EmployeeBean {
5-3
Chapter 5
Creating a Java Bean Implementation for a JDBC Connection
3. Import other dependent classes as shown below. If the particular class is not
imported, JDeveloper will display a message reminding you to import the required
package. Press the Alt+Enter keys to import the class:
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.PreparedStatement;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.OracleTypes;
import java.sql.PreparedStatement;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.OracleConnection;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
7. Get a connection by passing the database URL and database username and
password.
5-4
Chapter 5
Creating a Java Bean Implementation for a JDBC Connection
3. Start a try block. The source code is compiled with JDK8 and we use auto-closeable
statements which means that there is no need to explicitly specify catch and finally
blocks. The first try block is for getting a database connection by invoking the method
getConnection(). Declare a variable connection to establish a database connection.
5. Start another try block for ResultSet. Include the query that needs to be executed.
Make sure to retrieve all the required fields of the employee in the query.
try (ResultSet resultSet = statement.executeQuery("SELECT Employee_Id,
First_Name, Last_Name, Email, Phone_Number, Job_Id, Salary FROM
EMPLOYEES")) {
6. Start a while loop to capture the list of employees retrieved from the ResultSet.
while(resultSet.next()) {
returnValue.add(new Employee(resultSet));
}
7. Ensure you close the parenthesis for all the try blocks.
8. Catch the SQLException and log the message in logger as shown below.
return returnValue;
5-5
Chapter 5
Creating a Servlet to Process the Request
Steps to be Performed:
6. Create a servlet WebController.java and reportError() method
7. Create a method processRequest() – This method processes both GET and POST
HTTP requests.
8. Create a method doGet() – Add details to get the employee details from the
database and show the results in JSON. JSON will be the output format of the results
that is shown on the HTML.
9. Create a method getServletInfo() – To display some generic information about the
servlet.
10. Create a logger to log exceptions and other error messages.
Step 6: Instructions for creating a WebController.java and reportError() method
1. Declare the package for the WebController.java.
package com.oracle.jdbc.samples.web;
2. Import Employee class as it contains the employee details and also, the
EmployeeBeanImpl.
import com.oracle.jdbc.samples.entity.Employee;
import com.oracle.jdbc.samples.bean.EmployeeBean;
import com.oracle.jdbc.samples.bean.EmployeeBeanImpl;
3. Import the GSON (Google GSON) for displaying the Employee results.
import com.google.gson.Gson; import com.google.gson.reflect.TypeToken;
4. Import other dependent classes as shown below. If the particular class is not
imported, then JDeveloper will display a message reminding you to import the required
package. Press the Alt+Enter keys to import it.
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.BufferedReader;
import java.util.ArrayList;
5-6
Chapter 5
Creating a Servlet to Process the Request
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;
6. Add the following class declaration WebController that extends HttpServlet. Add an
open parenthesis ({) and closing parenthesis (}). Place the cursor in between the
parenthesis.
8. Declare an object “gson” of the type Gson. This will be a global variable and
available for all the methods such as reportError(), processRequest(), and doGet()
to use.
Gson gson = new Gson();
9. Declare a method reportError as shown below. This is to capture the error and show
it on the page.
private void reportError(HttpServletResponse response, String message)
throws ServletException, IOException {
10. Set the response content type to be “text/html” and charset=UTF-8 as shown
below.
response.setContentType("text/html;charset=UTF-8");
11. Create a PrintWriter object and print the error message as shown.
try (PrintWriter out = response.getWriter()) {
5-7
Chapter 5
Creating a Servlet to Process the Request
1. Add the following method declaration for processRequest(req, res). Add open and
close parentheses ({, }) and position the cursor in between the parenthesis.
protected void processRequest(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
2. Declare a variable employeeList of the List type that contains Employee objects.
Declare the variable gson to process Gson object.
List<Employee> employeeList = null;
gson.toJson(employeeList,
new TypeToken<ArrayList<Employee>>() {}.getType(),
response.getWriter());
else {
response.setStatus(HttpServletResponse.SC_NOT_FOUND);
}
1. Add the following method declaration for doGet().Add open and close parentheses
({, }) and position the cursor in between the parenthesis.
processRequest(request, response);
1. Add the following method declaration for getServletInfo().Add open and close
parentheses ({, }) and position the cursor in between the parenthesis.
public String getServletInfo() {
5-8
Chapter 5
Create an HTML Page to Display Results
Step 11: Instructions to create title, stylesheet, and body of the HTML Page:
1. Create the title, stylesheet, and body for the HTML page.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>List all Employees</title>
5-9
Chapter 5
Create a CSS File
2. Define the action when the requests are sent i.e., when the links for each one of the
functionalities is selected.
xmlhttp. {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
processResponse(xmlhttp.responseText);
}
}
xmlhttp.open("GET", url, true);
xmlhttp.send();
function processResponse(response) {
// Process the JSON response into an array.
var arr = JSON.parse(response);
var i;
var out - "<table>";
keys = Object.keys(arr[0]);
//Print headers
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<th>"+keys [i]+"</th>"
}
out += "</tr>";
// Print values
for(j = 0; j < arr.length; j++) {
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<td>"+arr[j][keys[i]]+"</td>"
}
out += "</tr>"
}
out += "</table>";
document.getElementById("id-emp").innerHTML = out;
5-10
Chapter 5
Create a CSS File
Description: The stylesheet has the color, font, and style for all the UI elements such
as buttons, side navigation, main page, links, etc., on the page.
Steps to be Performed:
Step 14: Download the app.css and use it in your application.
5-11
6
Search by Employee ID
“Search by Employee Id” is the functionality where we can search for a particular
employee based on their employee Id which is the primary key. The user needs to
input the employee Id and submit the request.
1. Declare the method getEmployee(int) that returns a List of the objects of type
Employee.
public List<Employee> getEmployee(int empId) {
3. The first try block is for the getting a database connection by invoking the method
getConnection(). Declare a variable connection to establish a database connection.
6-1
Chapter 6
Add the code to a Servlet to process the request
5. Set the input parameter which is the employee id for the query.
preparedStatement.setInt(1, empId);
if(resultSet.next()) {
returnValue.add(new Employee(resultSet));
} else {
throw new SQLException("No recrods found");
}
8. Make sure to close the parenthesis for all the try blocks. There are a total of 3 try
blocks which need to be closed.
9. Catch the SQLException and log the message in logger as shown below.
6-2
Chapter 6
Create a New HTML for Search by Employee Id
1. Declare a varianle ID_KEY to capture the employee id. This is a global variable,
hence, needs to be declared outside the method processRequest() but within the
WebController class.
4. In addition to what was added for “ListAll” feature, we will add an if condition to
handle the new functionality. Get the employee id entered by the user and invoke the
method getEmployee(int) to verify if the employee record exists.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>List Employee by Id</title>
<!-- Specify the stylesheet here -->
<link rel="stylesheet" type="text/css" href="css/app.css" >
<!-- Bootstrap JS for the UI -->
6-3
Chapter 6
Create a New HTML for Search by Employee Id
<link rel="stylesheet"
href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/
bootstrap.min.css">
</head>
<body>
<div><label>Employee Id: </label>
<input id="empId" type="textfield"
waitForEnter(event)"\>
</div>
<br/>
<br/>
<script>
function waitForEnter(e) {
if (e.keyCode == 13) {
var tb = document.getElementById("empId");
fetchElementById(tb.value)
return false;
}
}
<script>
var xmlhttp = new XMLHttpRequest();
var url = "WebController";
3. Define the action when the requests are sent i.e., when the links for each one of the
functionalities is selected.
xmlhttp. {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
processResponse(xmlhttp.responseText);
}
}
xmlhttp.open("GET", url, true);
xmlhttp.send();
function processResponse(response) {
//Process the JSON respnse into an array.
var arr = JSON.parse(response);
var i;
var out = "<table>";
keys = Object.keys(arr[0]);
// Print Headers
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<th>"+keys[i]+"</th>"
6-4
Chapter 6
Create a New HTML for Search by Employee Id
}
out += "</tr>";
// Print values
for(j = 0; j < arr.length; j++) {
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<td>"+arr[j][keys[i]]+"</td>"
}
out += "</tr>"
}
out += "</table>";
document.getElementById("id-emp").innerHTML = out;
}
6-5
7
Update an Employee Record
The hradmin has the privilege to update an employee record. The hrstaff user does
not have this privilege.
First, you must search for an employee in the records. Once you retrieve the
information related to the employee, you will find the ‘Edit’ and ‘Delete’options to
modify details related to the employee.
This Chapter shows you the classes that need to be created and code that needs to
be added to build ‘Search by Employee ID’ functionality.
In this Chapter, you will learn how to:
1. Declare a new new method getEmployeeByFn(String) in EmployeeBean.java
2. Declare a new method updateEmployee(int) in EmployeeBean.java
3. Implement a new method getEmployeeByFn(String) in EmployeeBeanImpl.java
4. Implement a new method updateEmployee(int) in EmployeeBeanImpl.java
5. Add the code to a WebController.java to process the request and response
6. Create a HTML page (listByName.html) to display the results
Class Name:
src/main/java/com/oracle/jdbc/samples/entity/Employee.java
Use the Employee.java file that you created, earlier in the example.
7-1
Chapter 7
Declare a new method updateEmployee(Employee)
1. Use the class EmployeeBean that you created on Day 1 of the exercise. You can
add new methods for each of the functionalities.
2. Declare a method getEmployeeByFn(String) that takes first name as a parameter.
1. Use the EmployeeBean file that you created in the exercise from Day 1 for ‘ListAll’
functionality. You can use the same class and add new methods for the new
functionalities.
2. Declare a method updateEmployee(Employee) that takes Employee object as a
parameter.
public String updateEmployee(Employee employee) throws SQLException;
3. Create a try block. The try block will help you create a database connection by
invoking the getConnection method.
7-2
Chapter 7
Implement a new method updateEmployee(Employee)
4. Create another try block to create a PreparedStatement. Add the query that will be
executed to select an employee based on the employee id.
5. Set an input parameter — first name of the employee. The input parameter of the
method is set as the IN parameter for the preparedStatement.
preparedStatement.setString(1, fn + '%');
6. Create another try block for ResultSet. Include the query that needs to be
executed.
try (ResultSet resultSet = preparedStatement.executeQuery()) {
7. Include while statement to loop through the results. If any record is found, add it to
the returnValue.
while(resultSet.next()) {
returnValue.add(new Employee(resultSet));
}
return returnValue;
7-3
Chapter 7
Implement a new method updateEmployee(Employee)
2. Declare and initialize a variable for capturing the number of records updated.
int updateCount = 0;
4. Create another try block to make a PreparedStatement. Include the query you will
need to execute to select an employee based on the employee id.
5. Set the new values entered by the user for each attribute and execute the
prepapredStatement.
preparedStatement.setString(1, employee.getFirst_Name());
preparedStatement.setString(2, employee.getLast_Name());
preparedStatement.setString(3, employee.getEmail());
preparedStatement.setString(4, employee.getPhone_Number());
preparedStatement.setInt(5, employee.getSalary());
preparedStatement.setInt(6, employee.getEmployee_Id());
updateCount = preparedStatement.executeUpdate();
7. Catch the SQLException and log the message in the logger as shown below:
8. Log the message with the number of records updated to the logger.
logger.fine("Update count: " +updateCount);
if (updateCount != 1) {
logger.severe("Unable to update record");
throw new SQLException("Alert! Record could not be updated");
}
7-4
Chapter 7
Add the Code to a Servlet (WebController.java)
if ("incrementSalary".equals(value)) {
if ((value = request.getParameter(INCREMENT_PCT)) != null) {
try {
System.out.println("increment% = " +value);
response.setContentType("application/json");
List<Employee> employeeList =
employeeBean.incrementSalary(Integer.valueOf(value));
System.out.println("incrementSalary, employeeList: "
+employeeList.toString());
gson.toJson(employeeList,
new TypeToken<ArrayList<Employee>>(){}.getType(),
response.getWriter());
}catch (Exception ea)
{ response.setStatus(HttpServletResponse.SC_NOT_MODIFIED);
}
} else {
response.setStatus(HttpServletResponse.SC_NOT_MODIFIED);
}
}
7-5
Chapter 7
Create a new HTML(incrementSalary.html)
Step 7: Instructions to create the title, stylesheet, and body of the HTML page:
1. Create the title, stylesheet, and body of the HTML page.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Increment Salary</title>
<link rel="stylesheet" type="text/css" href="css/app.css" >
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/
3.3.6/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.2/
jquery.min.js">script src="https://ajax.googleapis.com/ajax/libs/jquery/
1.12.2/jquery.min.js"></script>
</head>
<body>
<div> Enter the percentage increase in salary<inputid='incrementField'
type="number" max="100" min="3">%
</div>
<div id="UpdateButton"> <button type="button" class="btn btn-info btn-lg"
Increment Salaries</button> <button
type="button" class="btn btn-default btn-lg"
><div id="status" class="none"></div>
<div id="id-emp"></div>
<script>
function showStatus(c, message) {
$('#status').text(message);
$('#status').attr('class', c);
}
function confirmUpdate() {
var increment = $('#incrementField').val();
var res = confirm("Do you really want to Increment Salary by "
+increment +"%?");
if(res == true) {
console.log("Salary record");
$('#UpdateButton').hide();
showStatus("alert alert-info", "Updating records, processing request");
var xmlhttp = new XMLHttpRequest();
var url = "WebController?op=incrementSalary&incrementPct=" +increment;
xmlhttp. {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
processResponse(xmlhttp.responseText);
showStatus("alert alert-success", "Updating records, successfully
updated");
7-6
Chapter 7
Creating Login Users in Tomcat
}
else {
showStatus("alert alert-danger", "Updating records, failure, could
not update records");
}
}
xmlhttp.open("POST", url, true);
xmlhttp.send();
showStatus("alert alert-info", "Updating records, request sent");
}
else {
console.log("Salary not updated");
showStatus("alert alert-warning", "Updating records, attempt
cancelled");
}
}
</script>
1. Create the function processRequest() to display the JSON results on HTML page.
unction processResponse(response) {
var arr = JSON.parse(response);
var i;
var out = "<table>";
keys = Object.keys(arr[0]);
// Print headers
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<th>"+keys[i]+"</th>"
}
out += "</tr>";
// Print values
for(j = 0; j < arr.length; j++) {
out += "<tr>"
for(i = 0; i < keys.length; ++i) {
out += "<td>"+arr[j][keys[i]]+"</td>"
}
out += "</tr>"
}
out += "</table>";
document.getElementById("id-emp").innerHTML = out;
}
7-7
Chapter 7
Creating Login Users in Tomcat
After you login on the home scree using your credentials, you will be able to see the
landing page, with details of the web application. The hradmin and hrstaff have
different privileges and access to different features.
This Chapter will show you the required classes that you need to create and how to
build the ‘Login’ functionality in Tomcat.
• Create a XML file (tomcat-users.xml) for login functionality
• Create a HTML page (login.html) to login the user
• Create a HTML page (login-failed.html) to display the error message
• Create a web.xml to authenticate the users during login
• Create a HTML page (about.html) to show more details about the application
• Create a landing page (index.html) and define the html pages for redirection
• Add code to the servlet (WebController.java) to process logout
7-8
Chapter 7
Creating Login Users in Tomcat
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Login to Jdbc Web Sample application</title>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/
3.3.6/css/bootstrap.min.css">
<style>
#cent {
position:absolute;
top:50%;
left:50%;
margin-top:-50px; /* this is half the height of your div*/
margin-left:-100px; /*this is half of width of your div*/
}
td {
height: 30px;
}
</style>
</head>
<body>
<div id="cent">
<form method="POST" action="j_security_check">
<table>
<tr>
<td colspan="2">Login to the Jdbc Web Sample application:</td>
</tr>
<td>Name:</td>
<td><input type="text" name="j_username" /></td>
</tr>
<tr>
<td>Password:</td>
<td><input type="password" name="j_password"/></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="Go" /></td>
</tr>
</table>
</form>
</div>
</body>
7-9
Chapter 7
Creating Login Users in Tomcat
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Login Failed</title>
</head>
<body>
<p>
Sorry, login failed!
</p>
</body>
</html>
7-10
Chapter 7
Creating Login Users in Tomcat
authentication</web-resource-name>
<url-pattern>/*</url-pattern>
<http-method>GET</http-method>
<http-method>POST</http-method>
</web-resource-collection>
<auth-constraint>
<role-name>manager</role-name>
</auth-constraint>
<user-data-constraint>
<transport-guarantee>NONE</transport-guarantee>
</user-data-constraint>
</security-constraint>
<security-constraint>
<web-resource-collection>
<web-resource-name>Wildcard means whole app requires
authentication</web-resource-name>
<url-pattern>/*</url-pattern>
<http-method>GET</http-method>
</web-resource-collection>
<auth-constraint>
<role-name>staff</role-name>
</auth-constraint>
<user-data-constraint>
<transport-guarantee>NONE</transport-guarantee>
</user-data-constraint>
</security-constraint>
<login-config>
<auth-method>FORM</auth-method>
<form-login-config>
<form-login-page>/login.html</form-login-page>
<form-error-page>/login-failed.html</form-error-page>
</form-login-config>
</login-config>
</web-app>
Create a landing page (index.html) and define the pages for redirection
Class Name: src/main/webapp/index.html
7-11
Chapter 7
Creating Login Users in Tomcat
Step 8: Create the <body> to invoke the html pages for redirecting the requests
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Employee table listing</title>
<link rel="stylesheet" type="text/css" href="css/app.css" >
<style>
iframe:focus {
outline: none;
}
iframe[seamless] {
display: block;
}
</style>
</head>
<body>
1. Create <body> and actions for the features through navigation links and logout.
<body>
<div id="sideNav" class="sidenav">
7-12
Chapter 7
Creating Login Users in Tomcat
</div>
<div>
<span style="font-size:30px;cursor:pointer" Java 2
Days HR Web Application </span>
</div>
<div>
<iframe id="content"
src="about.html"
frameborder="0"
style="overflow:hidden; height:100%; width:100%"
height="100%"
width="100%"></iframe>
</div>
</div>
<script>
function openNav() {
document.getElementById("sideNav").style.width = "256px";
document.getElementById("main").style.marginLeft = "256px";
}
function closeNav() {
document.getElementById("sideNav").style.width = "0";
document.getElementById("main").style.marginLeft= "0";
}
function switchSrc(src) {
document.getElementById('content').src = src;
}
function logout() {
xmllogout. {
window.location.replace("index.html");
}
return true;
}
xmlhttp. {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
role = xmlhttp.responseText;
console.log("role: " +role);
if (role == "staff") {
console.log ("disabling manager");
var x = document.getElementsByClassName('manager');
7-13
Chapter 7
Creating Login Users in Tomcat
1. You have created a method in the “ListAll” feature in the earlier steps. In this step,
you will add code for the logout functionality.
2. Create an if block to verify the functionality you will invoke based on input. Check if
the input valuie is ‘LOGOUT’. Then, invoke the relevant method to log out the user.
}
}
7-14
Chapter 7
Creating Login Users in Tomcat
7-15
8
Best Practices
1. Use Database Service on Cloud:
Use the Oracle Database Service on Cloud (DBCS) to create a database on cloud.
DBCS comes with an in-built HR schema and tables that you can use when you build
the HR web application.
Note:
Use Oracle Database Enterprise Edition 12.2.0.1 to use features and
functionalities of the latest Oracle Database.
Note:
Download the latest JDBC drivers and UCP from 12.2.0.1 JDBC driver and
UCP
3. JDK 8
It is recommended that you use the latest version of Oracle JDBC driver 12.2.0.1, that
is compliant with JDK 8.
4. Auto-closeable statements
Starting JDK7, ‘Auto-closeable statements’ has been introduced, that close by default
without an explicit catch statement.
8-1
9
Troubleshooting and Debugging
1. Tomcat log file:
Check TOMCAT_HOME/logs/catalina.out for any errors after deploying the application.
2. Additional Logging:
Enable logging in Tomcat to find logging messages in the code.
Note:
Refer https://tomcat.apache.org/tomcat-8.0-doc/logging.html for more
information
9-1
Index
E JSP, 4-4
JSP pages
Entry Level of the SQL-92, 2-1 deploying, 4-4
H O
HR account Oracle Database 12c Release 2, 4-2
testing, 4-3 installation, 4-5
HR user account installation guides, 4-3
sample application, 4-3 release notes, 4-3
unlocking, 4-3 verifying, 4-5
verifying installation, 4-5
I Oracle Database 12c Release 2 installation
platform-specific, 4-5
IDE, 4-4 Oracle WebLogic Server, 4-4
Oracle JDeveloper, 4-4
installation S
verifying on the database, 4-5
integrated development environment, 4-4 sample application
HR user account, 4-3
J
J2SE, 4-4
W
installing, 4-4 Web server, 4-4
Java Database Connectivity, 2-1 Apache Tomcat, 4-4
JavaServer Pages, 4-4 servlet container, 4-4
JDBC, 2-1
Index-1