In this assignment, we'll build a very simple database (using JDBC) for a (very small!) Video Rental store. We'll create a Java application which will allow you to "rent" movies to customers, and to report on current rentals.
We'll be creating a "Video Store" database, using JDBC, which contains the following tables:
There is one row in the
There will be one row in the
There is one row in the
What To Do
Here are the steps you should follow to complete this assignment.
Obtain a JDBC Driver and Database
Choose a JDBC driver/database to use. Click here for a list of possible JDBC drivers to use, together with links to their sources. Note that it will pay you to choose a driver/database that is not too difficult to learn, so I suggest you do some research to identify the best one for your needs. Another thing to keep in mind is that some drivers come with their associated database, and some do not. For example, the Oracle JDBC driver is freely downloadable from the Oracle web site, but you can only use it if you also have an appropriate Oracle database installed on your system (or available to you via a client/server connection to another system).
Install the driver/database on your system.
Learn How to Use Your Selected Driver/Database
Whatever your choice of driver/database, you will need to learn how to create a database. Each database has its own conventions for creating a database, and you will have to do the appropriate reading in that database's documentation to learn how.
If you decide to use the JDBC/ODBC Bridge Driver (which is the most easily available since it comes with the JDK, but which is not easy to use because you have to learn how to configure ODBC), you will have to learn how to do the following:
Regardless of which database you choose, you will also have to learn the fully-qualified class name for your selected driver.
Create and Populate Your Video Store Database
Using your chosen JDBC driver/database, create the database. Then create the tables within that database, and populate those tables with appropriate sets of data. There should be:
You can, if you wish, use the
Verify Your Database Contents
Once your database has been created and populated, you
should verify its contents. One convenient way of doing this
is to use the
Write Your Video Store Application
We'll be writing a Java application to emulate a simple video store's operations.
Note: Don't try to use an applet for JDBC; it's more trouble than we have time for in this course!
Actually, I've written the application for you; all you have to do is plug in the proper JDBC code and database queries to finish the job. You can download the application, minus the real contents of its data access class. (See later.)
I expect to see at least one (preferably more than one)
Here's how the application operates:
Renting Movies to a Customer
When you run my VideoStore application, it comes up looking like:
When the program starts up, it makes a connection to the database using JDBC.
The layout consists of a top panel, a middle panel, and a bottom panel:
The top panel is already set up to "Rent Movies to Customer", and the middle panel presents a list of customers from which you can select. When the Select Customer page is displayed, the program populates the list with all the video store's customers, from the Customers table.
You select a customer from the list, and then click on the Next button, which brings up the following:
Here, you get to select the movie (or movies) that you rent to that customer. You select one of the movies on the left, and then click on the Add button to add that movie to the right, which is the customer's "shopping bag". Since there are multiple copies of a movie in stock at any given time, a movie is not removed from the left when it is added to the right.
Similarly, when you select one of the movies that have already been added to the right, and click on the Remove button, that movie is removed from the right.
If you decide not to rent any movies after all, you can click on the Back button, which returns you to the Select Customer page. From there, you can select another customer if you wish.
Once all the movies the customer wishes to rent have been selected and added to the right, you click the Finish button, which inserts the necessary entries into the Rentals table (one entry for each movie rented to the customer), and then the program returns to the "Select Customer" page:
Adding a New Customer
If you click on the Add New Customer radio button, you will see the following:
Notice that the application generates an ID for you; that field is disabled, so you can't change it. You can now fill in the other fields, and then click on the Add button, at which point the program inserts a corresponding row into the Customers table.
Note that the all the fields are required, except for the ZIP and Phone. The ID must be an integer, and must not conflict with any existing customer ID. The program checks that the ID is an integer, that all the required fields are present and that the new customer's ID is unique, before the new row is inserted into the Customers table.
Once the customer has been added, the ID field's value is incremented by one, and the other fields are cleared out, and the program remains on the same page so that you can enter another new customer.
Editing or Deleting a Customer
If you select the "Edit or Delete Customer" radio button, the page changes to allow you to select a customer:
You select a customer, and click on the Next key, and the screen changes to look like this:
The program reads the data for the selected customer (based on the customer ID), and fills out the fields in the form. Note that the ID is in a field that is disabled, which means that it cannot be changed (the text in it also looks gray, instead of black)
At this point you have three choices:
Listing Rentals for a Customer
If you click on the "List Rentals for Customer" radio button, the window looks like:
Select the customer you are going to list rentals for, and click on Next, and you will see:
The program has queried the Rentals and Tapes tables (a join operation) to find all the movies that this customer currently has rented out. Note that it displays the movie ID, its title, when the movie was rented, and when it is due back.
You can use the Back button to return to the Select Customer page, or you can select another operation.
Listing all Movies Currently Rented Out
The final operation is to list all movies that are currently rented out, and by whom. Click on the "List All Rentals" radio button, and you will see:
In this case, the program has performed a 3-way join operation among the Rentals, Customers and Tapes tables.
Clearly, in any real video store, there also has to be an operation to return movies into inventory when a customer drops them off.
I haven't implemented this, yet -- I got tired!
Note: If I didn't do it, I don't expect you to, either -- there's plenty of other stuff to do in this program already! Remember, this is an exercise in learning how to use JDBC, not a real application.
Tips and Pointers from my Implementation
Here are some (hopefully helpful) pointers, tips and suggestions that I have learned from writing my version of the above:
Here's where you download the application.
The sources for the Video Store application are packaged in a JAR file. Download it, and
"unjar" it into a convenient source
directory, maintaining the directory hierarchy. You can use the jar program provided with the Java
Standard Edition, or you can use a utility such as WinZIP.
Once you've extracted the sources, and compiled them, the application
should run immediately (
Change one file only --
|The page was last updated February 19, 2008|