LAB PROJECT 01: JDBC Demo 1

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:
This Java application will read the student data from the MySQL database and display the query results on the console

Below is the SQL database schema structure:
   Database Name:  student_schema
   Table Name:  student
   Fields Name/ 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. totalPoints  INT NULL
         6. 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 latest 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 student_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 `student_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 student_schema

      

 

C. SQL Table Setup Pre-requisites:

1. Create the student table by running the SQL script
    a. Double click on the student_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,
`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 student_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 student table and click on the Maintenance icon button.
         

 

D. Insert Records Setup Pre-requisites:

1. Create the records for the student table by running the SQL script
     a.  Expand the Tables folder and select the student table

    

 

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

insert into student (studentID, lastName, firstName, course, totalPoints, letterGrade) values ('A123456','Mistal','Joenil','COMSC-051 Java Programming Part 1','1000','A');
insert into student (studentID, lastName, firstName, course, totalPoints, letterGrade) values ('B123456','Doe','John','COMSC-051 Java Programming Part 1','850','B');
insert into student (studentID, lastName, firstName, course, totalPoints, letterGrade) values ('C123456','Brown','Susan','COMSC-051 Java Programming Part 1','700','C');
insert into student (studentID, lastName, firstName, course, totalPoints, letterGrade) values ('D123456','Smith','Brian','COMSC-051 Java Programming Part 1','650','D')

 

   d. Click on the execute button to run the script

     

    e. Verify if the records were inserted into the table.   Right click on the student table and click on the Select Rows.  

       

       

 


E. 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-01 folder which you already created in Step A2 above
a. Select File-> Switch Workspace
b. Browse and select your Project-01 folder as your Workspace.


F. Requirements:

1.   Create a Java Project and name it as JDBCDemo1

 

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


3.  IMPORTANT:   Verify the mysql-connector-java-5.1.41-bin.jar file is inside the JDBCDemo1 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 Data.

G. 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 readData()

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

String url ="jdbc:mySQL://localhost:3306/student_schema";
String username = "root";
String password= "comsc051";  

 

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

String query = "select studentID, lastName, firstName, course, totalPoints, letterGrade from student";

 

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

Connection connection = null;
Statement myStatement= null;

 

6.  Insert a Try-Catch block to perform error handling if data cannot be read from the SQL database

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

System.out.println("Connecting to the MySQL database...");
connection = DriverManager.getConnection(url, username, password);
System.out.println("MySQL Database connected!");
System.out.println();      

b.  Call the object's Create Statement method

myStatement = connection.createStatement();

c.    Create the object from the ResultSet SQL Library class and execute the SQL query by calling the method and passing the query value

ResultSet data = myStatement.executeQuery(query);

      d. Perform a While loop to display the query results from the student table

while (data.next()) {
      // set the parameter values for 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 totalPoints field
      //    and position 6 is letterGrade in the table)

     System.out.print("Student ID: " + data.getString(1));
     System.out.print("  ");
     System.out.print("Name: " + data.getString(3) + " " + data.getString(2));
     System.out.println(" ");
     System.out.print("Total Points: " + data.getInt(5));
     System.out.print("  ");
     System.out.println("Grade: " + data.getString(6));
     System.out.println();
}

e.  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());

 

 

 

 

H. 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 method

 

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

 

J. 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. Zip up and submit the compressed JDBCDemo1 that is in the Project-01 folder.
NOTE: Right click on the
subfolder and select Send to “Compress Folder”.  The file will have a file extension of .zip.