CSCI Lab 7: Java { MySQL Connection Solution

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 le from https://dev.mysql.com/downloads/connector/j/ If in doubt, use the \platform independent” le.

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

4. Con gure 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 work-bench 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:

1. 1. Select * from orders where OrderID = 10260;

1. 2. Select * from orderdetails where OrderID = 10260;

1. 3. Select ProductID, ProductName, CategoryID from products where ProductID = 41 or ProductID = 57;

1. 4. 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:

1. 1. The order date and order number

1. 2. The customer name and address

1. 3. The product codes and quantities ordered

1. 4. The total cost of the order

Questions

1. How could you test the correctness of your program from Part 2?

Reporting

1. In one le, 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. Generate a PDF from the document.

2. 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 re ect how well you have used MySQLWorkbench and the Java mysql connection.

3