import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.PreparedStatement;

import oracle.jdbc.OracleStatement;

import oracle.jdbc.OracleConnection;

import oracle.jdbc.driver.OracleDriver;

import oracle.jdbc.pool.OracleDataSource;

public class SalaryHikeSP {

/*

public static ResultSet incrementSalary (int percentIncrease) throws SQLException {

int updateCount = 0;

int totalemployees = 0;

int tier3emp = 0;

int tier2emp = 0;

int tier1emp = 0;

float totalsalary = 0.0f;

float minsalary = 0.0f;

float maxsalary = 0.0f;

float totalbudget = 0.0f;

float tier3hike = 0.0f;

float tier2hike = 0.0f;

float tier1hike = 0.0f;

// Percentage to divide the salary hike budget into

// three tiers based on the salary range

// Tier 1 salary range is 15,001 to 25,000

float tier1percent = 0.10f;

// Tier 2 salary range is 7,001 to 15,000

float tier2percent = 0.30f;

// Tier 3 salary range is 0 to 7,000

float tier3percent = 0.60f;

Connection connection = null;

ResultSet resultSet = null;

Statement statement = null;

PreparedStatement preparedStatement;

ResultSet rset = null;

System.out.println("==== Here ===== ");

String TOTAL_EMP = "SELECT count(EMPLOYEE_ID) from EMPLOYEES";

String TOTAL_SALARY = "SELECT sum(salary) from EMPLOYEES";

String MIN_SALARY = "SELECT min(salary) from EMPLOYEES";

String MAX_SALARY = "SELECT max(salary) from EMPLOYEES";

String UPDATE_SQL =

"UPDATE EMPLOYEES SET SALARY = 20000 WHERE EMPLOYEE_ID=100";

String TIER3_EMP = "SELECT COUNT(EMPLOYEE_ID) from EMPLOYEES WHERE " +

"salary >= ? and salary < 7000.00";

String TIER2_EMP = "SELECT count(EMPLOYEE_ID) from EMPLOYEES " +

" WHERE SALARY > 7001.00 and SALARY < 15000.00";

String TIER1_EMP ="SELECT count(EMPLOYEE_ID) from EMPLOYEES " +

" WHERE SALARY >15001.00 AND SALARY < ?";

String TIER3_UPDATE ="UPDATE EMPLOYEES SET SALARY = (SALARY + ?)" +

" WHERE salary > ? " +

" and salary < 7000.00";

String TIER2_UPDATE = "UPDATE EMPLOYEES SET SALARY = (SALARY + ? )" +

" WHERE SALARY > 7001.00 and SALARY < 15000.00 ";

String TIER1_UPDATE = "UPDATE EMPLOYEES SET SALARY = (SALARY + ?)" +

" WHERE SALARY > 15001.00 and SALARY < ? ";

if (percentIncrease <= 0) {

throw new

IllegalArgumentException("Invalid percentage provided: " +percentIncrease);

}

try {

connection = new OracleDriver().defaultConnection();

// Get the total number of employees

statement = connection.createStatement();

resultSet = statement.executeQuery(TOTAL_EMP);

while (resultSet.next()) {

totalemployees = resultSet.getInt(1);

System.out.println("Number of employees" + totalemployees);

}

// Get the total salary of all employees

resultSet = statement.executeQuery(TOTAL_SALARY);

while (resultSet.next()) {

totalsalary = resultSet.getFloat(1);

System.out.println("Total salary of all employees: " + totalsalary);

}

// Get the minimum salary of all employees

resultSet = statement.executeQuery(MIN_SALARY);

while (resultSet.next()) {

minsalary = resultSet.getFloat(1);

System.out.println("Minimum salary of all employees: " + minsalary);

}

// Get the maximum salary of all employees

resultSet = statement.executeQuery(MAX_SALARY);

while (resultSet.next()) {

maxsalary = resultSet.getFloat(1);

System.out.println("Maximum salary of all employees: " + maxsalary);

}

// Get the budget for the salary rise

totalbudget = (totalsalary * percentIncrease )/100;

System.out.println("Total budget for the rise: " + totalbudget);

// Get the salary increase for the tier3 employees

// 60% of the total budget is for tier3 employees

preparedStatement = connection.prepareStatement(TIER3_EMP);

preparedStatement.setFloat(1,minsalary);

resultSet = preparedStatement.executeQuery();

while (resultSet.next()) {

tier3emp = resultSet.getInt(1);

if ( tier3emp != 0 ) {

tier3hike = (float) Math.ceil(((totalbudget * tier3percent)/tier3emp));

}

System.out.println("Number of tier3 employees: " + tier3emp);

System.out.println("Hike for tier3 employees: " + tier3hike);

}

// Get the salary increase for the tier2 employees

// 30% of the total budget is for tier2 employees

statement = connection.createStatement();

resultSet = statement.executeQuery(TIER2_EMP);

while (resultSet.next()) {

tier2emp = resultSet.getInt(1);

if ( tier2emp != 0 ) {

tier2hike = (float) Math.ceil(((totalbudget * tier2percent)/tier2emp));

}

System.out.println("Number of tier2 employees: " + tier2emp);

System.out.println("Hike for tier2 employees: " + tier2hike);

}

// Get the salary increase for the tier1 employees

// 10% of the total budget is for tier1 employees

preparedStatement = connection.prepareStatement(TIER1_EMP);

preparedStatement.setFloat(1,maxsalary);

resultSet = preparedStatement.executeQuery();

while (resultSet.next()) {

tier1emp = resultSet.getInt(1);

if ( tier1emp != 0 ) {

tier1hike = (float) Math.ceil(((totalbudget * tier1percent)/tier1emp));

}

System.out.println("Number of tier1 employees: " + tier1emp);

System.out.println("Hike for tier1 employees: " + tier1hike);

}

// Give a salary hike to tier3 employees

preparedStatement = connection.prepareStatement(TIER3_UPDATE);

preparedStatement.setFloat(1, tier3hike);

preparedStatement.setFloat(2,minsalary);

preparedStatement.executeUpdate();

// Give a salary hike to tier2 employees

preparedStatement = connection.prepareStatement(TIER2_UPDATE);

preparedStatement.setFloat(1, tier2hike);

updateCount = preparedStatement.executeUpdate();

// Give a salary hike to tier1 employees

preparedStatement = connection.prepareStatement(TIER1_UPDATE);

preparedStatement.setFloat(1, tier1hike);

preparedStatement.setFloat(2,maxsalary);

preparedStatement.executeUpdate();

// Verifying if the update was successful.

// Get the salary of all employees using a ref cursor and print it.

((OracleConnection)connection).setCreateStatementAsRefCursor(true);

Statement stmt = connection.createStatement();

((OracleStatement)stmt).setRowPrefetch(1);

rset = stmt.executeQuery("SELECT Employee_Id, First_Name, Last_Name, Email, Phone_Number, Job_Id, Salary FROM EMPLOYEES");

// fetch one row

if (rset.next()) {

System.out.println("Ename = " + rset.getObject("FIRST_NAME") +

"-- Salary = " + rset.getObject("salary"));

}

// Verifying if the update was successful.

// Get the total salary of all employees after the salary increase

resultSet = statement.executeQuery(TOTAL_SALARY);

while (resultSet.next()) {

totalsalary = resultSet.getFloat(1);

System.out.println("Total salary of all employees after the"+

" salary increase: " + totalsalary);

}

} catch (Exception e) {

e.printStackTrace();

}

return rset;

}

}

Rem SalaryHikeSP.sql

Rem

Rem Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.

Rem

Rem NAME

Rem SalaryHikeSP.sql

Rem

Rem DESCRIPTION

Rem SQL for invoking the method which gets a server side connection to

Rem internal T2 Driver

Rem

Rem MODIFIED (MM/DD/YY)

Rem nbsundar 03/23/15 - Created

Rem kmensah 03/23/15 - Contributor

REM Reads the content of the Java source from SalaryHikeSP.java

REM then compiles it

connect hr/hr

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED SalaryHikeSP_src AS

@SalaryHikeSP.java

REM Check for errors

show error

REM Create a PL/SQL wrapper

create or replace package refcur_pkg as

TYPE EmpCurTyp IS REF CURSOR;

function incrementsalary(percent IN NUMBER) return EmpCurTyp;

end refcur_pkg;

show errors;

create or replace package body refcur_pkg as

function incrementsalary(percent IN NUMBER) return EmpCurTyp

as language java

name 'SalaryHikeSP.incrementSalary(int) returns java.sql.ResultSet';

end refcur_pkg;

show error

/

---type EmpCur IS REF CURSOR;

---rc EmpCur;

--employee employees%ROWTYPE;

--rc := refcur_pkg.incrementsalary(5);

--LOOP

--fetch rc into employee;

--exit when rc%notfound;

--dbms_output.put_line(' Name = ' || employee.FIRST_NAME || ' Salary = ' || employee.SALARY);

--end loop;

<!DOCTYPE web-app PUBLIC

"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"

"http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>

<display-name>Jdbc Web Sample</display-name>

<security-role>

<role-name>manager</role-name>

</security-role>

<security-role>

<role-name>staff</role-name>

</security-role>

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

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

<html lang="en">

<meta charset="UTF-8">

<title>About</title>

<h1 color=red>HR Application for AnyCo Corporation</h1>

HR Application for the company <strong> AnyCo Corporation</strong> is designed to show the various scenarios of using an HR application. <br> <br>

HR Application is an web application that provides access to information of all employees in AnyCo Corporation.

HRStaff and HRAdmin are the two users of this application with different roles and privileges.

<strong> HRAdmin </strong>is an admin user who has all kinds of access i.e., read, write, and delete access to records of employees. <br>

Some of the functionalities accessible to HRAdmin are: <br>

<ul>

<li> <strong> List All Employees </strong>: This functionality is primarily used to retrieve all employees information. Employee information such as

Employee_id, First_Name, Last_Name, Email, Phone_Number, Job_Id and Salary are displayed. </li>

<li> <strong> Search By Employee Id </strong>: Employee records can be searched using the primary key of employee Id.</li>

<li> <strong> Update Employee Record </strong>: This functionality is used by HRAdmin to update the employee records. HRAdmin can search based on the name of the employee and the results will get displayed on the screen. Later, HRAdmin can choose an employee record to either UPDATE or DELETE. UPDATE functionality offers to update any fields such as First_name, last_name, Email, Phone_number, Job_ID, and salary of an Employee record. DELETE will delete the entire employee record from the database.</li>

<li> <strong> Increment Salary </strong> :This functionality is designed to showcase the usage of "Java in the database". HRAdmin will input the percentage for the salary hike. The Salary Hike algorithm calculates the budget for the salary hike based on the input percentage. This salary hike budget is later used to provide a salary increase to employees based on the salary tier that they belong. Please refer to the <strong> Java 2 Day Developer's Guide </strong> for detailed explanation of the salary hike algorithm. </li>

<li> <strong> About </strong>: This page provides an overview of the HR Application and explains the functionalities that it offers. </li>

<strong> HRStaff </strong> is a staff user who has only read only access. HRStaff can only view the employee records and he can search the records based on the employee Id. However, HRStaff does not have any update or delete access and hence, any update/delete functionalities are hidden to this user. <br>

Some of the functionalities accessible to HRStaff are: <br>

<ul>

<li> <strong> List All Employees </strong>: This functionality is primarily used to retrieve all employees information. Employee information such as Employee_id, First_Name, Last_Name, Email, Phone_Number, and Job_Id are displayed. </li>

<li> <strong> Search By Employee Id </strong>: Employee records can be searched using the primary key of employee Id.</li>

<li> <strong> About </strong>: This page provides an overview of the HR Application and explains the functionalities that it offers. </li>