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.
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.
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. Generate a PDF from the document.
