Project 5 – Database Application - Intro

Academic Integrity: I completed this project without the assistance of others. I understand that cheating, helping others to cheat, or failing to report such actions is dishonest and wrong. Such acts could result in disciplinary action against me.

Project Submission:

• Name your project Project5_JohnDoe (substitute your own name of course)
• Name your application class containing the main() method MulchSimulator so that the TA doesn’t have to look through all the different classes to find it.
• Eclipse
• Include a text file call Student.txt containing your name and email address
• Include the SQL files into the project as text files
• Zip the entire Eclipse project directory.  
• Email your solution in an attached ZIP file to both the instructor and the TA.
• The subject should be
• “Project5 - " + Your First Name + Last Name e.g. Project5 - JohnDoe
• Your file name should be
• “Project5 - " + Your First Name + Last Name + ". ZIP” e.g. Project5 - JohnDoe.pdf
• For full credit, your assignment must be received by 11:59 pm on the due date.

When the TA runs your app, he will:
1    Run the scripts to CREATE the database structures
2    Run the INSERTS to load the data
3    Run your app
4    Run the DROP script to get rid of all of the data and structures

This project requires you to develop a three tiered application:

Backend / Database side / Java data layer

Controller

Front end / GUI

DATABASE SIDE

· mulch_db_create.sql

            All tables, primary keys, foreign keys, and any other constraints

· mulch_db_inserts.sql

            All inserts for data contained in the Mulch Order Data spreadsheet

· mulch_db_drop.sql

            Delete all data and drop all tables.

APPLICATION SIDE

· Follow the directions on the course web site to set up the Java / JDBC / Oracle environment  on your own local machine or on the GL machines.

· Write this application in good object oriented design fashion.

o Create a package named control that will contain classes that control execution of the application. Specifically:

· The main application class to instantiate the application

· Functionality for running all Phases 2 through 4

o Create a package named model

· All the base classes for needed for dispatch and delivery (e.g., truck, mulch, order, etc). 

· You might consider creating specialized report classes for specific reports.

· Report classes should contain base classes that make up the report data

o Create a package named view that will contain classes that will format and display the output. Specifically:

· Classes to format and print the required output

o Create a package named data that will contain classes that access the database. Specifically:

· A Database class that will encapsulate all the generic JDBC functions of communicating with the database. 

· See below

· A MulchDatabase class that inherits from Database. It that will translate between the result sets returned by JDBC into mulch objects and report objects

· Create a Database class in db that will encapsulate all the generic functions of working with the database. 

• There should be class attributes for the JDBC objects

o userLogin

o userPasswd

o url

o connection

o statement

o result set

• Constructors

o Primary constructor should take the following arguments

§ Oracle instance => a value of “gl” or “local”

· Set url attribute appropriately

§ userLogin for that instance of Oracle

§ userPasswd for that instance of Oracle

• No-arg constructor should set string attributes to empty strings
• There should be methods to:
• connect()
• Connect to Oracle on the specified machine
• execSQL(String sqlStatement)
• Should take in a fully formed SQL statement as a string
• Execute it
• Initialize the result set object
• closeSQL()
• Call the JDBC statement set close() method
• Set statement attribute back to null
• getResults()
• Return the ResultSet
• closeResults()
• Call the JDBC result set close() method
• Set result set attribute back to null
• printResults()
• Print the result set using meta data
• disconnect()
• Make sure result set and statement have been closed
• Call the JDBC connection close() method
• Set connection attribute back to null
• processError (SQLException ex)
• See below
• All Database methods should catch SQLException and do the following
• Call processError()

public void processError(SQLException ex) {

       System.err.println("SQLException information");

       while(ex!=null) {

System.err.println ("Error msg: " + ex.getMessage());

             System.err.println ("SQLSTATE: " + ex.getSQLState());

             System.err.println ("Error code: " + ex.getErrorCode());

ex.printStackTrace();

 // For drivers that support chained exceptions

             ex = ex.getNextException();

      } // end while

System.exit(1);

}   // end processError ()