JAVA PROGRAMMING (CSE1007)
DIGITAL ASSIGNMENT 1
Name : Aashish Sharma
Reg. No: 19BCE0971
Slot : G2
--------------------------------------------------------------------------------------------------------------------------------------
2. Create a JAVAFX program that allows the user to store the records of employees name, age,
salary and experience into the database. And also, a function to search the employee name with
his name.
Solution:
DBUtil Class Code:
package sample.util;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.*;
/**
* Created by ONUR BASKIRT on 22.02.2016.
*/
public class DBUtil {
//Declare JDBC Driver
private static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
//Connection
private static Connection conn = null;
//Connection String
//String connStr = "jdbc:oracle:thin:Username/Password@IP:Port/SID";
//Username=HR, Password=HR, IP=localhost, IP=1521, SID=xe
private static final String connStr = "jdbc:oracle:thin:HR/HR@localhost:1521/xe";
//Connect to DB
public static void dbConnect() throws SQLException, ClassNotFoundException {
//Setting Oracle JDBC Driver
try {
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println("Where is your Oracle JDBC Driver?");
e.printStackTrace();
throw e;
System.out.println("Oracle JDBC Driver Registered!");
//Establish the Oracle Connection using Connection String
try {
conn = DriverManager.getConnection(connStr);
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console" + e);
e.printStackTrace();
throw e;
//Close Connection
public static void dbDisconnect() throws SQLException {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
} catch (Exception e){
throw e;
//DB Execute Query Operation
public static ResultSet dbExecuteQuery(String queryStmt) throws SQLException,
ClassNotFoundException {
//Declare statement, resultSet and CachedResultSet as null
Statement stmt = null;
ResultSet resultSet = null;
CachedRowSetImpl crs = null;
try {
//Connect to DB (Establish Oracle Connection)
dbConnect();
System.out.println("Select statement: " + queryStmt + "\n");
//Create statement
stmt = conn.createStatement();
//Execute select (query) operation
resultSet = stmt.executeQuery(queryStmt);
//CachedRowSet Implementation
//In order to prevent "java.sql.SQLRecoverableException: Closed Connection: next" error
//We are using CachedRowSet
crs = new CachedRowSetImpl();
crs.populate(resultSet);
} catch (SQLException e) {
System.out.println("Problem occurred at executeQuery operation : " + e);
throw e;
} finally {
if (resultSet != null) {
//Close resultSet
resultSet.close();
if (stmt != null) {
//Close Statement
stmt.close();
//Close connection
dbDisconnect();
}
//Return CachedRowSet
return crs;
//DB Execute Update (For Update/Insert/Delete) Operation
public static void dbExecuteUpdate(String sqlStmt) throws SQLException, ClassNotFoundException {
//Declare statement as null
Statement stmt = null;
try {
//Connect to DB (Establish Oracle Connection)
dbConnect();
//Create Statement
stmt = conn.createStatement();
//Run executeUpdate operation with given sql statement
stmt.executeUpdate(sqlStmt);
} catch (SQLException e) {
System.out.println("Problem occurred at executeUpdate operation : " + e);
throw e;
} finally {
if (stmt != null) {
//Close statement
stmt.close();
//Close connection
dbDisconnect();
Employee Class Code:
package sample.model;
import javafx.beans.property.*;
import java.sql.Date;
/**
* Created by ONUR BASKIRT on 27.02.2016.
*/
public class Employee {
//Declare Employees Table Columns
private IntegerProperty employee_id;
private StringProperty first_name;
private StringProperty last_name;
private StringProperty email;
private StringProperty phone_number;
private SimpleObjectProperty<Date> hire_date;
private StringProperty job_id;
private IntegerProperty salary;
private DoubleProperty commission_pct;
private IntegerProperty manager_id;
private IntegerProperty department_id;
//Constructor
public Employee() {
this.employee_id = new SimpleIntegerProperty();
this.first_name = new SimpleStringProperty();
this.last_name = new SimpleStringProperty();
this.email = new SimpleStringProperty();
this.phone_number = new SimpleStringProperty();
this.hire_date = new SimpleObjectProperty<>();
this.job_id = new SimpleStringProperty();
this.salary = new SimpleIntegerProperty();
this.commission_pct = new SimpleDoubleProperty();
this.manager_id = new SimpleIntegerProperty();
this.department_id = new SimpleIntegerProperty();
//employee_id
public int getEmployeeId() {
return employee_id.get();
public void setEmployeeId(int employeeId){
this.employee_id.set(employeeId);
public IntegerProperty employeeIdProperty(){
return employee_id;
//first_name
public String getFirstName () {
return first_name.get();
public void setFirstName(String firstName){
this.first_name.set(firstName);
public StringProperty firstNameProperty() {
return first_name;
//last_name
public String getLastName () {
return last_name.get();
public void setLastName(String lastName){
this.last_name.set(lastName);
public StringProperty lastNameProperty() {
return last_name;
//email
public String getEmail () {
return email.get();
public void setEmail (String email){
this.email.set(email);
public StringProperty emailProperty() {
return email;
//phone_number
public String getPhoneNumber () {
return phone_number.get();
public void setPhoneNumber (String phoneNumber){
this.phone_number.set(phoneNumber);
public StringProperty phoneNumberProperty() {
return phone_number;
//hire_date
public Object getHireDate(){
return hire_date.get();
public void setHireDate(Date hireDate){
this.hire_date.set(hireDate);
public SimpleObjectProperty<Date> hireDateProperty(){
return hire_date;
//job_id
public String getJobId () {
return job_id.get();
}
public void setJobId (String jobId){
this.job_id.set(jobId);
public StringProperty jobIdProperty() {
return job_id;
//salary
public int getSalary() {
return salary.get();
public void setSalary(int salary){
this.salary.set(salary);
public IntegerProperty salaryProperty(){
return salary;
//commission_pct
public double getCommissionPct() {
return commission_pct.get();
public void setCommissionPct(double commissionPct){
this.commission_pct.set(commissionPct);
public DoubleProperty commissionPctProperty(){
return commission_pct;
//manager_id
public int getManagerId() {
return manager_id.get();
}
public void setManagerId(int managerId){
this.manager_id.set(managerId);
public IntegerProperty managerIdProperty(){
return manager_id;
//department_id
public int getDepartmanId() {
return department_id.get();
public void setDepartmantId(int departmentId){
this.manager_id.set(departmentId);
public IntegerProperty departmentIdProperty(){
return department_id;
EmployeeDAO Class Code:
package sample.model;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import sample.util.DBUtil;
import java.sql.ResultSet;
import java.sql.SQLException;
public class EmployeeDAO {
//*******************************
//SELECT an Employee
//*******************************
public static Employee searchEmployee (String empId) throws SQLException,
ClassNotFoundException {
//Declare a SELECT statement
String selectStmt = "SELECT * FROM employees WHERE employee_id="+empId;
//Execute SELECT statement
try {
//Get ResultSet from dbExecuteQuery method
ResultSet rsEmp = DBUtil.dbExecuteQuery(selectStmt);
//Send ResultSet to the getEmployeeFromResultSet method and get employee object
Employee employee = getEmployeeFromResultSet(rsEmp);
//Return employee object
return employee;
} catch (SQLException e) {
System.out.println("While searching an employee with " + empId + " id, an error occurred: " + e);
//Return exception
throw e;
//Use ResultSet from DB as parameter and set Employee Object's attributes and return employee
object.
private static Employee getEmployeeFromResultSet(ResultSet rs) throws SQLException
Employee emp = null;
if (rs.next()) {
emp = new Employee();
emp.setEmployeeId(rs.getInt("EMPLOYEE_ID"));
emp.setFirstName(rs.getString("FIRST_NAME"));
emp.setLastName(rs.getString("LAST_NAME"));
emp.setEmail(rs.getString("EMAIL"));
emp.setPhoneNumber(rs.getString("PHONE_NUMBER"));
emp.setHireDate(rs.getDate("HIRE_DATE"));
emp.setJobId(rs.getString("JOB_ID"));
emp.setSalary(rs.getInt("SALARY"));
emp.setCommissionPct(rs.getDouble("COMMISSION_PCT"));
emp.setManagerId(rs.getInt("MANAGER_ID"));
emp.setDepartmantId(rs.getInt("DEPARTMENT_ID"));
return emp;
//*******************************
//SELECT Employees
//*******************************
public static ObservableList<Employee> searchEmployees () throws SQLException,
ClassNotFoundException {
//Declare a SELECT statement
String selectStmt = "SELECT * FROM employees";
//Execute SELECT statement
try {
//Get ResultSet from dbExecuteQuery method
ResultSet rsEmps = DBUtil.dbExecuteQuery(selectStmt);
//Send ResultSet to the getEmployeeList method and get employee object
ObservableList<Employee> empList = getEmployeeList(rsEmps);
//Return employee object
return empList;
} catch (SQLException e) {
System.out.println("SQL select operation has been failed: " + e);
//Return exception
throw e;
//Select * from employees operation
private static ObservableList<Employee> getEmployeeList(ResultSet rs) throws SQLException,
ClassNotFoundException {
//Declare a observable List which comprises of Employee objects
ObservableList<Employee> empList = FXCollections.observableArrayList();
while (rs.next()) {
Employee emp = new Employee();
emp.setEmployeeId(rs.getInt("EMPLOYEE_ID"));
emp.setFirstName(rs.getString("FIRST_NAME"));
emp.setLastName(rs.getString("LAST_NAME"));
emp.setEmail(rs.getString("EMAIL"));
emp.setPhoneNumber(rs.getString("PHONE_NUMBER"));
emp.setHireDate(rs.getDate("HIRE_DATE"));
emp.setJobId(rs.getString("JOB_ID"));
emp.setSalary(rs.getInt("SALARY"));
emp.setCommissionPct(rs.getDouble("COMMISSION_PCT"));
emp.setManagerId(rs.getInt("MANAGER_ID"));
emp.setDepartmantId(rs.getInt("DEPARTMENT_ID"));
//Add employee to the ObservableList
empList.add(emp);
//return empList (ObservableList of Employees)
return empList;
//*************************************
//UPDATE an employee's email address
//*************************************
public static void updateEmpEmail (String empId, String empEmail) throws SQLException,
ClassNotFoundException {
//Declare a UPDATE statement
String updateStmt =
"BEGIN\n" +
" UPDATE employees\n" +
" SET EMAIL = '" + empEmail + "'\n" +
" WHERE EMPLOYEE_ID = " + empId + ";\n" +
" COMMIT;\n" +
"END;";
//Execute UPDATE operation
try {
DBUtil.dbExecuteUpdate(updateStmt);
} catch (SQLException e) {
System.out.print("Error occurred while UPDATE Operation: " + e);
throw e;
//*************************************
//DELETE an employee
//*************************************
public static void deleteEmpWithId (String empId) throws SQLException, ClassNotFoundException {
//Declare a DELETE statement
String updateStmt =
"BEGIN\n" +
" DELETE FROM employees\n" +
" WHERE employee_id ="+ empId +";\n" +
" COMMIT;\n" +
"END;";
//Execute UPDATE operation
try {
DBUtil.dbExecuteUpdate(updateStmt);
} catch (SQLException e) {
System.out.print("Error occurred while DELETE Operation: " + e);
throw e;
//*************************************
//INSERT an employee
//*************************************
public static void insertEmp (String name, String lastname, String email) throws SQLException,
ClassNotFoundException {
//Declare a DELETE statement
String updateStmt =
"BEGIN\n" +
"INSERT INTO employees\n" +
"(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)\n" +
"VALUES\n" +
"(sequence_employee.nextval, '"+name+"', '"+lastname+"','"+email+"', SYSDATE, 'IT_PROG');\n" +
"END;";
//Execute DELETE operation
try {
DBUtil.dbExecuteUpdate(updateStmt);
} catch (SQLException e) {
System.out.print("Error occurred while DELETE Operation: " + e);
throw e;
Employee View Code:
<?xml version="1.0" encoding="UTF-8"?>
<?import javafx.scene.control.*?>
<?import javafx.scene.layout.*?>
<?import javafx.scene.text.*?>
<AnchorPane maxHeight="-Infinity" maxWidth="-Infinity" minHeight="-Infinity" minWidth="-
Infinity"
prefHeight="344.0" prefWidth="600.0" xmlns="http://javafx.com/javafx/8"
xmlns:fx="http://javafx.com/fxml/1"
fx:controller="sample.controller.EmployeeController">
<children>
<TextField fx:id="empIdText" layoutX="193.0" layoutY="41.0" prefHeight="25.0" prefWidth="67.0"
promptText="Emp ID" />
<Label layoutX="194.0" layoutY="21.0" text="Employee ID" />
<Button fx:id="searchEmpBtn" layoutX="194.0" layoutY="70.0" mnemonicParsing="false"
onAction="#searchEmployee" prefHeight="25.0" prefWidth="56.0" text="Search" />
<Button fx:id="deleteEmpBtn" layoutX="332.0" layoutY="70.0" mnemonicParsing="false"
onAction="#deleteEmployee" prefHeight="25.0" prefWidth="56.0" text="Delete" />
<Button fx:id="updateEmpBtn" layoutX="263.0" layoutY="70.0" mnemonicParsing="false"
onAction="#updateEmployeeEmail" prefHeight="25.0" prefWidth="56.0" text="Update" />
<Button fx:id="addEmpBtn" layoutX="82.0" layoutY="114.0" mnemonicParsing="false"
onAction="#insertEmployee" text="Add Employee" />
<TextArea fx:id="resultArea" layoutX="7.0" layoutY="250.0" prefHeight="85.0" prefWidth="167.0"
wrapText="true" />
<Label layoutX="9.0" layoutY="231.0" text="Result Console">
<font>
<Font name="System Bold" size="12.0" />
</font></Label>
<TextField fx:id="newEmailText" layoutX="268.0" layoutY="41.0" prefHeight="25.0"
prefWidth="120.0"
promptText="new email" />
<Label layoutX="270.0" layoutY="21.0" text="New Email" />
<VBox layoutX="97.0" layoutY="24.0" spacing="4.0">
<children>
<TextField fx:id="nameText" prefHeight="25.0" prefWidth="79.0" promptText="Name" />
<TextField fx:id="surnameText" prefHeight="25.0" prefWidth="79.0" promptText="Surname" />
<TextField fx:id="emailText" prefHeight="25.0" prefWidth="79.0" promptText="email" />
</children>
</VBox>
<VBox layoutX="9.0" layoutY="28.0" prefWidth="67.0" spacing="12.0">
<children>
<Label text="Name" />
<Label text="Surname" />
<Label text="Email" />
</children>
</VBox>
<Separator layoutY="14.0" prefHeight="4.0" prefWidth="600.0" />
<Separator layoutX="180.0" layoutY="14.0" orientation="VERTICAL" prefHeight="333.0"
prefWidth="7.0" />
<TableView fx:id="employeeTable" editable="true" layoutX="193.0" layoutY="102.0"
prefHeight="234.0"
prefWidth="393.0" tableMenuButtonVisible="true">
<columns>
<TableColumn fx:id="empIdColumn" prefWidth="57.0" text="Id" />
<TableColumn fx:id="empNameColumn" prefWidth="75.0" text="Name" />
<TableColumn fx:id="empLastNameColumn" prefWidth="73.0" text="LastName" />
<TableColumn fx:id="empEmailColumn" prefWidth="79.0" text="Email" />
<TableColumn fx:id="empPhoneNumberColumn" prefWidth="73.0" text="Phone" />
<TableColumn fx:id="empHireDateColumn" prefWidth="93.0" text="Hire Date" />
</columns>
</TableView>
<Button fx:id="searchEmpsBtn" layoutX="396.0" layoutY="70.0" mnemonicParsing="false"
onAction="#searchEmployees" prefHeight="25.0" prefWidth="139.0" text="Search All Employees"
/>
</children>
</AnchorPane>
EmployeeController Class Code:
package sample.controller;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextArea;
import javafx.scene.control.TextField;
import sample.model.Employee;
import sample.model.EmployeeDAO;
import java.sql.Date;
import java.sql.SQLException;
/**
* Created by ONUR BASKIRT on 23.02.2016.
*/
public class EmployeeController {
@FXML
private TextField empIdText;
@FXML
private TextArea resultArea;
@FXML
private TextField newEmailText;
@FXML
private TextField nameText;
@FXML
private TextField surnameText;
@FXML
private TextField emailText;
@FXML
private TableView employeeTable;
@FXML
private TableColumn<Employee, Integer> empIdColumn;
@FXML
private TableColumn<Employee, String> empNameColumn;
@FXML
private TableColumn<Employee, String> empLastNameColumn;
@FXML
private TableColumn<Employee, String> empEmailColumn;
@FXML
private TableColumn<Employee, String> empPhoneNumberColumn;
@FXML
private TableColumn<Employee, Date> empHireDateColumn;
//Search an employee
@FXML
private void searchEmployee (ActionEvent actionEvent) throws ClassNotFoundException,
SQLException {
try {
//Get Employee information
Employee emp = EmployeeDAO.searchEmployee(empIdText.getText());
//Populate Employee on TableView and Display on TextArea
populateAndShowEmployee(emp);
} catch (SQLException e) {
e.printStackTrace();
resultArea.setText("Error occurred while getting employee information from DB.\n" + e);
throw e;
//Search all employees
@FXML
private void searchEmployees(ActionEvent actionEvent) throws SQLException,
ClassNotFoundException {
try {
//Get all Employees information
ObservableList<Employee> empData = EmployeeDAO.searchEmployees();
//Populate Employees on TableView
populateEmployees(empData);
} catch (SQLException e){
System.out.println("Error occurred while getting employees information from DB.\n" + e);
throw e;
}
//Initializing the controller class.
//This method is automatically called after the fxml file has been loaded.
@FXML
private void initialize () {
/*
The setCellValueFactory(...) that we set on the table columns are used to determine
which field inside the Employee objects should be used for the particular column.
The arrow -> indicates that we're using a Java 8 feature called Lambdas.
(Another option would be to use a PropertyValueFactory, but this is not type-safe
We're only using StringProperty values for our table columns in this example.
When you want to use IntegerProperty or DoubleProperty, the setCellValueFactory(...)
must have an additional asObject():
*/
empIdColumn.setCellValueFactory(cellData ->
cellData.getValue().employeeIdProperty().asObject());
empNameColumn.setCellValueFactory(cellData -> cellData.getValue().firstNameProperty());
empLastNameColumn.setCellValueFactory(cellData -> cellData.getValue().lastNameProperty());
empEmailColumn.setCellValueFactory(cellData -> cellData.getValue().emailProperty());
empPhoneNumberColumn.setCellValueFactory(cellData ->
cellData.getValue().phoneNumberProperty());
empHireDateColumn.setCellValueFactory(cellData -> cellData.getValue().hireDateProperty());
//Populate Employee
@FXML
private void populateEmployee (Employee emp) throws ClassNotFoundException {
//Declare and ObservableList for table view
ObservableList<Employee> empData = FXCollections.observableArrayList();
//Add employee to the ObservableList
empData.add(emp);
//Set items to the employeeTable
employeeTable.setItems(empData);
}
//Set Employee information to Text Area
@FXML
private void setEmpInfoToTextArea ( Employee emp) {
resultArea.setText("First Name: " + emp.getFirstName() + "\n" +
"Last Name: " + emp.getLastName());
//Populate Employee for TableView and Display Employee on TextArea
@FXML
private void populateAndShowEmployee(Employee emp) throws ClassNotFoundException {
if (emp != null) {
populateEmployee(emp);
setEmpInfoToTextArea(emp);
} else {
resultArea.setText("This employee does not exist!\n");
//Populate Employees for TableView
@FXML
private void populateEmployees (ObservableList<Employee> empData) throws
ClassNotFoundException {
//Set items to the employeeTable
employeeTable.setItems(empData);
//Update employee's email with the email which is written on newEmailText field
@FXML
private void updateEmployeeEmail (ActionEvent actionEvent) throws SQLException,
ClassNotFoundException {
try {
EmployeeDAO.updateEmpEmail(empIdText.getText(),newEmailText.getText());
resultArea.setText("Email has been updated for, employee id: " + empIdText.getText() + "\n");
} catch (SQLException e) {
resultArea.setText("Problem occurred while updating email: " + e);
}
//Insert an employee to the DB
@FXML
private void insertEmployee (ActionEvent actionEvent) throws SQLException,
ClassNotFoundException {
try {
EmployeeDAO.insertEmp(nameText.getText(),surnameText.getText(),emailText.getText());
resultArea.setText("Employee inserted! \n");
} catch (SQLException e) {
resultArea.setText("Problem occurred while inserting employee " + e);
throw e;
//Delete an employee with a given employee Id from DB
@FXML
private void deleteEmployee (ActionEvent actionEvent) throws SQLException,
ClassNotFoundException {
try {
EmployeeDAO.deleteEmpWithId(empIdText.getText());
resultArea.setText("Employee deleted! Employee id: " + empIdText.getText() + "\n");
} catch (SQLException e) {
resultArea.setText("Problem occurred while deleting employee " + e);
throw e;
Main Class Code:
package sample;
import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Scene;
import javafx.scene.layout.AnchorPane;
import javafx.scene.layout.BorderPane;
import javafx.stage.Stage;
import java.io.IOException;
//Main class which extends from Application Class
public class Main extends Application {
//This is our PrimaryStage (It contains everything)
private Stage primaryStage;
//This is the BorderPane of RootLayout
private BorderPane rootLayout;
@Override
public void start(Stage primaryStage) {
//1) Declare a primary stage (Everything will be on this stage)
this.primaryStage = primaryStage;
//Optional: Set a title for primary stage
this.primaryStage.setTitle("SW Test Academy - Sample JavaFX App");
//2) Initialize RootLayout
initRootLayout();
//3) Display the EmployeeOperations View
showEmployeeView();
//Initializes the root layout.
public void initRootLayout() {
try {
//First, load root layout from RootLayout.fxml
FXMLLoader loader = new FXMLLoader();
loader.setLocation(Main.class.getResource("view/RootLayout.fxml"));
rootLayout = (BorderPane) loader.load();
//Second, show the scene containing the root layout.
Scene scene = new Scene(rootLayout); //We are sending rootLayout to the Scene.
primaryStage.setScene(scene); //Set the scene in primary stage.
/*//Give the controller access to the main.
RootLayoutController controller = loader.getController();
controller.setMain(this);*/
//Third, show the primary stage
primaryStage.show(); //Display the primary stage
} catch (IOException e) {
e.printStackTrace();
//Shows the employee operations view inside the root layout.
public void showEmployeeView() {
try {
//First, load EmployeeView from EmployeeView.fxml
FXMLLoader loader = new FXMLLoader();
loader.setLocation(Main.class.getResource("view/EmployeeView.fxml"));
AnchorPane employeeOperationsView = (AnchorPane) loader.load();
// Set Employee Operations view into the center of root layout.
rootLayout.setCenter(employeeOperationsView);
} catch (IOException e) {
e.printStackTrace();
public static void main(String args) {
launch(args);
Output:
