Introduction

In this assignment, you will interact with a database through queries using Java Database Connectivity (JDBC) to perform SQL Queries. You will modify a java program (QueryInterface.java) to establish a connection to the database, query the database, and display the results/errors of the query.

You can learn more about how to interact with a database through JDBC in the JDBC trail of the Java tutorial.

You should read the Readme.txt provided with the Oracle^TM software for issues specific to JDBC connectivity with Oracle. This can be found at the path $ORACLE_HOME/jdbc/Readme.txt after you log into bulldog server. You need to set up the environment in order to compile and run JDBC programs for Oracle. Add $ORACLE_HOME/jdbc/lib/classes12.zip to your CLASSPATH and add $ORACLE_HOME/jdbc/lib to your LD_LIBRARY_PATH. This can be done as follows for C shell:

set CLASSPATH=($ORACLE_HOME/jdbc/lib/classes12.zip $CLASSPATH)
set LD_LIBRARY_PATH=($LD_LIBRARY_PATH $ORACLE_HOME/jdbc/lib)

To view the GUI (after running the QueryInterface class) you need to be connected to the bulldog server via a X-Client. You can find a X-Client software installed on the machines in MWAH 177 lab. Connect using the X-Client software to bulldog server by providing your login and password and then execute the java program as follows:

java QueryInterface

The Query Interface Program

The Star Wars Query Interface program provides a framework to perform some interesting queries on the Star Wars database we used in the first assignment. The query interface program provides the framework for your program to interface with the Star Wars database. It contains the skeleton to implement 5 different queries on the database. These queries are as follows:

1. Show all the distinct planets a Star Wars character has visited based on the TIMETABLE table in the Star Wars database.
2. Record the favorite Star Wars character, from amongst given 4 characters, of a user in a table as a poll. You should also display the summary of the poll results.
3. Ask the user to classify the characters according to their affiliations and check whether the user has classified correctly or not.
4. Read in a character and show all the characters who were on the same planet at the same time as the specified character.
5. Ask the user to select a particular character race and planet type and then show the characters of the selected race and the planets they have visited of the selected planet type.

Working of the QueryInterface Program

The Star Wars Query Interface program is a menu driven application for querying the Star Wars database. There are two menus: Query and Connection. The Query Menu contains links to the various queries provided by the interface, and the connection menu provides functionality to connect to and disconnect from a database.

The panels to query the database do not appear unless you connect to the database. After you connect to the database, you can perform various queries on the database by clicking the queries in the Query menu.

What you have to code in the assignment

The Star Wars Query Interface program provides the code for the GUI and handling various error condtions (but not all). You have to modify the code the add the database interaction to the application using JDBC. A simple way to find where you should modify the code is to search for the word EDIT in the source code.

In all you will need to modify 12 functions, which are described as follows:

1. getConnection : method to connect to the database, should open a connection to the
2. disconnect : method to close the current database connection.
3. getCharacters : method to retreive all of the characters in the CHARACTERS table.
4. getVisitedPlanets : method to retreive the distinct planets visited by a particular character from the TIMETABLE table.
5. executeQuery2 : method to record the user's name and his favorite character in a table created by you called VOTES whose script is given below. Allow the user to change his or her vote, but allow only one vote per user.

   create table votes
   (
          username varchar2(20),
          character varchar2(20)
   )
   

6. getPollResults : method to retreive the number of votes each character received from the VOTES table.
7. getRebels : method to get the names of all rebel affiliated characters from the CHARACTERS table.
8. getEmpire : method to get the names of all empire affiliated characters from the CHARACTERS table.
9. getPlanetMates : method to get the names of all characters who were on the same planet at the same time as the selected character from the TIMETABLE table.
10. getPlanetTypes : method to get all distinct planet types from the PLANETS table.
11. getRaces : method to get all distinct character races from the CHARACTERS table.
12. getResults : method to find characters belonging to a certain race and the distinct planets of the specified type that they have been to.

Submission Guidelines

Turn in a paper copy of your code.

Extra Credit (5 points)