CS 220X, D-term 2015

Homework 6 - intro to SQL and JDBC

Due: Tuesday, May 5 at 5pm

No late assignments will be accepted!

Outcomes

After successfully completing this assignment, you will...

know the most common SQL commands for manipulating a relational database
know how to write a Java application that can interface with a relational database
be familiar with the JDBC (Java Database Connectivity) API

Before Starting

Review Chapter 23 (you may skip section 23.3) in Horstmann.

The Assignment

For this assignment, you will be provided with a program that connects to an existing database on the WPI mySQL server. Your job is to add a series of SQL commands to the program, and to demonstrate that the commands are correct by displaying the results of each query.

Preliminaries

Create an Eclipse project for HW6. You may use the main method in this HW6 starter code as a starting point for your homework. In order for the starter code to compile and run correctly, you'll need to download the MySQL JDBC driver and add it to the Build Path of your Eclipse project. Here are the steps:

download and unzip MySQL Connector/J (choose the download link at the bottom of the page, for the mysql-connector-java-3.1.14.zip ZIP archive file)
Right-click on the project containing your Eclipse HW6 starter program. Choose “Build Path” and “Configure Build Path...”
Select the Libraries tab. Click “Add external jars...” and browse to the MySQL Connector/J jar file. Double-click on the jar file.

You should now be able to compile and execute the HW6 starter program. Don’t proceed beyond this point unless the program compiles with no errors and executes correctly. You should see the following text displayed on the console when the program runs:

Connecting to database...
Creating statement...
Participant Amanda has ID 767
Goodbye!

Here's a summary of SQL commands that you may refer to when constructing your queries/updates.

Familiarize yourself with the database

The HW6 starter file provides you with the name of the database (small_gs_example) and a username/password that provides access to the database. Use this information to examine the database to become familiar with its tables and structure. You may do this any way you wish: via the MySQL Workbench that was introduced in Lab 6, or by opening a command window and running mysql directly, or by writing commands in a Java program. Using the Workbench or a command window are the easiest ways. If you run mysql from a command window, use this command (which should be issued from the directory that contains mysql.exe):

mysql -hmysql.wpi.edu -urestricted_gs -pjMLuBo small_gs_example

You’ll find that the database consists of 5 tables, with the names VOTE, TIME_SLOT, PARTICIPANT, PARTICIPANT_MEETING_REL, and MEETING. Look at the attributes of each table, and note in particular how various tables are related to each other.

Here are screenshots from the MySQL Workbench that provide additional information about the tables.

Now it’s time to write some SQL commands in your program.

Write SQL Statements in a Java Program

Add Java statements to the HW6 starter file that will accomplish the following:

Determine the names of the columns in the MEETING table, and display the column names on the console (hint: read section 23.4.4 in the textbook on ResultSet Metadata).
Write a query to find the names of all participants for the meeting with ID 1144. Display the names on the console, preceded by the message, “Names of participants in meeting 1144”.
Insert a new participant with your CCC username as the participant’s name. Don’t specify the participant ID - let the ID be determined by the database. Determine the ID that was assigned to the new participant, and display it on the console (the statement that displays the ID should display the name of the new participant as well).
Ask the user of your program to enter a meeting ID, and read it in. Count the number of people participating in the meeting with the specified ID. Display a message on the console that gives both the meeting ID and the number of participants.
Determine the names of all people who participate in meetings that meet on Tuesdays. Display the names, preceded by the message, “Participants attending Tuesday meetings”.

Deliverables

Export your Eclipse project to a zip file and submit the zip file via web-based turnin. To export a project, go to File | Export… and choose Archive File from the General folder. Check off the entire project in the top left window and make sure the format is .zip and not .tar. Finally, give the archive file a name and click finish.

The name of the turnin project is Homework 6.

LATE PROGRAMS WILL NOT BE ACCEPTED. Programs must be submitted by 5pm on May 5.

Grading

Your program must compile without errors in order to receive any credit. It is suggested that before you submit your program, you should compile it one more time to make sure that it compiles correctly.