It is a very difficult question, any comments would be very appreciate for it!

using JDBC to handle a user order

An Order has a list of Products in it(some what similar to a shoppingcart). TheOrderhas:

A Customer Account number

A list of product items in the order

A product item is the Product number and how many of the product were ordered

you will have three database tables:

Ordertable: Each row of the Order table represents one customer order. To simplify things we will not store the individual products making up the order. This can be done,however,by adding an extra join table (optional).

Product table: Each row of the Product table represents one Product that can be sold

Customer Account table: Each row represents one customer and their balance (how much they currently owe)

1

For this lab, write a function that takes an Order object and:

Computes the order total just like Amazon sums up your shopping cart add in the costo fall the product item in the Order. Togetthe price of the product you will need to look it up in the database (product table) using the product number.

Insert a new row into the Order table, representing the Order.

For each product in the Order product list, adjust the amount in stock (this requires a change in the Product table)

Update the Customer Account table add the cost of the order to the Customers account balance

The table updates (Items 2, 3, and 4 above) should all be done under one transaction. If any part fails, everything should be rolled back.

Lets say we have the following order :

Item #1: The Order total would be (using the example database tables shown above): 3*259.50 + 2*101.65 =981.80

Item #2: We would need to insert a new row into the Order table that would have the amount 981.80

Item#3:Thenewnuminstock(Producttable)fortheZX5000wouldbe2 (5-3=2) Thenewnuminstock(Producttable)forthePW2875wouldbe1 (3-2=1)

Item #4: The new balance for Julia Shindel (Customer Account table) would be 1960.47 (current balance of 978.67 + the new order amount of 981.80)

2

Make sure you have the MySql ConnectorJ .jar file in your Eclipse project (that is an implementation of the JDBC API).

Start with a simple table like the product table. Create the schema, then create the ProducttableinMySQLWorkbench. AddsomerowstothetableusingMySQL Workbench. Dont handle transactions until the very end.

Now that the Product table is created, see if you can look up a Product given only the Productnumber. Testthiscapability.

Create the Order table and the Customer Account tables using MySQL Workbench. Once again, you can fill in some initial data (rows).

Write the Order class and then create an Order object (youll need to create the product listthattheOrderhas). Youcanjusthardcodesomedataasyouveseentheinstructor doinclass. However,asshowninclass,itshouldbecreatedinaseparatemethod dont mix this logic with production code. Write a method that can print an Order so you can verify that what you intended to create is what you have.

Write a method to find the order total.

Add a call to the method you wrote in the previous step and print it out so you can verify that the total is correct.

Write a method that can store the Order object in the Order table

Write a method that can update the Customer Account table (the customers balance

needs to be adjusted)

Add support for transactions

Notes on what to hand in:

Hand in an exported Eclipse project. Include the SQL commands in a file(as is done in the sample projects) called setup Database.sql. You dont need to write the SQL yourselfget it from MySQLWorkbench (if you are not sure how to get this,then ask). This file should include all commands needed to create the schema, tables, insert some initial rows into the tables, and create a user/password that can access the schema.

Work on more than just getting the functionality correct. Make your code readablebyusing good variable and function names . If a particular task needs to be performed, put it in a methodandthenameofthemethodshouldindicatewhattaskthemethodisperforming. If several tasks are required, each task should be accomplished by a function call instead of putting all the code in one method.