LAB PROJECT 02: JDBC Demo 2

Goal: In this project exercise, you will learn how to:
1. Install the MySQL Database software
2. Run the SQL scripts to create the database, table and records
3. Download the MySQL JDBC Driver (Java Database Connector)
4. Add MySQL database driver to the Java Project Class Path
5. Import the Java SQL Library Classes
6. Create a Void Method
7. Create the variables for the SQL connection and SQL query
8. Create the SQL objects from the SQL Library Classes and initialize
9. Use a Try-Catch block statement for the database read error handling and exceptions
10. Setup the SQL database connection
11. Call the object’s Create Statement method
12. Create the object from the Result Set SQL Library Class
13. Execute the SQL query by calling the method and passing the query value
14. Close the database connection
15. Create an instance of object from the Class
16. Call the object’s Void method

Program Specifications:
Description: The program prompts the user to enter the student ID, first and last name, total lab exercises points, total project points and final exam points. The program will then calculate the student total points earned and the student's letter grade.  

The data will then be written to a MySQL Database.  This Java application will insert the record into the table of the MySQL database.

Below is the SQL database schema structure:
   Database Name:  studentGrade_schema
   Table Name:  student
   Fields Name and DataType:
         1. studentID   VARCHAR(10)   NOT NULL  PRIMARY KEY studentID
         2. lastName   VARCHAR(45)   NOT NULL
         3. firstName   VARCHAR(45)   NOT NULL
         4. course        VARCHAR(45)   NOT NULL
         5. labPoints             INT(11) NULL
         6. projectPoints        INT(11)  NULL
         7. finalExamPoints   INT(11)   NULL
         8. totalPoints            INT(11)   NULL
         9. letterGrade VARCHAR(1)     NULL
 


A. Software Installation Pre-requisites:
    1. You will need to have the MySQL database server installed on your PC. 
        Below is the link to download and install the software
        http://dev.mysql.com/downloads/mysql/
        NOTE: The LMC Computer Lab has the MySQL Database server software installed on all the PCs.  

        

           NOTE:  You will need to install all components (MySQL Server, MySQL Workbench, MySQL Notifier)

 

   2. a. Create a folder on your desktop called JDBC-Driver
       b. Download the MySQL JDBC Driver to your JDBC-Driver folder.  Below is the link to download the driver
            http://dev.mysql.com/downloads/connector/j/

                

       c. Verify in your JDBC-Driver folder, you have the mysql-connector-java-5.1.41-bin.jar file
           NOTE: You will only need the mysql-connector-java-5.1.41-bin.jar.
                      
You will NOT need the other folders and files (circled in red)

               

 

B. SQL Database Schema Setup Pre-requisites:

1.   Verify the MySQL57 Service is running on your PC. 
Right click on the MySQL Notifier icon in the System Tray

    

   2. Launch the MySQL Workbench from the Start Programs Menu

    

   3. Click on the Local instance MySQL57. 
       Enter the Password that you have entered during the installation setup
       NOTE:  At the LMC Computer Lab PCs, the password is comsc051

     

 

   4. Create the studentGrade_schema database by running the SQL script
       a. Click on the new SQL tab for executing scripts/ queries
       b. Copy and paste the SQL script below to the query window

CREATE DATABASE `studentGrade_schema` /*!40100 DEFAULT CHARACTER SET utf8 */;

      c. Click on the execute button to run the script

    

 

     d. Right click on the Schemas window and select Refresh All. 
         Note: You should see the newly created database called studentGrade_schema

      

 

C. SQL Table Setup Pre-requisites:

1. Create the studentGrade table by running the SQL script
    a. Double click on the studentGrade_schema database.  
        Note: The database label should turn bold
      

   b. Click on the new SQL tab button for executing scripts/ queries
   c. Copy and paste the SQL script below to the query window

CREATE TABLE `student` (
`studentID` varchar(10) NOT NULL,
`lastName` varchar(45) NOT NULL,
`firstName` varchar(45) NOT NULL,
`course` varchar(45) DEFAULT NULL,
`labPoints` int(11) DEFAULT NULL,
`projectPoints` int(11) DEFAULT NULL,
`finalExamPoints` int(11) DEFAULT NULL,
`totalPoints` int(11) DEFAULT NULL,
`letterGrade` varchar(1) DEFAULT NULL,
PRIMARY KEY (`studentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

  d. Click on the execute button to run the script

     

 

    e. Under the studentGrade_schema, right click on the Tables label and select Refresh All. 
        Note: You should see the newly created table called student

        

 

      f. Verify the table structure.   Select the studentGrade table and click on the Maintenance icon button.
         

 


D. Java Project Setup Pre-requisites:

1. Launch Java EE- Eclipse
Note:  You will need to use the Java Perspective Workbench for this exercise

2. Setup your Eclipse Workspace to point to the Project-02 folder which you already created in Step A2 above
a. Select File-> Switch Workspace
b. Browse and select your Project-02 folder as your Workspace.


E. Requirements:

1.   Create a Java Project and name it as JDBCDemo2

 

2.   Copy and drag only mysql-connector-java-5.1.41-bin.jar file into the JDBCDemo2 Project folder


3.  IMPORTANT:   Verify the mysql-connector-java-5.1.41-bin.jar file is inside the JDBCDemo2 folder



4.  Press F5 to refresh the View. You should see mysql-connector-java-5.1.41-bin.jar

 

5.   Add the MySQL database driver to the Java Project Class Path
a.  Right click on the JDBCDemo project folder
b.  Select Built Path and then Configure Build Path

 

c. Select the Libraries tab and click on Add JARs button

 

d. Select the mysql-connector-java-5.1.41-bin.jar file and click OK

e. Verify the mysql-connector-java-5.1.41-bin.jar in the Libraries path. Click the OK button.

 

6.  Create the first Class that will have the Main Method

a.      Name the Class as MainApp

b.      Choose the main method to insert into the class

            

 

7.  Create a class called Student.  This will be the superclass (parent)

8.  Create a class called Grade.  This subclass that will inherit the methods and variables of the Student superclass

9.  Create a class called Data.  This subclass that will inherit the methods and variables of the Grade superclass


F. Requirements for the Student Class:

1.  Insert the import java.util.Scanner class which will allow data to be inputted from the console. 
Insert the line @SuppressWarnings("resource") 

2.  Create the public variables

public String id, lastName, firstName, letterGrade;
public int numStudents, labPoints, projectPoints, finaExamPoints, totalPoints;

 

3.  Create the public constant static variables and initialize

public final static String COURSE= "COMSC-051 Java Programming Part 1";
public final static String COLLEGE= "Los Medanos College";

 

4.  Create the public Void Method called getNumberStudents() that will prompt the user for the number of students in the class.

try {// declare the scanner object used to input the number of students in the class
            Scanner inputNum= new Scanner(System.in);
            System.out.print("Enter the number of students in the class: ");
             numStudents = inputNum.nextInt();  // assign the input value to the variable
             System.out.println();

   } catch (Exception e) {
            System.out.println("Invalid data entered");

 }               

5.  Create the public Void Method called getData() that will prompt the user to input the data.  Below is the code for the method.

// declare the scanner object used to input the student's ID from the console
Scanner inputID= new Scanner(System.in);
System.out.print("Enter the Student ID: ");
id = inputID.nextLine();                    

// declare the scanner object used to input the student's last name from the console
Scanner inputLastName= new Scanner(System.in);
System.out.print("Enter the Last Name: ");
lastName = inputLastName.nextLine();

// declare the scanner object used to input the student's first name from the console
Scanner inputFirstName= new Scanner(System.in);
System.out.print("Enter the First Name: ");
firstName = inputFirstName.nextLine();

// declare the scanner object used to input the lab exercises points earned from the console
Scanner  inputLabPoints= new Scanner(System.in);
System.out.print("Enter the Total Points for Lab Exerices (Max Points: 750): ");
labPoints = inputLabPoints.nextInt();

// declare the scanner object  used to input the project points earned from the console
Scanner inputProjectPoints= new Scanner(System.in);
System.out.print("Enter the Points for Projects: (Max Points: 150):");
projectPoints = inputProjectPoints.nextInt();

// declare the scanner object used to input the final exam points earned from the console
Scanner inputFinalExam= new Scanner(System.in);
System.out.print("Enter the Points for Final Exam: (Max Points: 100): ");
finaExamPoints = inputFinalExam.nextInt();             

 

 

G. Requirements for the Grade Class:

1.  The Grade class will be a subclass of the Student parent class (superclass)
NOTE: A subclass will inherit the methods and variables from the superclass

 Add the keyword "extends Students" after the name of class

2.  Create the a public Method called calcTotalPoints () with an integer returned data type that will calculate the total points earned by the student

totalPoints = labPoints + projectPoints + finaExamPoints;
return totalPoints;

 

3.  Create the a public Method called calcGrade() with a String returned data type that will calculate the student’s letter grade based on the total points earned

if (totalPoints>=900) {
          return letterGrade="A";
} else if(totalPoints >=800 && totalPoints <900) {
          return letterGrade="B";
} else if (totalPoints >=700 && totalPoints <800) {
         return letterGrade="C";
} else if (totalPoints >=600  && totalPoints<700){
          return letterGrade="D";
} else {
         return letterGrade="F";
}    

 

4.  Create the public Void Method called displayData() that will display the required output on the screen.  Below is the code for the method.

// display the inputed data on the console
System.out.println();
System.out.println("Student ID: " + id);
System.out.println("Student Name: " + firstName + " " + lastName);
System.out.println("College: " + COLLEGE);
System.out.println("Course: "  + COURSE);

// display the returned results on the console
System.out.println("Total Points: " + totalPoints);
System.out.println("Course Grade: " + letterGrade);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


H. Requirements for the Data Class:

1.  Insert the following Library Classes to connect and communicate with the SQL Database:
import java.sql.*

 

2.  Create the Void Method called writeData()

3.  Declare the SQL connection String variables and initialize.
IMPORTANT:  The password value will depend on the password you entered during the MySQL installation process
At the LMC Computer Lab PCs, the password is “comsc051”.

//  create the SQL connection variables and initialize
String url ="jdbc:mySQL://localhost:3306/studentGrade_schema?autoReconnect=true&useSSL=false";
String username = "root";
String password= "comsc051";  

 

4.  Declare the variable to hold the the SQL query statement

// create the SQL query statement with the parameter ? values
String query = "insert into student (studentID, lastName, firstName, course, labPoints, projectPoints, finalExamPoints, totalPoints, letterGrade) "
+ "values (?,?,?,?,?,?,?,?,?)";

 

5.  Create the SQL objects from the SQL library class and initialize to null (empty value)

// create the SQL objects and initialize the null
Connection connection = null;
PreparedStatement myStatement = null;

 

6.  Insert a Try-Catch block to perform error handling if data cannot be read from the SQL database
a.   Call the getNumberStudents() method to get the number of students in the class

getNumberStudents();

        b. Get a connection to the student SQL database by calling the object's method and passing the SQL connection values

// get a connection to the student_schema database by creating the MyConnect object from the Connection SQL Library class
System.out.println("Connecting to the MySQL database...");
connection = DriverManager.getConnection(url, username, password);
System.out.println("MySQL Database connected!");
System.out.println();

    c. User a For loop to execute the code that will prompt the user for the student information
        and perform the iteration of the code based on the number of students in the class.
        c1.  Call the methods

getData();                                       // call the void method from the parent class- StudentGrade
totalPoints = calcTotalPoints();  // call the method from the parent class and store the return results into the variable
letterGrade = calcGrade();          // call the method from the parent class and store the return results into the variable
displayData();                               // call the void method from the parent class- StudentGrade

   c2. Call the object's prepared statement method and pass the query value

// call the object's prepared statement method and pass the query value
myStatement = connection.prepareStatement(query);

 
  c3. Set the parameter values for data type and position

// set the parameter values for data type and position
// note:  parameter position are 1 based, left to right (i.e. position 1 is studentID field  in the table
//    position 2 is lastName field, position 3 is firstName, position 5 is labPoints field
//    position 6 is projectPoints field, position 7 is finalExamPoints, position 8 is totalPoints field
//    and position 9 is the letterGrade field.
myStatement.setString(1, id);
myStatement.setString(2, lastName);
myStatement.setString(3, firstName);
myStatement.setString(4, COURSE);
myStatement.setInt(5, labPoints);
myStatement.setInt(6, projectPoints);
myStatement.setInt(7, finaExamPoints);
myStatement.setInt(8, totalPoints);
myStatement.setString(9, letterGrade);

  
c4. Execute object's SQL update method

// execute object's SQL update method and display message
myStatement.executeUpdate();
System.out.println();
System.out.println("Data has been saved to database");
System.out.println();

 
d.   Close the database connection

// close the database connection
myStatement.close();
System.out.println("Closing the connection.");

 

7.  In the catch block pass the (SQLException e) value as the parameter

System.out.println(e.toString());

 


 

 

I.      Requirements for the MainApp Class:

1.  Add comments (documentation)– Program Description, Author and Date

2.  Create the instance of the object called studentInfo from the Data class

3. Call the Object's Void writeData() method

 

J. Test:  

1.   Save your Java code

2.   Compile and run your Java program.

3.   Verify there is no syntax, logical or run-time errors.

4.   Below is the output results.
NOTE:  Copy and paste a screenshot of the Console output into Paint (save as JPG) or Word

5.  Go to MySQL Workbench and run a query to verify records have been inserted

NOTE:  Copy and paste a screenshot of the MySQL query screen into Paint (save as JPG) or Word   

 

K. Submit:

1. Submit the screen shot of the Eclipse Workbench window showing the Console output screen.
You can use Paint (save as JPG) or Word to paste the screenshot.

2. Submit the screen shot of the MySQL query screen showing the inserted records in the table (see step J5 above)

3. Zip up and submit the compressed JDBCDemo2 that is in the Project-02 folder.
NOTE: Right click on the
subfolder and select Send to “Compress Folder”.  The file will have a file extension of .zip.

        NOTE:   Upload all 3 files above separately to receive full credit