Project assignment 2: a web interface to a database

Due Tuesday, March 12th at 6pm in Elena's office

See the submission instructions.

NEW! Intermediate report: On Tuesday, March 5th , before 1pm, please submit a short note about the state of your work: what you have finished, what are you currently working on, you may also mention main difficulties that you have encountered. You can send it to me by e-mail (cc all project members) or as a brief paper note in class.

This assignment specifies the list of features that you must implement, it also makes suggestions about other things that you might want to implement. Each project design is different, your project may require features which are not listed here or it may not need something that's listed as mandatory. If that's the case with your project, please get in touch with me to discuss implementation of your design.

This is one of the key parts of the project. In this part you will need to:

If your project requires other queries, they all need to be implemented in this part of the project. Below are the details of what you need to do.

Technical details

  1. Solutions for parts 1 to 4 must use JDBC to connect to the database.
  2. All data should be entered and modified via SQL queries.
  3. Parts 2-4 should come with a GUI which will allow the user to choose search options or fill forms and will display the results. The GUI must be done using the swing package.
  4. The solution for part 1 may be either an applet or an application. The solutions for parts 2-4 must be applets. In particular, they should be declared as (for instance):
    public class MySearchEngine extends JApplet implements Runnable()
    The reason for this is that the applets will be attached to web pages.
  5. Since your searches are likely to be repeated many times with different parameters, you should use PreparedStatement.
  6. To process an order form you are likely to need several updates and selects (to check the quantity of each product, decrement these quantities if the order can be filled, update the order table, and so on). Use transactions to guarantee that the consistency of data in the database.

Part 0 (or see an alternative in part 1). Creating the database

Create the tables of the database as specified in your database design. The instructions on how to access the database for each project group will be posted shortly.

Alternatively you may create the tables automatically in the program that populates the database (see below).

Part 1. Writing a program to populate the database

While it is unreasonable to expect your database to contain thousands of items of data, it is realistic to store at least a couple of hundred. However, instead of entering data by hand, you should use a program to enter the data from a file (or files). There are also many ways of generating the files efficiently, s.a. copy-paste information from some online source, s.a. a list of players on a US hockey team (make sure that it's public information!), use a random number generator to generate dates of birth or "credit card" numbers, and so on. You can copy-paste lists of products from online catalogues and other sources (but make sure that no copyright is violated).

Besides just saving time, there is another good reason to populate the database by data from files: no database is 100% reliable, and ours is not an exception! Having a program that enters data from files means that you don't need to retype it by hand every time the database crashes or you accidently have changed the information in a way from which it's not easy to recover.

What do you need to do for this program: write a Java application or an applet that inserts data contained in a file or files into the database via JDBC queries. The files may contain parts of information (s.a. a file with just names, a separate file with dates of birth, and so on) or the entire rows of data. Your program does not have to be general: it should just handle your files and your database tables.

When the program is done, use it to populate the database. You may, if you want, create the tables right in the program, or you may use the McKoi Query Tool to create the tables.

Please make sure to keep all of your data files, it's very likely that you will need to reenter the database data at some point.

Part 2. Writing a search engine for the user

If your web site allows the user to search for a product and/or get information about the order which has been already filled, then you need to write an applet or several applets to process the user's request.

The user probably will have a choice between several categories (for instance, Fiction/Non-fiction in an online bookstore), and have several search options (s.a. by author, by title, or by subject). You need to implement these choices in your applet, probably as check buttons (s.a. JCheckButton or JRadioButton) or as a list (s.a. JList). After the user makes a selection, he/she enters the search word in a text field and presses "Return" or clicks on a button. The applet then displays the results of the search.

As an additional feature, you might want to offer to narrow the results of a query based on another selection, which effectively will allow the user to search on the subject and the author (again, using a bookstore example). This feature is not required, however.

Processing request for information about an order is similar, except for you need the user to identify him/herself uniquely by user ID or to enter the order number. Under no condition should your database display order information for more than one user at a time! You might also think of a way of generating order numbers so that they are not sequential integers, because a curious user with an order number 123 might also want to check the order number 124. The order number should be generated by the order processing form (see below).

The order information should display, at least, the shipment date and other related information. You might not want to display the contents of the order for security reasons. Think carefully about other security concerns when processing order status requests. While encryption is not part of this project assignment, you should start making a list of data transfers (between your web site and the user) that you think should be encrypted.

Since all your queries will be very similar, please use PreparedStatement class so that your queries can be precompiled on the database server.

Ideally your search engine should be such that if a query takes too long (which could be due to a busy connection to the database server), the user should be able to stop the query by clicking on a button. This would require that your query runs as a separate thread. You are not required to implement this feature, but it's something worth trying!

Part 3. Processing order and registration forms

The part that allows you to know which products have been selected by the user will be implemented (via shopping cart and cookies) in the next installment of your project. For now just assume that the user has already selected some products. Your program may read the list of products from a file, this makes your implementation easier to test. Note that an applet can read only files which are in the same directory as the applet class!

Processing an order form will require you to do the following:

  1. check customer information, i.e. that the customer has supplied his/her name, address, credit card number, and all these fields make sense (no letters in the credit card number, no digits in a name, and so on).
  2. check product information, i.e. that the order can be filled.
  3. if all the customer information checks out OK and the order can be filled, then you need to enter all the necessary information into the database, generate the order number, and return it to the customer.
All of the above should be done as one or more transactions with a possibility of a rollback if something goes wrong.

You may need to have a special case for a returning customer, where the customer data is already stored in the database. Presumably this option will require a password.

After the order has been succesfully processed, you may display a message on your applet: "Thank you for your order!" or something like this. You may use a label to do it.

Processing a registration form is very similar. Again, please keep in mind security considerations.

Please feel free to add graphics to your GUI, you can display it using labels (see examples in JLabel tutorial. However, your programs will be mostly graded on the functionality, convenience of use, and the quality of code.

This page has been created and is maintained by Elena Machkasova
Comments and suggestions are welcome at

Spring Semester 2002