ICS 184 / EECS 116 Spring 2006
Assignment #6 (extra credit)
Due date and time: Sunday, September 3, 11:59pm
Introduction
Up to this point in the quarter, we've focused our energies on learning about database management systems as singular software systems. Our interaction with databases has been direct: issuing SQL statements directly to the database server and receiving results, using the mysql command-line client. To us, the database has stood alone thus far.
Directly issuing SQL statements is one important use of a database, but most practical uses of databases are in larger applications, with the database playing the role of storing and finding data, while other aspects of the application, such as the user interface and business rules, are handled elsewhere. For example, a client-server application is one in which a program written in a "conventional" programming language such as Java provides a user interface that allows users to view and manipulate data in the database in a natural way, rather than being required to write SQL statements. Another example is a web-based application, in which a specialized web server generates web pages dynamically using information stored in a database, then serves them up to browsers in response to web requests.
The tricky part of connecting to a database from an application is that information needs to be sent between the application and the database. Since the database runs outside of the application, this isn't just a matter of calling some predefined method or function; binary data needs to be sent between the application and the database. This requires an agreement about the format of that data — how does the application send an SQL statement and how does it interpret the result? These details can be cumbersome, so it's wise for them to be hidden beneath a simpler interface whenever possible.
Java hides these details beneath an API called JDBC, which allows Java programs to talk to various database servers in a relatively portable way, by providing a set of interfaces to objects like database connections, SQL statements, and results. The interfaces are implemented by JDBC drivers, which handle the specific details about how to connect to a particular database server, taking this burden off of Java programmers' shoulders. A Java program can then connect to a database without any intimate knowledge of how the connection is to be managed; in many cases, the Java program need not even be aware of what kind of database server it's connecting to!
In this assignment, I'd like you to engage in a brief exploration of client-server database applications, by writing a Java program that uses JDBC to connect to a MySQL database and performs a variety of operations on it. In lecture, we went through a demonstration of how to use the JDBC API; completed and commented versions of the code examples from the in-lecture demonstration are available at this link. Be sure you've read and understand the code examples before proceeding with this assignment. You'll also likely need to refer to the Java 5 API documentation as you work, as you will inevitably discover that you need to find details that weren't covered in lecture.
Downloading and installing the JDBC driver for MySQL
Downloading and extracting Connector/J
The JDBC driver for MySQL is called Connector/J, the latest version of which (3.1.13) is provided at this link. There are two different downloads available at this link; they are the same files archived in two different formats (.zip and .tar.gz). If you've never heard of tar.gz before, it's probably best to download the zip version. Contained within the archive is the JAR that comprises the driver, along with documentation, and the driver's complete source code.
Extract the archive to a folder of your choosing. I'll assume in the rest of this section that you're running Windows and that you extracted it to the folder C:\MySQLConnectorJ.
What's a JAR?
There are two ways to distribute compiled Java code: as a set of .class files, or in a JAR (Java ARchive). A JAR is actually nothing special; it's a bunch of .class files (and whatever else you want to include) stored in Zip format, but with the file extension .jar. The Java Virtual Machine knows how to open up JARs and find classes within them for you automatically, provided that you tell it which JARs to open, which makes JARs a nice way to distribute a set of classes that implements some functionality. (There are even ways to make JARs "executable," so that you can double-click them to run programs stored inside. That functionality isn't important in this assignment, though.)
Adjusting your CLASSPATH to include Connector/J
Connector/J is provided as a JAR, but the Java Virtual Machine won't know to look inside the JAR for its classes unless you tell it to. Just as you have to tell Java which folders it should look in when it tries to load a Java class, you also have to specifically tell it which JARs to open, as well. Before you'll be able to use the driver in one of your Java programs, you'll need to add Connector/J to your CLASSPATH environment variable. Add this entry to your CLASSPATH:
C:\MySQLConnectorJ\mysql-connector-java-3.1.13-bin.jar
(If you installed the driver somewhere other than where I suggested, be sure to specify your installation folder instead of mine.)
If you're not sure what it means to add an entry to your CLASSPATH environment variable, or if you haven't installed Java on your computer at all, read through my document that explains how to install and configure Java on your computer.
Creating the database
Your program will need to connect to the database that manages information about the hypothetical car dealership corporation described in Assignment #1 and Assignment #2. (All of the tables are included, though you may find that you don't need to use all of them in this assignment.) Before the program can connect to it, of course, the database will need to exist. As is customary in programs like this one, the program is not in charge of creating the database from scratch; it is instead assumed to exist when the program is run for the first time. That means it's necessary to start by creating the database. It's also important to set up a user account for your program to use when connecting to it. Below is a description of the steps you should take to set up your database.
Step 1: Creating a user account for your program
It's often a good idea for an application to have its own special user account for connecting to a database; this allows the database administrator to carefully control the set of privileges that the application will have available to it, which might be different than the privileges that should be given to other applications or human users.
From our perspective in this course, it's also good for us to agree on a username and password for your program to use, so that we'll be able to grade your programs by connecting them to our test database. For this reason, I'm requiring you to use the following login information when connecting to the database from your program:
Username: ics184jdbc
Password: password
To create this user account, start up the mysql command-line client, logging in with your root account, then issue the following command:
CREATE USER ics184jdbc IDENTIFIED BY 'password';
Now, quit the mysql command-line client and you've got your user account.
Step 2: Creating the database
I'm providing an SQL script that creates the version of the automobile database that you'll need for this assignment. It also grants full privileges to the ics184jdbc user account that we created in Step 1. This is critical, since your program will be logging into the database as ics184jdbc and will need sufficient privileges to do so.
Since the ics184jdbc account won't have sufficient privileges to create a new database, you'll need to execute this script using your root user account, as you did in previous assignments.
I haven't included any test data in my SQL script, though you'll probably want to include some, so that you can test your program. A good solution to this problem is to reuse the test data you developed for Assignment #2.
The program
For this assignment, you're required to write a Java program that uses JDBC to connect to the database created in the previous section. It should provide a console-mode user interface that allows the user to perform the following operations:
Do not use stored procedures or functions to implement this functionality; the program should issue SQL statements on its own. You may assume that the program has complete control over the database; don't worry about the possibility of concurrent transactions causing problems.
In the event of an unexpected SQLException (such as one caused by an inability to connect to the database), it's okay for your program to print a stack trace and terminate, but you should perform error-checking to prevent crashes whenever possible. For example, when adding a car to a dealership's inventory, if the specified dealership ID refers to a non-existent dealership, the program should print an error message, rather than crashing.
Deliverables
You must submit all of the .java files that comprise your program. Please do not turn in the .class files, or other files generated by your development environment. Also, it's not necessary (or even desirable) to submit your SQL script, since we'll be using our own script to create a database for use in testing your program.
Follow this link for a discussion of how to submit your assignment. Be aware that I'll be holding you to all of the rules specified in that document, including the one that says that you're responsible for submitting the version of the assignment that you want graded. We won't regrade your assignment simply because you submitted the wrong version by accident.