Centennial College

COMP 228: Java Programming

LAB Assignment #5

1. This is a walk-through lab. You have two exercises to complete for this lab:

Your first job is to implement a database application using JDBC. You need to follow the code below and debug your code as/if problems arise. To run the application as is, you need create a database table named Student. Use your Oracle account provided to you during the class hour to create the table Student. Please note that the application uses prepared statements. All the concepts used were explained during the lecture time.[50 marks]

Your second job is to modify the application by adding at least ONE more functionality. Some suggestions have been provided for such a modification. However, you are not restricted to the suggestions provide.[50 marks]

Submit your lab within the deadline (7days from the issuing date) posted in the drop box titled Lab5. You are required to submit a Word file with screen shots and code plus the zipped java files.

CODE:

import javafx.application.Application;

import javafx.scene.Scene;

import javafx.scene.control.Button;

import javafx.scene.control.Label;

import javafx.scene.control.TextField;

import javafx.scene.layout.HBox;

import javafx.scene.layout.VBox;

import javafx.stage.Stage;

import java.sql.*;

public class FindGradeWithPreparedStatement extends Application

{

private PreparedStatement preparedStatement;

private TextField tfSIN = new TextField();

private Label lblStatus = new Label();

@Override

public void start(Stage primaryStage)

{

//initialize database connection and create a statement object

initializeDB();

Button btShowGrade = new Button("Show Grade");

HBox hBox = new HBox(10);

hBox.setStyle("-fx-margin:20,10,10,10; -fx-padding:10,10,10,10;-fx-background-color:WHITE; -fx-border-radius:10;-fx-padding:5; -fx-background-radius:10");

hBox.getChildren().addAll(new Label(" SIN"), tfSIN, (btShowGrade));

btShowGrade.setStyle("-fx-font: 16 Verdana; -fx-base: #cccc00;-fx-border-radius:10;-fx-padding:5,5,5,5; -fx-background-radius:10;");

tfSIN.setStyle("-fx-font: 14 Verdana; -fx-base: #cccc00;-fx-border-radius:10;-fx-padding:5,5,5,5; -fx-background-radius:10");

tfSIN.setPrefWidth(200);

VBox vBox = new VBox(10);

btShowGrade.setMouseTransparent(false);

vBox.setStyle("-fx-margin:20,10,10,10; -fx-padding:10,10,10,10;-fx-background-color:#ffff99;-fx-border-radius:10;-fx-padding:5; -fx-background-radius:10;");

vBox.getChildren().addAll(hBox, lblStatus);

lblStatus.setStyle("-fx-font: 18 Verdana;");

tfSIN.setPrefColumnCount(5);

//Register handler

btShowGrade.setOnAction(e -> showGrade());

//routine for UI

Scene scene = new Scene(vBox, 420, 300);

primaryStage.setTitle("FindGrade");

primaryStage.setScene(scene);

primaryStage.show();

}

private void initializeDB()

{

try

{

//load jdbc driver

Class.forName("oracle.jdbc.OracleDriver");

//for sqlserver

//Class.forName(com.mysql.jdbc.Driver");

System.out.println("Driver loaded.");

//establish a connection

Connection connection = D..:@199.212.26.208:1521:SQLD","COMP228_F19_sy_61", "password");

System.out.println("Database connected.");

String queryString = "select firstName, mi, lastName, grade from STUDENT1 where sin = ?";

//create a statement

preparedStatement = connection.prepareStatement(queryString);

}

catch(Exception ex)

{

ex.printStackTrace();

}

}

private void showGrade()

{

String sin = tfSIN.getText();

try

{

preparedStatement.setString(1, sin);

ResultSet rset = preparedStatement.executeQuery();

if(rset.next())

{

String lastName = rset.getNString(3);

String mi = rset.getString(2);

String firstName = rset.getString(1);

String grade = rset.getString(4);

//display result in a label

lblStatus.setText(firstName + " " + mi + " " + lastName + "'s grade is " + grade);

}

else

{

lblStatus.setText("Not found.");

}

}

catch(SQLException ex)

{

ex.printStackTrace();

}

}

public static void main(String[] args)

{

Application.launch();

}

}

DATABASE:

-- create table using this query:-

create table Student1 (

sin varchar2(50),

firstName varchar2(50),

mi varchar2(50),

lastName varchar2(50),

grade varchar2(50)

);

Note: The following is just to show how to insert. DO NOT INSERT THE SAME DATA. USE YOUR OWN DATA.

-- insert 10 entries by replacing values in above insert query then search by putting sin in textbox which we inserted using insert queries.

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('1','Taifur','M','Adams','A');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('2','Khairul','K','Wara','A+');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('3','Mashud','A','Ahmed','A');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('4','Sanjib','S','Khan','A+');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('5','Saifuddin','C','Chowdhury','A');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('6','Tomas','T','San','A+');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('7','David','D','Poka','A');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('8','Fione','F','Ault','B');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('9','Lee','L','Chung','C');

INSERT INTO Student1 (sin, firstName, mi, lastName, grade) VALUES('10','Poker','L','China','C');

A test run should resemble the following. Please do not use this data.

EXCEPTION HANDLING

TABLE IN SQL should look like this. Please do not use the same data. Each student's data should be different from each other.

2.Add at least one more functionality to the application that you have just developed. Some suggestions:

You may wish to create an additional button to expose other data related to the SIN.

You may also wish to create tables with additional information and display any information of your choice.

[These are just suggestions; you are allowed to modify the application with added functionalities of your choice.][50 marks]