In this lab, you will establish a connection between a Java program and a MySQL database.

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 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 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 its.dal.ca as the target server.
4. Configure MySQLWorkbench to get a TCP/IP connection over SSH via 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:
   • Select * from orders where OrderID = 10260;
   • Select * from orderdetails where OrderID = 10260;
   • Select ProductID, ProductName, CategoryID from products where ProductID = 41 or ProductID = 57;
   • 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:

• The order date and order number
• The customer name and address
• The product codes and quantities ordered
• The total cost of the order

Questions

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