• Introduction
  • Baseline
  • Resources
• Design
  • About the Problem
  • Diagram
  • Design Notes
• Source
• Demo

In addition to the baseline homework, you must include one or more complex elements(how much depends on your own background and whether you're in a group):

• You can have more complex tables and relations between the tables. This will significantly increase the complexity of the code to change information.
  
  
• You can write a Java program to load the data into the tables. It can be modelled after(but significantly different) than the example in the coreJava book. It should read data from files and insert it into the tables using JDBC.
  
  
• Write and install PL/SQL procedures into the database. Then use JDBC to execute the stored procedures.
  
  
• Your database may have complex access patterns or user interfaces.
  
  

Pizza Order Database is a sample application that demonstrates how JDBC Two-Tier Applet works. The program consists of two distinct modules: One is the Client Applet module -- OrderForm, which offers an interface for customers to query on ordering information; The second module is the Database Access backend -- ControlPanel, which handles queries from customers, performs database access through JDBC and displays results to customers.

OrderForm applet is used at the counter for taking pizza delivery orders. Customers usually call up the pizza shop and order the pizza over the phone. This design is meant for helping the person taking the orders. Thus the first section of the GUI deals with the details of the person calling in, like the phone number, address, etc,. Then the different varities of pizzas are listed from which customers can choose either a ready made pizza or a custom made pizza. Finally the three buttons at the bottom are used to set up connection to Database, clear current input data in order form and close connection to Database.

ControlPanel is used as a bridge between client applet interface and database access. It monitors the state of buttons. Whenever customers press SEARCH, INSERT, UPDATE and DELETE buttons, it collects information from OrderForm and then sends queries through methods of myDatabase.class to access database. Query results are processed by reformating their display. Finally, the results are show in TextArea through methods of DisplayPanel.

myDatabase implements methods to access the Oracle database via JDBC. The API includes open and close database, executeSQL for executing query commands and getRecord for returning multiple records that matches the query. Vector object is used to hold multiple Hashtables as elements, where each record is held by a hashtable. As each query is packaged into a string object that deliverd to execution method. so all of the database access methods in ControlPanel take executeSQL and getRecord as the underline methods. And it is very flexible to expand the functions of query based on this scheme.

I use oracle.jdbc.driver.OracleDriver to connect to the Oracle database on Carver. The database access of my program is actually done all on client side using the above JDBC driver and the driver does the communication with the remote database. This is the reason we call the program a JDBC Two-Tier Applet module.

Back to Top Page


Diagram




Back to Top Page


Design Notes

The applet uses four main classes for building the GUI. The main applet class is the PizzaApplet class which uses BorderLayout scheme to put a BannerPage component on top of the page and an OrderForm component below it. The BannerPage class is extended from Canvas and it just displays the banner on top. The OrderForm component is the main class which has all the other components that adds functionalities to the applet. It uses the GridBagLayout and GribBagConstraints classes for placing the components on the screen appropriately. Several components like Textfields, Buttons, Checkboxes are placed appropriately on the Panel. It uses the addComp method in the OrderForm class to actually place the components and uses the Insets class to give appropriate distances from the borders of the components.

Two custom components that are placed on the Panel are the lists built using the IngredientList class which itself extends from the List class. It has two constructors, one which builds an empty list and another one which takes in an array of elements and builds the list. There are other methods to add elements in a sorted order to the list etc. There are two list components placed on the Panel, one for the initial Ingredients and another one listing the final Ingredients in the list. The two buttons with right and left arrows are used for putting the components back and forth between these two lists. Initially when no pizzas are selected then these buttons are inactive.

Apart from placing these components, the OrderForm class implements three Listener Interfaces, namely the ActionListener for trapping the events generated by the buttons, the TextListener for trapping the events generated by the Textfields and the ItemListener for trapping events from the checkbox selections. The various methods implemented for these interfaces use the getSource method to determine the actual origin of the event to decide on which button or check box was actually clicked.

The check boxes for selecting the pizza and the list boxes for choosing the ingredients for the pizza work together. For eg., if the customer chooses a veggie pizza by selecting the corresponding checkbox, then a list of all the vegetables present in this pizza are displayed in the final ingredients list on the right. From this list components can be removed or added using the right and left arrow buttons. If the make your own pizza is selected, a list of available ingredients are listed on the left from which the required components can be selected and put into the list on the right using the right arrow key.

There are other methods defined in the OrderForm class for clearing the filed and displaying the default values, for displaying the output of the print button.(Note this uses a simple drawString and prints the output on the applet screen itself. A better display of output could be added instead.) Several error checking methods are also present in this class to check if values greater than what the Textfields could accept are given. Error messages are again displyed at the bottom of the applet in the same way as the output. Error messages are also displayed if certain fields are left empty. Only the City field is optional. All the other fields are necessary. The pay by field has a default value of Cash. If any of the other fields are not selected, then an error message will be printed upon clicking the Print button. Cancel button clears all the textfields and displays the default values for the check boxes and the list elements.

ControlPanel is used as a bridge between client applet interface and database access. It monitors the state of buttons. Whenever customers press SEARCH, INSERT, UPDATE and DELETE buttons, it will collects information from OrderForm and then sends queries through methods of myDatabase.class to access database.

The myDatabase class implements two important methods executeSQL and getRecord, apart from open and close database methods. All queries are done by calling them througn upper level API searchDB(), insertDB(), updateRecord() and deleteRecord() in ControlPanel class.

Back to Top Page


Source

• Source Code :
  PizzaApplet.java
  BannerPage.java
  OrderForm.java
  DisplayPanel.java
  ControlPanel.java
  myDatabase.java
  table.sql
  
• Demo :
  JDBC Two-Tier Applet