CS 430/630 Homework 4 Intro to JDBC

Homework 4 due Monday midnight Nov. 27. Note that topcat and pe07 have different versions of Java, so you need to recompile .java files when switching from pe07 (Java v8) to topcat (Java v7), though not the other way around because of upwards compatibility.

 1. Run JdbcCheckup.java (in the jdbc directory of our home page) using your Oracle account while logged in on topcat or pe07 and also from your development system. Specifically, copy the jdbc directory from /data/htdocs/cs630 to your cs630 homework directory:

cp -r /data/htdocs/cs630/jdbc cs630          (assuming your current directory is your login directory on topcat or pe07 or even users, all the same directory)

That will give you the needed driver jar files as well as JdbcCheckup.java itself. Don't forget you need the driver jar file on the command line when you run the program, as detailed in the header comment in JdbcCheckup.java. Put the script of the topcat (or pe07) run in hw4/JdbcCheckup_topcat.txt in your cs630/hw4 directory. To make a script, run "script", then run JdbcCheckup, then exit and rename typescript to JdbcCheckup_topcat.script.  Optionally, run it using mysql on pe07 as well. Note that you can reach mysql on pe07 from topcat as well as pe07.

Transfer the whole jdbc directory to your development system. Note that you will need a tunnel to access Oracle from your dev system. See DatabaseSetup. Compile and run JdbcCheckup using the command line. Put the text of your dev system run in JdbcCheckup_devsys.txt and transfer it back to your cs630/hw4 directory. If you have decided to do the program development on topcat or pe07 rather than on your own development system, say so in a README file and explain how you will be editing files for use on topcat or pe07. In this case, your "topcat" script will be enough. Optionally, run JdbcCheckup to mysql on pe07.

Once you have JdbcCheckup working on the command line, you could try running it an IDE (DrJava, netbeans, eclipse, etc.) if that is how you develop Java programs. Note that each IDE has some way to add jar files (i.e. libraries) to a project. If you don't want to use an IDE, that's fine too. In that case use a text editor as described in DevelopmentSetup.

2. Consider the flights-aircraft-employees-certified database of our createdb.sql. Let's assume these employees are pilots. The times (using SQL datatype timestamp) are assumed to all be using one timezone, so we don't have to worry about timezones. Read about the timestamp datatype for Oracle on pp. 531-545 of Murach, and, optionally, here for MySQL. FYI: Oracle can handle timezones, so we could handle data that specified various timezones, as shown on pg. 549 of Murach.

Write a small Java program FindFlights that accepts database credentials like JdbcCheckup, and then accepts two cities (Los-Angeles and Chicago for example) and finds the flights from the first to the second in the flights table. For the selected flights, print out the flight number, distance, and duration of the flight, computed by time difference (in hours:min or days:hours:mon). Note that one of the time differences is negative: just report it that way. Note: don't use LIKE where equals (=) can be used to match the city name. Equals is much faster in general.

Provide a typescript FindFlights.txt showing FindFlights reports on Los-Angeles to Honolulu, Los-Angeles to Sydney, and Madison to Chicago (3 runs of the program).

3. For FindPilot.java, which also accepts origin and destination cities, use the first flight found as in problem 2., and find a pilot (employee) for it, and an airplane, such that the pilot is certified for the aircraft, and the aircraft has cruisingrange above the distance of the flight. Find the lowest-salary such pilot by using order by and then using the first row returned. We are assuming any employee and any aircraft can be at the origin airport without delay. Print out the origin, destination, and distance of the flight, the pilot's name and salary and the aircraft's model and cruisingrange. Use only one additional query in this program over the one in FindFlights. Sample output:

Please enter information for connection to the database
Using Oracle (o) or MySql (m)? o
Oracle username: xxxx
Oracle password: xxxx
host: localhost
port (often 1521): 1521
sid (site id): dbs3
origin city: Madison
destination city: Chicago
using connection string: jdbc:oracle:thin:@localhost:1521:dbs3
Connecting to the database...using connection string: jdbc:oracle:thin:@localhost:1521:dbs3
Connecting to the database...connected.

Flight: from Madison to Chicago, distance 84
Pilot chosen: Milo Brooks, salary 20
Aircraft chosen: Piper Archer III, cruisingrange 520

Note that this salary value is probably a data error! Luckily Milo is not certified for larger airplanes or he would be chosen in every case.

Note: If all this code is in main, the code "needs procedurization".  To procedurize, set up one method (static method is fine) for finding the flight and another for finding the pilot. It turns out that all that is needed from the first method is the distance, so return that as the method return value and then provide it to the second method. Both need the origin and destination, so establish those in main.

Provide a typescript FindPilot.txt showing FindPilot reports on Los-Angeles to Honolulu, Los-Angeles to Sydney, and Madison to Chicago (3 runs of the program).

4. Database rows and Java objects. Study the emaillist example (partly from Murach's Java Servlets and JSP book) available in the emaillist subdirectory of the class home. See its README. This example uses a User object to hold information on one user, and holds the information on emaillist users in a database table userdb. The provided program EmailList.java accepts new emaillist users for the database. Write a Java program EditUser.java to allow changes to information on one emaillist user. EditUser should accept database credentials and connect to the database as EmalList does, then additionally request a emailList user email via readEntry, then call UserDB.selectUser to get that user's User object. Then output the firstname, ask for changed firstname or nullstring for no change, and similarly for lastname. Then use UserDB calls to change the database to reflect the edit. Use delete and insert or provide an update method and use that. Provide a typescript EmailList.txt that shows execution of dropuserdb.sql, createuserdb.sql, then a run of EmailList like that in README, followed by a run of EditUser editing information on swong@gmail.com, specifically changing Sue to Susan.

Files to deliver in cs630/hw4: (the .class and .jar driver files are also expected. Make sure it runs on topcat or pe07 with Oracle, optionally also with mysql)

README: explanations to grader.

1. JdbcCheckup_topcat.txt, JdbcCheckup_devsys.txt (Please use _topcat.txt even if you ran it on pe07)
2. FindFlights.java, FindFlights.txt
3. FindPilot.java, FindPilot.txt
4. files from emaillist directory except its README, plus EditUser.java, EmailList.txt