· 6 years ago · Jun 17, 2019, 07:40 PM
1package cpsc4620.antonspizza;
2
3import java.io.*;
4import java.sql.*;
5import java.util.*;
6
7/*
8This file is where most of your code changes will occur
9You will write the code to retrieve information from the database, or save information to the database
10
11The class has several hard coded static variables used for the connection, you will need to change those to your connection information
12
13This class also has static string variables for pickup, delivery and dine-in. If your database stores the strings differently (i.e "pick-up" vs "pickup") changing these static variables will ensure that the comparison is checking for the right string in other places in the program. You will also need to use these strings if you store this as boolean fields or an integer.
14
15
16*/
17
18/**
19 * A utility class to help add and retrieve information from the database
20 */
21
22public final class DBNinja {
23 //enter your user name here
24 private static String user = "AntnPzr_rdqc";
25 //enter your password here
26 private static String password = "zse4xdr5";
27 //enter your database name here
28 private static String database_name = "AntonPizzeria_zyl7";
29 //Do not change the port. 3306 is the default MySQL port
30 private static String port = "3306";
31 private static Connection conn;
32
33 //Change these variables to however you record dine-in, pick-up and delivery, and sizes and crusts
34 public final static String pickup = "pickup";
35 public final static String delivery = "delivery";
36 public final static String dine_in = "dinein";
37
38 public final static String size_s = "small";
39 public final static String size_m = "medium";
40 public final static String size_l = "large";
41 public final static String size_xl = "xlarge";
42
43 public final static String crust_thin = "thin";
44 public final static String crust_orig = "original";
45 public final static String crust_pan = "pan";
46 public final static String crust_gf = "glutenfree";
47
48
49
50 /**
51 * This function will handle the connection to the database
52 * @return true if the connection was successfully made
53 * @throws SQLException
54 * @throws IOException
55 */
56 private static boolean connect_to_db() throws SQLException, IOException
57 {
58 try
59 {
60 Class.forName("com.mysql.jdbc.Driver");
61 } catch (ClassNotFoundException e) {
62 System.out.println ("Could not load the driver");
63
64 System.out.println("Message : " + e.getMessage());
65
66
67 return false;
68 }
69
70
71 conn = DriverManager.getConnection("jdbc:mysql://mysql1.cs.clemson.edu:"+port+"/"+database_name, user, password);
72 return true;
73 }
74
75 /**
76 *
77 * @param o order that needs to be saved to the database
78 * @throws SQLException
79 * @throws IOException
80 * @requires o is not NULL. o's ID is -1, as it has not been assigned yet. The pizzas do not exist in the database
81 * yet, and the topping inventory will allow for these pizzas to be made
82 * @ensures o will be assigned an id and added to the database, along with all of it's pizzas. Inventory levels
83 * will be updated appropriately
84 */
85 public static void addOrder(Order o) throws SQLException, IOException
86 {
87 connect_to_db();
88
89 /* add code to add the order to the DB.
90 *
91 * Remember to add the pizzas and discounts as well, which will involve multiple tables.
92 *
93 * Customer should already exist. Toppings will need to be added to the pizzas.
94 *
95 * It may be beneficial to define more functions to add an individual pizza to a database, add a topping to a pizza, etc.
96 *
97 * Note: the order ID will be -1 and will need to be replaced to be a fitting primary key.
98 *
99 * You will also need to add timestamps to your pizzas/orders in your database.
100 * >done
101 *
102 * Those timestamps are not stored in this program, but you can get the current time before inserting into the database
103 *
104 *
105 * Remember, when a new order comes in the ingredient levels for the topping need to be adjusted accordingly
106 *
107 * Remember to check for "extra" of a topping here as well.
108 *
109 * You do not need to check to see if you have the topping in stock before adding to a pizza. You can just let it go negative.
110 */
111
112
113 //queries
114 String orderNumQuery = "SELECT MAX(order_number) FROM Orders;";
115
116 String orderQuery = "INSERT INTO orders VALUES (?);";
117
118 String pizzaNumQuery = "SELECT MAX(pizzaID) FROM pizza;";
119 String pizzaQuery = "INSERT INTO pizza VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
120
121 String pizzaToppingsQuery = "INSERT INTO pizzaToppings VALUES (?, ?, ?);";
122
123 String discountedPizzaQuery = "INSERT INTO discountedPizza VALUES (?, ?);";
124 String discountedOrderQuery = "INSERT INTO discountedOrder VALUES (?, ?);";
125
126 String dineinQuery = "INSERT INTO dinein VALUES (?, ?);";
127 String pickupQuery = "INSERT INTO pickup VALUES (?, ?);";
128 String deliveryQuery = "INSERT INTO delivery VALUES (?, ?);";
129
130 String dineInSeatQuery = "INSERT INTO dineInSeat VALUES (?, ?);";
131
132 //preparedStatements
133
134 Statement orderNumCheck = conn.createStatement();
135 PreparedStatement orderAdd = conn.prepareStatement(orderQuery);
136
137 Statement pizzaNumCheck = conn.createStatement();
138 PreparedStatement pizzaAdd = conn.prepareStatement(pizzaQuery);
139
140 PreparedStatement pizzaToppingsAdd = conn.prepareStatement(pizzaToppings);
141
142 PreparedStatement discountedPizzaAdd = conn.prepareStatement(discountedPizzaQuery);
143 PreparedStatement discountedOrderAdd = conn.prepareStatement(discountedOrderQuery);
144
145 PreparedStatement dineinAdd = conn.prepareStatement(dineinQuery);
146 PreparedStatement pickupAdd = conn.prepareStatement(pickupQuery);
147 PreparedStatement deliveryAdd = conn.prepareStatement(deliveryQuery);
148 PreparedStatement dineInSeatAdd = conn.prepareStatement(dineInSeatQuery);
149
150
151 //Timestamp
152 SimpleDateFormat dateForm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
153
154 ResultSet order_numLatest = orderNumCheck.executeQuery(orderNumQuery);
155 ResultSet pizza_numLatest;
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170 string query = "INSERT INTO order VALUES" +
171 Statement statement = conn.createStatement();
172
173
174 conn.close();
175
176 }
177
178 /**
179 *
180 * @param c the new customer to add to the database
181 * @throws SQLException
182 * @throws IOException
183 * @requires c is not null. C's ID is -1 and will need to be assigned
184 * @ensures c is given an ID and added to the database
185 */
186 public static void addCustomer(ICustomer c) throws SQLException, IOException
187 {
188 connect_to_db();
189 /*add code to add the customer to the DB.
190 Note: the ID will be -1 and will need to be replaced to be a fitting primary key
191 Note that the customer is an ICustomer data type, which means c could be a dine in, carryout or delivery customer
192 */
193
194 c = new ICustomer();
195
196
197
198
199
200
201 conn.close();
202 }
203
204 /**
205 *
206 * @param o the order to mark as complete in the database
207 * @throws SQLException
208 * @throws IOException
209 * @requires the order exists in the database
210 * @ensures the order will be marked as complete
211 */
212 public static void CompleteOrder(Order o) throws SQLException, IOException
213 {
214 connect_to_db();
215 /*add code to mark an order as complete in the DB. You may have a boolean field for this, or maybe a completed time timestamp. However you have it, */
216
217 String query = "";
218 conn.close();
219 }
220
221 /**
222 *
223 * @param t the topping whose inventory is being replenished
224 * @param toAdd the amount of inventory of t to add
225 * @throws SQLException
226 * @throws IOException
227 * @requires t exists in the database and toAdd > 0
228 * @ensures t's inventory level is increased by toAdd
229 */
230 public static void AddToInventory(Topping t, double toAdd) throws SQLException, IOException
231 {
232 connect_to_db();
233 /*add code to add toAdd to the inventory level of T
234 * This is not adding a new topping, it is adding a certain amount of stock for a topping
235 * This would be used to show that an order was made to replenish the restaurants supply of pepperoni, etc
236 */
237
238
239 conn.close();
240 }
241
242
243 /*
244 A function to get the list of toppings and their inventory levels. I have left this code "complete" as an example of how to use JDBC to get data from the database. This query will not work on your database if you have different field or table names, so it will need to be changed
245
246 Also note, this is just getting the topping ids and then calling getTopping() to get the actual topping. You will need to complete this on your own
247
248 You don't actually have to use and write the getTopping() function, but it can save some repeated code if the program were to expand, and it keeps the functions simpler, more elegant and easy to read. Breaking up the queries this way also keeps them simpler. I think it's a better way to do it, and many people in the industry would agree, but its a suggestion, not a requirement.
249 */
250
251 /**
252 *
253 * @return the List of all toppings in the database
254 * @throws SQLException
255 * @throws IOException
256 * @ensures the returned list will include all toppings and accurate inventory levels
257 */
258 public static ArrayList<Topping> getInventory() throws SQLException, IOException
259 {
260 //start by connecting
261 connect_to_db();
262 ArrayList<Topping> ts = new ArrayList<Topping>();
263 //create a string with out query, this one is an easy one
264 String query = "SELECT toppingID, inventory_level From toppings;";
265
266 Statement stmt = conn.createStatement();
267 try {
268 ResultSet rset = stmt.executeQuery(query);
269 //even if you only have one result, you still need to call ResultSet.next() to load the first tuple
270 while(rset.next())
271 {
272 /*Use getInt, getDouble, getString to get the actual value. You can use the column number starting with 1, or use the column name as a string
273
274 NOTE: You want to use rset.getInt() instead of Integer.parseInt(rset.getString()), not just because it's shorter, but because of the possible NULL values. A NUll would cause parseInt to fail
275
276 If there is a possibility that it could return a NULL value you need to check to see if it was NULL. In this query we won't get nulls, so I didn't. If I was going to I would do:
277
278 int ID = rset.getInt(1);
279 if(rset.wasNull())
280 {
281 //set ID to what it should be for NULL, and whatever you need to do.
282 }
283
284 NOTE: you can't check for NULL until after you have read the value using one of the getters.
285
286 */
287 int ID = rset.getInt(1);
288 //Now I'm just passing my primary key to this function to get the topping itself individually
289 ts.add(getTopping(ID));
290 }
291 }
292 catch (SQLException e) {
293 System.out.println("Error loading inventory");
294 while (e != null) {
295 System.out.println("Message : " + e.getMessage());
296 e = e.getNextException();
297 }
298
299 //don't leave your connection open!
300 conn.close();
301 return ts;
302 }
303
304
305 //end by closing the connection
306 conn.close();
307 return ts;
308 }
309
310 /**
311 *
312 * @return a list of all orders that are currently open in the kitchen
313 * @throws SQLException
314 * @throws IOException
315 * @ensures all currently open orders will be included in the returned list.
316 */
317 public static ArrayList<Order> getCurrentOrders() throws SQLException, IOException
318 {
319 connect_to_db();
320
321 ArrayList<Order> os = new ArrayList<Order>();
322 /*add code to get a list of all open orders. Only return Orders that have not been completed. If any pizzas are not completed, then the order is open.*/
323
324 conn.close();
325 return os;
326 }
327
328 /**
329 *
330 * @param size the pizza size
331 * @param crust the type of crust
332 * @return the base price for a pizza with that size and crust
333 * @throws SQLException
334 * @throws IOException
335 * @requires size = size_s || size_m || size_l || size_xl AND crust = crust_thin || crust_orig || crust_pan || crust_gf
336 * @ensures the base price for a pizza with that size and crust is returned
337 */
338 public static double getBasePrice(String size, String crust) throws SQLException, IOException
339 {
340 connect_to_db();
341 double bp = 0.0;
342 //add code to get the base price for that size and crust pizza Depending on how you store size and crust in your database, you may have to do a conversion
343
344 String query = "SELECT price FROM pizza WHERE crust_type = " + crust + " AND size = " + size + ";";
345
346 Statement statement = conn.createStatement();
347 try {
348 ResultSet rset = statement.executeQuery(query);
349
350 while (rset.next()) {
351
352 int ID = getInt(1);
353 }
354 }
355 catch (SQLException e) {
356 System.out.println("Error loading base price");
357 while (e != null) {
358 System.out.println("Message : " + e.getMessage());
359 e = e.getNextException();
360 }
361
362 conn.close();
363 return bp;
364 }
365
366 /**
367 *
368 * @return the list of all discounts in the database
369 * @throws SQLException
370 * @throws IOException
371 * @ensures all discounts are included in the returned list
372 */
373 public static ArrayList<Discount> getDiscountList() throws SQLException, IOException
374 {
375 ArrayList<Discount> discs = new ArrayList<Discount>();
376 connect_to_db();
377 //add code to get a list of all discounts
378 //SELECT * FROM discounts; for all discounts
379 //SELECT discountID FROM discounts; for all discount ID numbers only
380 String query = "SELECT discountID FROM discounts;";
381
382 Statement statement = conn.createStatement();
383 try {
384 ResultSet rset = statement.executeQuery(query);
385
386 while (rset.next()) {
387
388 int ID = getInt(1);
389 discs.add(getDiscount(ID));
390 }
391 }
392 catch (SQLException e) {
393 System.out.println("Error loading discount list");
394 while (e != null) {
395 System.out.println("Message : " + e.getMessage());
396 e = e.getNextException();
397 }
398
399 conn.close();
400 return discs;
401 }
402
403 /**
404 *
405 * @return the list of all delivery and carry out customers
406 * @throws SQLException
407 * @throws IOException
408 * @ensures the list contains all carryout and delivery customers in the database
409 */
410 public static ArrayList<ICustomer> getCustomerList() throws SQLException, IOException
411 {
412 ArrayList<ICustomer> custs = new ArrayList<ICustomer>();
413 connect_to_db();
414 //add code to get a list of all customers
415 String query = "SELECT DISTINCT c.customerID, c.Fname, c.Mname, c.Lname, c.phone_number " +
416 "FROM customer AS c JOIN orders ON c.customerID = orders.customerID " +
417 "WHERE order_type = 'delivery' OR order_type = 'pickup';";
418
419 Statement statement = conn.createStatement();
420 try {
421 ResultSet rset = statement.executeQuery(query);
422
423 while (rset.next()) {
424
425 int ID = getInt(1);
426 custs.add(getICustomer(ID));
427 }
428 }
429 catch (SQLException e) {
430 System.out.println("Error loading customer list");
431 while (e != null) {
432 System.out.println("Message : " + e.getMessage());
433 e = e.getNextException();
434 }
435
436 conn.close();
437 return custs;
438 }
439
440
441
442 /*
443 Note: The following incomplete functions are not strictly required, but could make your DBNinja class much simpler. For instance, instead of writing one query to get all of the information about an order, you can find the primary key of the order, and use that to find the primary keys of the pizzas on that order, then use the pizza primary keys individually to build your pizzas. We are no longer trying to get everything in one query, so feel free to break them up as much as possible
444
445 You could also add functions that take in a Pizza object and add that to the database, or take in a pizza id and a topping id and add that topping to the pizza in the database, etc. I would recommend this to keep your addOrder function much simpler
446
447 These simpler functions should still not be called from our menu class. That is why they are private
448
449 We don't need to open and close the connection in these, since they are only called by a function that has opened the connection and will close it after
450 */
451
452
453 private static Topping getTopping(int ID) throws SQLException, IOException
454 {
455
456 //add code to get a topping
457 //the java compiler on unix does not like that t could be null, so I created a fake topping that will be replaced
458 Topping t = new Topping("fake", 0.25, 100.0, -1);
459 String query = "SELECT tname, company_price, inventory_level FROM toppings WHERE toppingID = " + ID + ";";
460
461 Statement stmt = conn.createStatement();
462 try {
463 ResultSet rset = stmt.executeQuery(query);
464 //even if you only have one result, you still need to call ResultSet.next() to load the first tuple
465 while(rset.next())
466 {
467 String tname = rset.getString(1);
468 double price = rset.getDouble(2);
469 double inv = rset.getDouble(3);
470
471 t = new Topping(tname, price, inv, ID);
472 }
473
474 }
475 catch (SQLException e) {
476 System.out.println("Error loading Topping");
477 while (e != null) {
478 System.out.println("Message : " + e.getMessage());
479 e = e.getNextException();
480 }
481
482 //don't leave your connection open!
483 conn.close();
484 return t;
485 }
486
487 return t;
488
489 }
490
491 private static Discount getDiscount() throws SQLException, IOException
492 {
493
494 //add code to get a discount
495
496 Discount D = new Discount("temp", 0.0, 0.0, -1);
497 String query = "SELECT discountName, percentOff, dollaroff FROM discounts, percentageOff, dollarOff WHERE discountID = " + ID + ";";
498
499 Statement statement = conn.createStatement();
500 try {
501 ResultSet rset = statement.executeQuery(query);
502
503 while (rset.next()) {
504
505 String dname = rset.getString(1);
506 int pOff = rset.getInt(2);
507 double cashOff = rset.getDouble(3);
508
509 D = new Discount(dname, pOff, cashOff, ID);
510 }
511 }
512 catch (SQLException e) {
513 System.out.println("Error loading Discount");
514 while (e != null) {
515 System.out.println("Message : " + e.getMessage());
516 e = e.getNextException();
517 }
518
519 conn.close();
520 return D;
521 }
522
523 return D;
524
525 }
526
527 private static Pizza getPizza() throws SQLException, IOException
528 {
529
530 //add code to get Pizza Remember, a Pizza has toppings and discounts on it
531 //ID, Size, Crust, Base Price
532 Pizza P = new Pizza(-1, size_s, crust_orig, 0.0);
533 String query = "SELECT size, crust_type, price FROM pizza WHERE pizzaID = " + ID + ";";
534
535 Statement statement = conn.createStatement();
536 try {
537 ResultSet rset = statement.executeQuery(query);
538
539 while (rset.next()) {
540
541 String size = rset.getString(1);
542 String crust = rset.getString(2);
543 double price = rset.getDouble(3);
544
545 P = new Pizza(ID, size, crust, price);
546 }
547 }
548 catch (SQLException e) {
549 System.out.println("Error loading Pizza");
550 while (e != null) {
551 System.out.println("Message : " + e.getMessage());
552 e = e.getNextException();
553 }
554
555 conn.close();
556 return P;
557 }
558
559 return P;
560
561 }
562
563 private static ICustomer getCustomer() throws SQLException, IOException
564 {
565
566 //add code to get customer
567
568 ICustomer C = new ICustomer();
569
570
571
572 return C;
573
574
575 }
576
577 private static Order getOrder() throws SQLException, IOException
578 {
579
580 //add code to get an order. Remember, an order has pizzas, a customer, and discounts on it
581 //ID, Customer, Type
582 Order O = new Order(-1, -1, pickup);
583 String query = "SELECT customerID, "
584
585 return O;
586
587 }
588
589}