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` ( |
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'); |
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"; |
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; |
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..."); |
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()) { System.out.print("Student
ID: " + data.getString(1)); |
e. Close
the database connection
myStatement.close(); |
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.