Description
Objective
In this lab, you will establish a connection between a Java program and a MySQL database.
Complete this lab alone or in a group of 2.
Assignment 5 will use the kind of database connection that you establish in this lab.
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.zentut.com/sql-tutorial/sql-sample-database/ for the data in
this lab
• 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).
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 a new project in your IDE.
2. Link the mysql.jar file from the preparation section as an 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.
1
4. Configure MySQLWorkbench to get a TCP/IP connection over SSH via timberlea.cs.dal.ca
to db.cs.dal.ca.
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 command show tables; command to identify and report which tables are in the
database.
3. Report the outcome of the following SQL statements:
(a) Select * from orders where OrderID = 10260;
(b) Select * from orderdetails where OrderID = 10260;
(c) Select ProductID, ProductName, CategoryID from products where ProductID = 41
or ProductID = 57;
(d) Select customers.CustomerID, CompanyName from orders, customers where OrderID
= 10260 and orders.customerID = customers.CustomerID;
Part 2 – Java connection
1. 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. The invoice should include:
(a) The order date and order number
(b) The customer name and address
(c) The product codes and quantities ordered
(d) The total cost of the order
Questions
1. How could you test the correctness of your program from Part 2?
Reporting
1. In one file, list
• The members of your team.
• The answers to the questions in part 1 (steps 2 and 3).
• The output of your program on order 10260 from part 2.
• Your answer to the question in Part 3.
2
2. Generate a PDF from the document.
3. Submit the PDF and your Java program in Brightspace in the Lab/Lab 7 folder.
Assessment
The assessment will be on a letter grade and will reflect how well you have used MySQLWorkbench
and the Java mysql connection.
3
