AITP Regional Conference

AITP National Collegiate Conference Java Problem Statement April 8, 2005 7:00 – 11:00 PM You are being asked to write a purchase order retrieval program. You will be given a MySQL database, with two tables, pohdr (Purchase Order Header) and podtl (Purchase Order Detail). You may open them with a user ID of “root” and no password. See the database table descriptions presented below. In addition, you will be given three interfaces for you to implement with the classes shown in the picture below; as well as, an Address Javabean. You may use Notepad, TextPad, Word, Eclipse, NetBeans, or J-Builder, whichever you prefer. The package org.aitp.competitor should be used. If using an IDE, create a Java project called Teamnn, where nn is your team number; otherwise, all work should be done in a directory group headed by a directory called Teamnn. You must create a text file called Team.properties in the classes directory. The contents of that text file should be “Team=nn” (without the quotes), where nn is your team number. See folder structure presented below. The objectives of this problem are to 1. Create a simple GUI object to accept a Purchase Order Number. (PurchaseOrderUI class). The single purchase order in the provided file is number 200401. 2. Retrieve the corresponding Purchase Order Header and Purchase Order Detail records from the MySQL database using the Purchase Order Number. (PurchaseOrderDataAccess class) 3. Store the Purchase Order Header and Purchase Order Detail records in a Purchase Order Javabean. (PO class) 4. Create an XML document called po.xml built from the information within the Purchase Order Javabean. When creating the XML document, use the java.util.ResourceBundle API to retrieve your team number from the properties file and add it as an attribute to the PO element of the XML document. (PurchaseOrderXMLSerialization class) For your program to be considered, your program should compile, execute, and produce output. Only functional programs will then be judged further. Regardless of whether you are able to implement everything requested or not, make sure you can submit a working program at the end of the contest period. See the attached Scoring Model. Page 1 of 6 AITP National Collegiate Conference Java Problem Statement Components Provided for You       Two MySQL Database tables: pohdr and podtl Table data schemas XML document structure XML document builder pseudo-code Three interfaces to be implemented An Address Javabean. April 8, 2005 7:00 – 11:00 PM Components to be Completed by You  PO and PODtl Javabeans  PurchaseOrder  PurchaseOrderUI  PurchaseOrderDataAccess  PurchaseOrderXMLSerialization  Team.properties file  Po.xml document. Page 2 of 6 AITP National Collegiate Conference Java Problem Statement Table Data Schemas pohdr (Purchase Order Header) April 8, 2005 7:00 – 11:00 PM podtl (Purchase Order Detail line) PONbr int(9) unsigned NOT NULL auto_increment, BlgNam varchar(30) NOT NULL DEFAULT '' , BlgLn1 varchar(30) NOT NULL DEFAULT '' , BlgLn2 varchar(30) , BlgCty varchar(30) NOT NULL DEFAULT '' , BlgSt char(2) NOT NULL DEFAULT '' , BlgZip varchar(9) NOT NULL DEFAULT '' , ShpNam varchar(30) , ShpLn1 varchar(30) , ShpLn2 varchar(30) , ShpCty varchar(30) , ShpSt char(2) , ShpZip varchar(9) , OrdDte date , ReqDte date , ShpDte date , ItmTtl decimal(9,2) NOT NULL DEFAULT '0.00' , SlsTax decimal(9,2) NOT NULL DEFAULT '0.00' , ShpAmt decimal(9,2) NOT NULL DEFAULT '0.00' , PONbr int(9) unsigned NOT NULL DEFAULT '0' , POLin tinyint(3) unsigned NOT NULL auto_increment, ItmNbr varchar(10) NOT NULL DEFAULT '' , ItmPrc decimal(4,2) NOT NULL DEFAULT '0.00' , ItmWgt decimal(5,2) NOT NULL DEFAULT '0.00' , OrdAmt int(5) NOT NULL DEFAULT '0' , ShpAmt int(5) DEFAULT '0' , BOAmt int(5) unsigned DEFAULT '0' , PRIMARY KEY (PONbr,POLin) PRIMARY KEY (PONbr), Page 3 of 6 AITP National Collegiate Conference Java Problem Statement Purchase Order XML Structure April 8, 2005 7:00 – 11:00 PM Here is the format of the XML document using “po” as the entity being represented: "blgNam" "line1" "line2" "city" "state" "zip" "shpNam" "line1" "line2" "city" "state" "zip" "ordDte" "reqDte" "shpDte" "itmTtl" "slsTax" "shpAmt" "itmNbr" "itmPrc" "ordAmt" "shpAmt" "boAmt" (poLin should be repeated for each purchase order line.) XML Builder Pseudo-code Page 4 of 6 AITP National Collegiate Conference Java Problem Statement April 8, 2005 7:00 – 11:00 PM Instantiate an Element for your root element (PO). For each element of the document (and field in the MySQL table), Instantiate an Element. AppendChild the value to the element. If it has an attribute, Instantiate an Attribute; add it to its Element. Append the Element to its parent. Finally, appendChild the Purchase Order element to the root. Instantiate a Document with the root Element. Instantiate a Serializer object to output your XML document to a file called po.xml, which is the required deliverable. Diagram of your Java program PurchaseOrder Team. properties po poNo poNo po PurchaseOrderUI PurchaseOrder DataAccess PurchaseOrderXML Serialization (XOM or JDOM) (Swing) (jdbc) Screen pohdr table podtl table po.xml document Page 5 of 6 AITP National Collegiate Conference Java Problem Statement Folder structure to be submitted for judging: TeamNN +classes |+org ||+aitp |||+competitor ||||compiled classes |Team.properties +src |+org ||+aitp |||+competitor ||||Java source files April 8, 2005 7:00 – 11:00 PM (Project folder) (classes folder) (Package structure) (Java classes within package) (properties file in the classes folder) (src folder) (Package structure) (Java source files with package) Page 6 of 6