SIMS 255: Assignment 9

Work alone or with proper pair programming practices.

Turn in assignment as both a hardcopy in class and a zipped file, using this link to cardea. Please have all your code as runnable .java files.

Introduction

The purpose of this assignment is to give you some practice writing java code that issues queries to a DBMS (database management system) and processes the results.

Using MySQL

For this assignment, we will be using mysql, an open-source database management system package. To get started, read the online tutorial.

For reference purposes, there is also detailed online documentation

Kevin has created a MySQL database for everyone in the class who didn't already have one. He based this on the class mailing list, so if there is anyone in the class who is not on the mailing list, they don't have a MySQL database. He created the databases using the UNIX/NT username for the name of the database as well as both the username and password for accessing the database. We use dream as our MySQL server. For example, Allison B. would use the following to connect to her database:

hostname: dream.sims.berkeley.edu
database: allisonb
username: allisonb
password: allisonb

Each database has already been set up with a single table (called 'mytable') with two columns and two rows of data.

You can get familiar with interacting with SQL and mysql by calling it from the Unix command line and issuing queries, as shown in lecture. There is a nice feature -- if you type contol-P, it brings up the previous command that you typed and lets you edit it. Multiple control-p's move farther back in your query history.

As seen in class, it is very simple to access a database using java via the JDBC API. Use the code provided in class, called TestDB.java, to get started, and the jar file that I'm calling mysql.jar. For more details on how to set this up, see Lab 14.

Application using a Database

1. Use java to create tables representing classes and class schedules, and to access information from the schedule table, perhaps putting it into objects of class Schedule that you used in earlier assignments. One idea would be to use a sorting algorithm to print out students' schedules according to different criteria. (You can do a variation on this suggestion if you like.)

2. Use web pages to create queries to a database. (This option requires that you learn a bunch of material on your own.) Our system administrators have set up a facility that allows students to make use of JSP (java server pages), which allows you to write java code that communicates with a web server. Use JSP and web forms to query the contents of a database and send the results to a web page. You must write java code (in the form of beans) to do this version. To learn more about JSP, go to my.sims.berkeley.edu and look under the navigation bar under "Web". Once you have an account, clicking on the link shows you documentation for how to use JSP.)

3. Consider the original goals of your metadata project in IS202. Write java code to put your data into a database and then access it in a way that meets those goals.

4. Other. Use java to create and use a database of information to do something interesting.

Note that you should not simply use the supplied code as is, and type in database commands at the command line. The provided code is meant to show you how to make the connection, and how to use the ResultSet and ResultSetMetaData to do other things.

Instead, your program should have code that creates queries directly (rather than taking them from the command line). It should also take the results of queries and put them into data structures of some kind, and then do something with those data structures.