ITAP3371: Database II

Submission Method: Demo

drop table STUDENT;

create table STUDENT (

ID varchar2(10) PRIMARY KEY,

FName varchar2(10),

LName varchar2(10),

Grade varchar(5));

Modify the Java program in Lab5 to manipulate the STUDENT table by updating the interface of the program to make it menu driven as shown below:

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

1

ID FNAME LNAME GRADE

===================================

620010 Louis Legakis B+

620011 Deoraj Narine B+

620012 Jian Wang B+

620013 Peter Angelo C+

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

2

Enter the ID:

620014

Enter the First Name:

Robert

Enter the Last Name:

Adam

Enter the Grade:

A+

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

4

Enter the ID:

620014

Enter the new value for First Name or press enter if you do not want to update it>

Enter the new value for Last Name or press enter if you do not want to update it>

Admas

Enter the new value for Grade or press enter if you do not want to update Grade>

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

1

ID FNAME LNAME GRADE

===================================

620010 Louis Legakis B+

620011 Deoraj Narine B+

620012 Jian Wang B+

620013 Peter Angelo C+

620014 Robert Admas A+

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

3

Enter the ID:

620014

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

1

ID FNAME LNAME GRADE

====================================

620010 Louis Legakis B+

620011 Deoraj Narine B+

620012 Jian Wang B+

620013 Peter Angelo C+

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

0

Your program should also support error checking when the user enters an invalid option like this:

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

5

Invalid option. Please try again.

Enter 0 to terminate>

Enter 1 to view the table>

Enter 2 to insert>

Enter 3 to delete>

Enter 4 to update>

LAB5

Instructions:

The submission method of this lab is by demo only as you need to show the instructor a working Java program enhanced by completing an exercise.

Log in to Blackboard and click on the folder Database Access in Java. Your main task today is to execute the Java source code provided and do an exercise. To complete this lab, please follow these steps:

Make sure to run the student.sql script which you will find in the Database Access in Java folder in Blackboard. This script creates the STUDENT table the Java program you will create in the following steps will manipulate.

Start NetBeans and create a project called DBAccessInJava (you must enter the name exactly as indicated since Java requires the filename to match the class name)

In the folder Database Access in Java in Blackboard, click on the file DBAccessInJava.java to display its content and copy it using CTRL-A and CTRL-C.

Completely override the generated initial code by NetBeans by using the combination CTRL-A and CTRL-V.

After overriding the code, find the line

connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "instructor","pmu")

and edit it as follows:

If you are doing this lab using one of the workstations in the lab, say, F143, then replace localhost with FF143PC16, xe with ORCL, instructor with your Oracle account (UXXXXXXXXX), and password with your actual password.

If you are doing this lab in your own laptop/desktop and you want to connect to the Oracle DB server installed there, then just replace instructor with your Oracle account (assuming that after you installed Oracle, you created your own account), and password with your actual password.

Download the JDBC jar file ojdbc6.jar from the folder Database Access in Java to any folder in the computer you are using.

Go back to your NetBeans project and select RunSet Project Configuration Customize. Then under Categories: click on Libraries.

Next click on the Add JAR/folder, and select the ojdbc6.jar file you downloaded in step 6 above.

You are now ready to compile and execute the project. First, compile the code using Run Build Project (DBAccessInJava). Your project should compile without any errors; if not, try to get some help from you lab instructor.

Execute your project by selecting Run Run Project (DBAccessInJava) and ensure that you get the following output:

-------- Oracle JDBC Connection Testing ------

Oracle JDBC Driver Registered!

ID FNAME LNAME GRADE

620010 Louis Legakis B+

620011 Deoraj Narine B+

620012 Jian Wang B+

620013 Peter Angelo C+

B+ to all

update Student set Grade = 'B+'

Number of students affected=4

delete from Student where ID='620013'

Deleting 620013

insert into Student values('620013','Peter','Angelo','C+')

Inserting 620013

ID FNAME LNAME GRADE

620010 Louis Legakis B+

620011 Deoraj Narine B+

620012 Jian Wang B+

620013 Peter Angelo C+

: Modify the method showResult() to display the number of students in the result. After successfully completing this exercise, your program should generate the following output:

-------- Oracle JDBC Connection Testing ------

Oracle JDBC Driver Registered!

ID FNAME LNAME GRADE

620010 Louis Legakis B+

620011 Deoraj Narine B+

620012 Jian Wang B+

620013 Peter Angelo C+

There are 4 students in the result

B+ to all

update Student set Grade = 'B+'

Number of students affected=4

delete from Student where ID='620013'

Deleting 620013

insert into Student values('620013','Peter','Angelo','C+')

Inserting 620013

ID FNAME LNAME GRADE

620010 Louis Legakis B+

620011 Deoraj Narine B+

620012 Jian Wang B+

620013 Peter Angelo C+

There are 4 students in the result

Note