QMCS 281

Pat’s Potted Plants

Fifth Assignment

 

In this assignment we’ll add database functionality to Pat’s Potted Plants. The database is called Pats.mdb and is an Access database and was created with Access 2000. This database will have to be registered as an ODBC database. All UST lab machines should allow this capability.

 

The first step is to create a class called “PatsDatabase”. It will have Statement, ResultSet, and Connection objects as its class variables. The following methods should be written:

 

-- A constructor. This will accept nothing and load the JDBC driver, create a Connection object, and create a Statement object.

 

-- A getData method. This will accept a String for the table name and return a ResultSet containing all the fields and all the records for that table.

 

-- An addVendor method to insert Vendor object data into the database.

 

-- An addCustomer method to insert Customer object data into the database.

 

-- An addInventory method to insert Plant object data into the database.

 

-- An addOrder method to insert Order object data into the database. Note that this method should accept a String for the date and the date should be formatted as month/day/year. The following code will do that for you.

 

                        DateFormat form = DateFormat.getDateInstance(3);

                String s = form.format(date);

 

Since we will be “percolating” exceptions up to the user interface class, you don’t need any try…catch blocks in this class. You will, however, need to add the phrase “throws Exception” to every method. Each of the addXXX methods will need to accept the appropriate data needed to add to the respective table – e.g., the addVendor method needs to accept a String for the vendor name, a String for the vendor address, and an int for the average days for delivery.  Here’s example code for the addVendor method:

 

            public int addVendor(String name, String address, int delivery) throws Exception

        {

                String sql = "insert into vendor values('"

                        + name + "','" + address + "'," + delivery

                        + ")";

                int number = statement.executeUpdate(sql);

                return number;

 

            }

 

Please, oh please, don’t try to write the entire PatsDatabase class all at once. Write a little program to test the database methods as you write them. This program (called something like TestDatabase) should have a main method (with the “throws Exception” phrase) that instantiates a PatsDatabase object and then tries the methods. You can check against the Access database to see if the items have been added correctly.

 

Once you believe that your PatsDatabase class is working correctly, you can then attack the main system.

 

You will need to change the TestPat class as follows:

 

-- Add the phrase “throws Exception” to every method except the main method.

 

-- In the main method have the call to the constructor in a try block with a catch block following. The catch block should print out the exception’s message and the stack trace, then exit the system.

 

The classes Vendors, Inventory, and Customers all need to be changed in a similar manner as follows:

 

-- import java.sql.* should be added to each class.

 

-- Put the phrase “throws Exception” on the constructor and the addXXX methods.

 

-- The constructor should be changed to use PatsDatabase to get the data for its objects. Here’s the sample code for the Vendors class:

 

   public Vendors() throws Exception

  {

                  vendors = new Vector();

                  PatsDatabase pdb = new PatsDatabase();

                  ResultSet rs = pdb.getData("vendor");

                  while (rs.next())

                  {

                          String name = rs.getString(1);

                          String location = rs.getString(2);

                          int days = Integer.parseInt(rs.getString(3));

                          Vendor vend = new Vendor(name,location,days);

                          vendors.addElement(vend);

                  }

                  pdb.close();

  }

 

-- The addXXX method should be changed to also add the data to the database. Again, here is sample code for the Vendors class:

 

   public void addVendor(String name, String location, int days)

          throws Exception

  {

          vendors.addElement(new Vendor(name,location,days));

          PatsDatabase pdb = new PatsDatabase();

          pdb.addVendor(name, location, days);

          pdb.close();

  }

 

 

Again, change one collection class at a time and run your TestPat program to make sure the changes were done properly. You might want to add print statements to print the collection class during testing. Once you’ve changed these classes, you can attack the Orders and Order classes.

 

The Order class needs to be changed as follows:

 

-- Add an overloaded constructor that will accept a Customer object, a Plant object, and a Date object.

 

-- Add a method called getDateString that will return the date as a String as shown above.

 

The Orders class needs to be changed as follows:

 

-- import java.sql.* into the class

 

-- The constructor needs to be changed similarly to all the other collection class constructors – i.e., get the data for Order objects from the database. Note that you should use the getDate method for the ResultSet when getting the field containing the date for the Order.

 

-- The addOrder method needs to be changed to insert the new data into the database. Again, this is similar to the other collection classes.

 

That should be it!