Description
Objective
In this lab, you will establish a connection between a Java program and a MySQL database.
Assignments 5 and 6 will use the kind of database connection that you establish in this lab.
Due: Not due for grading. This lab is for your own development. Its focus is to get your work
environment set-up for future course work.
Preparation
• Download the mysql java .jar file from https://dev.mysql.com/downloads/connector/j/
If in doubt, use the “platform independent” file.
• Download and install the MySQLWorkbench from
https://dev.mysql.com/downloads/workbench/ By some reports, the install for
Windows may ask you for pre-requisite modules and/or may not run at the start. I hear
that this can happen when trying to install the full MySQL suite. Instead, only look to
install the workbench element of the larger download.
Resources
• Database diagram at http://www.mysqltutorial.org/mysql-sample-database.aspx
for the data in this lab.
This database, which we will be using for the rest of the term, is the sales database for a
model car company.
• The csci3901 database available at db.cs.dal.ca You will need to be on the Dal network
to access this database, so you will want to use the Dal Virtual Private Network (VPN) if
you are working from home.
Alternatively, you can download and install your own copy of the mysql database onto
your local computer and install your own copy of the database, with data retrieved from
the same web page as the database diagram.
Procedure
Set-up
1. Create an IDE Java project for this lab
2. Link the mysql .jar file from the preparation section as an added or external library to your
IDE project.
3. Download and install the Dal VPN client (from https://wireless.dal.ca/vpnsoftware.php)
When asked for a server to connect to, use vpn.its.dal.ca as the target server.
4. Configure MySQLWorkbench to get a TCP/IP connection over SSH via timberlea.cs.dal.ca to
db.cs.dal.ca (Figure 1). In hat figure, store your timberlea password in the “Store in
Keychain…” button near SSH Password and store your Banner ID (with a capital B) in the
“Store in Keychain…” button near “Password” at the bottom.
Lab steps
Part 1 – Using MySQLWorkbench
1. Open the MySQLWorkbench application. Execute the command “use csci3901;” in the
workbench to access the class database.
2. Use the “show tables;” command to identify and report which tables are in the database
and not in the ER diagram from the resources.
3. Report the outcome of the following SQL statements:
a. select * from orders where OrderID = 10250;
b. select * from orderdetails where OrderID = 10250;
c. select productCode, productName, productDescription from products where
productCode = “S12_2823” or productCode = “S18_2238”;
d. select customers.customerNumber, customerName from orders, customers
where orderNumber = 10250 and orders.customerNumber =
customers.customerNumber;
Figure 1 MySQLWorkbench connection parameters
Part 2 – Java connection
4. Create a program that will ask for an order number from the user and will show the
order information on the screen as an invoice, with a well-formatted structure. The
invoice should include
a. The order date and order number
b. The customer name and address
c. The name of the sales representative
d. Order lines with product codes and product names, aligned appropriately
e. The total cost of the order
Use the steps and sample Java code in the “program a mysql connection in Java.pdf” file
associated with the lab to help make the connection to the database.
Part 3 – Testing
5. Develop a plan on how you could test the correctness of your program from Part 2.
Database diagram:
Figure 2 Database from http://www.mysqltutorial.org/mysql-sample-database.aspx
CSCI 3901
