· 5 years ago · Mar 06, 2020, 03:42 AM
1import java.sql.*;
2import java.util.Date;
3import java.util.Arrays;
4import java.util.List;
5import java.util.ArrayList;
6import java.util.Collections;
7
8public class Assignment2 {
9
10 // A connection to the database
11 Connection connection;
12
13 // Can use if you wish: seat letters
14 List<String> seatLetters = Arrays.asList("A", "B", "C", "D", "E", "F");
15
16 Assignment2() throws SQLException {
17 try {
18 Class.forName("org.postgresql.Driver");
19 } catch (ClassNotFoundException e) {
20 e.printStackTrace();
21 }
22 }
23
24 /**
25 * Connects and sets the search path.
26 *
27 * Establishes a connection to be used for this session, assigning it to
28 * the instance variable 'connection'. In addition, sets the search
29 * path to 'air_travel, public'.
30 *
31 * @param url the url for the database
32 * @param username the username to connect to the database
33 * @param password the password to connect to the database
34 * @return true if connecting is successful, false otherwise
35 */
36 public boolean connectDB(String URL, String username, String password) {
37 // Implement this method!
38 try{
39 connection = DriverManager.getConnection(URL, username, password);
40 String queryString = "SET search_path TO air_travel, public";
41 PreparedStatement ps = connection.prepareStatement(queryString);
42 ps.executeUpdate();
43 return true;
44 }catch(SQLException e){
45 e.printStackTrace();
46 return false;
47 }
48 }
49
50 /**
51 * Closes the database connection.
52 *
53 * @return true if the closing was successful, false otherwise
54 */
55 public boolean disconnectDB() {
56 // Implement this method!
57 try{
58 connection.close();
59 return true;
60 }catch(SQLException e){
61 e.printStackTrace();
62 return false;
63 }
64 }
65
66 /* ======================= Airline-related methods ======================= */
67
68 /**
69 * Attempts to book a flight for a passenger in a particular seat class.
70 * Does so by inserting a row into the Booking table.
71 *
72 * Read handout for information on how seats are booked.
73 * Returns false if seat can't be booked, or if passenger or flight cannot be found.
74 *
75 *
76 * @param passID id of the passenger
77 * @param flightID id of the flight
78 * @param seatClass the class of the seat (economy, business, or first)
79 * @return true if the booking was successful, false otherwise.
80 */
81 public boolean bookSeat(int passID, int flightID, String seatClass) {
82 try {
83 // Check if the passID exists.
84 int numResults = 0;
85
86 String queryString = "SELECT * FROM passenger WHERE id="+passID;
87 PreparedStatement qStatement = connection.prepareStatement(queryString);
88 ResultSet rs = qStatement.executeQuery();
89 while (rs.next()) {
90 numResults++;
91 }
92 if (numResults == 0) {
93 throw new SQLException("Could not find passenger with ID " + passID);
94 }
95
96 // Check if the flightID exists.
97 numResults = 0;
98
99 queryString = "SELECT * " + "FROM flight " + "WHERE id = " + flightID;
100 qStatement = connection.prepareStatement(queryString);
101 rs = qStatement.executeQuery();
102
103 String tailNumber = "";
104
105 while (rs.next()) {
106 numResults++;
107 tailNumber += rs.getString("plane");
108 }
109 if (numResults == 0) {
110 throw new SQLException("Could not find flight with ID " + flightID);
111 } else if (numResults > 1) {
112 throw new SQLException("Multiple flights have that ID!");
113 }
114
115 // Now we know that the passID and flightID are valid.
116 // Need the capacities of plane.
117 numResults = 0;
118 int economyCap = 0;
119 int businessCap = 0;
120 int firstCap = 0;
121
122 queryString = "SELECT * FROM plane WHERE tail_number='"+tailNumber+"'";
123 qStatement = connection.prepareStatement(queryString);
124 rs = qStatement.executeQuery();
125
126 while (rs.next()) {
127 numResults++;
128 economyCap = rs.getInt("capacity_economy");
129 businessCap = rs.getInt("capacity_business");
130 firstCap = rs.getInt("capacity_first");
131 }
132 if (numResults == 0) {
133 // This shouldnt actually occur
134 throw new SQLException("No plane exists with tail_number " + tailNumber);
135 }
136
137 // Get the prices for the flights.
138 numResults = 0;
139 int economyPrice = 0;
140 int businessPrice = 0;
141 int firstPrice = 0;
142
143 queryString = "SELECT * " + "FROM price " + "WHERE flight_id = " + flightID;
144 qStatement = connection.prepareStatement(queryString);
145 rs = qStatement.executeQuery();
146
147 while (rs.next()) {
148 numResults++;
149 economyPrice = rs.getInt("economy");
150 businessPrice = rs.getInt("business");
151 firstPrice = rs.getInt("first");
152 }
153 if (numResults == 0) {
154 // This shouldnt actually occur
155 throw new SQLException("No prices exist for flight " + flightID);
156 }
157
158 // Get the bookings for the flight with ID flight_id
159 int numBookings = 0;
160 int nullEconCount = 0;
161
162 queryString = "SELECT * FROM booking WHERE flight_id="+flightID;
163 qStatement = connection.prepareStatement(queryString);
164 rs = qStatement.executeQuery();
165
166 List<String> seatClassLst = new ArrayList<String>();
167 List<Integer> rowLst = new ArrayList<Integer>();
168 List<String> letterLst = new ArrayList<String>();
169
170 while (rs.next()) {
171 int fid = rs.getInt("flight_id");
172 //TODO: What if its an economy seat with a NULL row and letter??
173 if(rs.getInt("row") == 0 && rs.getString("letter") == null){
174 nullEconCount++;
175 }else{
176 String seatClassTmp = rs.getString("seat_class");
177 seatClassLst.add(seatClassTmp);
178 rowLst.add(rs.getInt("row"));
179 letterLst.add(rs.getString("letter"));
180 }
181 }
182
183 // Create a list of List(Row, Letter) pairs for each seat type.
184 List<List> validFirstSeats = getValidSeats(firstCap, 1);
185 int businessRowStart = 1;
186 int validFirstLen = validFirstSeats.size();
187 if (validFirstLen > 0)
188 businessRowStart = ((int) validFirstSeats.get(validFirstLen - 1).get(0)) + 1;
189 List<List> validBusinessSeats = getValidSeats(businessCap, businessRowStart);
190 int economyRowStart = 1;
191 int validBusinessLen = validBusinessSeats.size();
192 if (validBusinessLen > 0)
193 economyRowStart = ((int) validBusinessSeats.get(validBusinessLen - 1).get(0)) + 1;
194 List<List> validEconomySeats = getValidSeats(economyCap, economyRowStart);
195
196 // Prune from all seats the seats that have already been booked.
197 for (int i = 0; i < seatClassLst.size(); i++) {
198 String curSeatClass = seatClassLst.get(i);
199 List<Object> tmp = new ArrayList<>();
200 tmp.add((int) rowLst.get(i));
201 tmp.add(letterLst.get(i));
202 if (curSeatClass.equals("economy")) {
203 validEconomySeats.remove(tmp);
204 } else if (curSeatClass.equals("business")) {
205 validBusinessSeats.remove(tmp);
206 } else {
207 validFirstSeats.remove(tmp);
208 }
209 }
210
211 // Get the booking ID for the new passenger booking
212 int bID;
213
214 queryString = "SELECT max(id)+1 AS id FROM booking";
215 qStatement = connection.prepareStatement(queryString);
216 rs = qStatement.executeQuery();
217 rs.next();
218 bID = rs.getInt("id");
219
220 Statement stmt = connection.createStatement();
221 if (seatClass.equals("business")){
222 if (validBusinessSeats.size() > 0){
223 int row = (int)validBusinessSeats.get(0).get(0);
224 String letter = (String)validBusinessSeats.get(0).get(1);
225 queryString = "INSERT INTO booking VALUES(" +
226 bID +
227 ", " + passID +
228 ", " + flightID +
229 ", '" + getCurrentTimeStamp() + "'" +
230 ", " + businessPrice +
231 ", '" + seatClass + "'" +
232 ", " + row +
233 ", '" + letter + "'" +
234 ")";
235 stmt.executeUpdate(queryString);
236
237 System.out.println("Booking seat " +
238 row + "" +
239 letter + " for business class.");
240 return true;
241 } else {
242 System.out.println("No business seats left.");
243 return false;
244 }
245 }
246 if (seatClass.equals("first")){
247 if (validFirstSeats.size() > 0){
248 int row = (int)validFirstSeats.get(0).get(0);
249 String letter = (String)validFirstSeats.get(0).get(1);
250 queryString = "INSERT INTO booking VALUES(" +
251 bID +
252 ", " + passID +
253 ", " + flightID +
254 ", '" + getCurrentTimeStamp() + "'" +
255 ", " + firstPrice +
256 ", '" + seatClass + "'" +
257 ", " + row +
258 ", '" + letter + "'" +
259 ")";
260 stmt.executeUpdate(queryString);
261
262
263 System.out.println("Booking seat " +
264 row + "" +
265 letter + " for first class.");
266 return true;
267 } else {
268 System.out.println("No first class seats left.");
269 return false;
270 }
271 }
272 if (seatClass.equals("economy")){
273 if (validEconomySeats.size() > 0){
274 int row = (int)validEconomySeats.get(0).get(0);
275 String letter = (String)validEconomySeats.get(0).get(1);
276 queryString = "INSERT INTO booking VALUES(" +
277 bID +
278 ", " + passID +
279 ", " + flightID +
280 ", '" + getCurrentTimeStamp() + "'" +
281 ", " + economyPrice +
282 ", '" + seatClass + "'" +
283 ", " + row +
284 ", '" + letter + "'" +
285 ")";
286 stmt.executeUpdate(queryString);
287
288 System.out.println("Booking seat " +
289 row + "" +
290 letter + " for economy class.");
291
292 return true;
293 }
294 // TODO: NULL seats and letters for ppl booking economy when no seat is available.
295 if(nullEconCount < 10){
296 queryString = "INSERT INTO booking VALUES(" +
297 bID +
298 ", " + passID +
299 ", " + flightID +
300 ", '" + getCurrentTimeStamp() + "'" +
301 ", " + economyPrice +
302 ", '" + seatClass + "'" +
303 ", NULL, NULL)";
304 stmt.executeUpdate(queryString);
305
306 System.out.println("Passenger assigned to NULL seat: position "+(nullEconCount+1));
307
308 return true;
309 }
310 }
311 } catch (SQLException e) {
312 System.out.println("SQL ERROR: " + e.getMessage());
313 }
314 return false;
315 }
316
317 /**
318 * Attempts to upgrade overbooked economy passengers to business class
319 * or first class (in that order until each seat class is filled).
320 * Does so by altering the database records for the bookings such that the
321 * seat and seat_class are updated if an upgrade can be processed.
322 *
323 * Upgrades should happen in order of earliest booking timestamp first.
324 *
325 * If economy passengers left over without a seat (i.e. more than 10 overbooked passengers or not enough higher class seats),
326 * remove their bookings from the database.
327 *
328 * @param flightID The flight to upgrade passengers in.
329 * @return the number of passengers upgraded, or -1 if an error occured.
330 */
331 public int upgrade(int flightID) {
332 // Implement this method!
333 try{
334 String queryString;
335 PreparedStatement ps;
336 ResultSet rs;
337
338 // Check if the flightID exists.
339 int numResults = 0;
340
341 queryString = "SELECT * " + "FROM flight " + "WHERE id = " + flightID;
342 ps = connection.prepareStatement(queryString);
343 rs = ps.executeQuery();
344
345 String tailNumber = "";
346
347 while (rs.next()) {
348 numResults++;
349 tailNumber += rs.getString("plane");
350 }
351 if (numResults == 0) {
352 throw new SQLException("Could not find flight with ID " + flightID);
353 } else if (numResults > 1) {
354 throw new SQLException("Multiple flights have that ID!");
355 }
356
357 // Need the capacities of plane.
358 numResults = 0;
359 int economyCap = 0;
360 int businessCap = 0;
361 int firstCap = 0;
362
363 queryString = "SELECT * FROM plane WHERE tail_number='"+tailNumber+"'";
364 ps = connection.prepareStatement(queryString);
365 rs = ps.executeQuery();
366
367 while (rs.next()) {
368 numResults++;
369 economyCap = rs.getInt("capacity_economy");
370 businessCap = rs.getInt("capacity_business");
371 firstCap = rs.getInt("capacity_first");
372 }
373 if (numResults == 0) {
374 // This shouldnt actually occur
375 throw new SQLException("No plane exists with tail_number " + tailNumber);
376 }
377 // get the business and first class passengers, while deleting the NULL seat passengers
378 queryString = "SELECT * FROM booking WHERE flight_id="+flightID;
379 ps = connection.prepareStatement(queryString);
380 rs = ps.executeQuery();
381
382 List<BookingInfo> nullSeats = new ArrayList<BookingInfo>();
383
384 List<String> seatClassLst = new ArrayList<String>();
385 List<Integer> rowLst = new ArrayList<Integer>();
386 List<String> letterLst = new ArrayList<String>();
387
388 while (rs.next()) {
389 String seatClass = rs.getString("seat_class");
390 int row = rs.getInt("row");
391 String letter = rs.getString("letter");
392
393 if(row == 0 && letter == null){
394 BookingInfo bInfo = new BookingInfo();
395
396 bInfo.pID = rs.getInt("pass_id");
397 bInfo.fID = rs.getInt("flight_id");
398 bInfo.price = rs.getInt("price");
399 bInfo.timestamp = rs.getTimestamp("datetime");
400
401 nullSeats.add(bInfo);
402
403 // delete the NULL passenger from the booking list
404 String queryTemp = "DELETE FROM booking WHERE id="+rs.getInt("id");
405 PreparedStatement psTemp = connection.prepareStatement(queryTemp);
406 psTemp.executeUpdate();
407 }else if(seatClass.equals("business") || seatClass.equals("first")){
408 seatClassLst.add(seatClass);
409 rowLst.add(row);
410 letterLst.add(letter);
411 }
412 }
413
414 if(nullSeats.size() == 0){
415 return 0;
416 }
417
418 // Create a list of List(Row, Letter) pairs for each seat type.
419 List<List> validFirstSeats = getValidSeats(firstCap, 1);
420 int businessRowStart = 1;
421 int validFirstLen = validFirstSeats.size();
422 if (validFirstLen > 0)
423 businessRowStart = ((int) validFirstSeats.get(validFirstLen - 1).get(0)) + 1;
424 List<List> validBusinessSeats = getValidSeats(businessCap, businessRowStart);
425
426 // Prune from all seats the seats that have already been booked.
427 for (int i = 0; i < seatClassLst.size(); i++) {
428 String curSeatClass = seatClassLst.get(i);
429 List<Object> tmp = new ArrayList<>();
430 tmp.add((int) rowLst.get(i));
431 tmp.add(letterLst.get(i));
432 if (curSeatClass.equals("business")) {
433 validBusinessSeats.remove(tmp);
434 } else {
435 validFirstSeats.remove(tmp);
436 }
437 }
438
439 int num_upgraded = 0;
440
441 // sort the NULL passengers according to their timestamps
442 Collections.sort(nullSeats);
443
444 for(int i = 0;i < nullSeats.size();i++){
445 // find the first available seat
446 BookingInfo bInfo = nullSeats.get(i);
447
448 if(validBusinessSeats.size() > 0){
449 bInfo.row = (int)validBusinessSeats.get(0).get(0);
450 bInfo.letter = (String)validBusinessSeats.get(0).get(1);
451 bInfo.seatClass = "business";
452 validBusinessSeats.remove(0);
453 }else if(validFirstSeats.size() > 0){
454 bInfo.row = (int)validFirstSeats.get(0).get(0);
455 bInfo.letter = (String)validFirstSeats.get(0).get(1);
456 bInfo.seatClass = "first";
457 validFirstSeats.remove(0);
458 }else{
459 break;
460 }
461
462 // get the new booking id for the passenger
463 queryString = "SELECT max(id) AS id from booking";
464 ps = connection.prepareStatement(queryString);
465 rs = ps.executeQuery();
466 while(rs.next()){
467 bInfo.bID = rs.getInt("id")+1;
468 }
469
470 bInfo.timestamp = getCurrentTimeStamp();
471
472 queryString = "INSERT INTO booking VALUES "+bInfo.toString();
473 ps = connection.prepareStatement(queryString);
474 ps.executeUpdate();
475
476 System.out.println("Upgraded passenger "+
477 bInfo.pID+
478 " to seat "+
479 bInfo.row+
480 bInfo.letter+
481 " ("+
482 bInfo.seatClass+
483 " class)");
484
485 num_upgraded++;
486 }
487
488 return num_upgraded;
489 }catch(SQLException e){
490 System.out.println("SQL ERROR: " + e.getMessage());
491 }
492
493 return -1;
494 }
495
496 /* ----------------------- Helper functions below ------------------------- */
497
498 // A helpful function for adding a timestamp to new bookings.
499 // Example of setting a timestamp in a PreparedStatement:
500 // ps.setTimestamp(1, getCurrentTimeStamp());
501
502 /**
503 * Returns a SQL Timestamp object of the current time.
504 *
505 * @return Timestamp of current time.
506 */
507 private java.sql.Timestamp getCurrentTimeStamp() {
508 java.util.Date now = new java.util.Date();
509 return new java.sql.Timestamp(now.getTime());
510 }
511
512 private static List<List> getValidSeats(int numSeats, int startingRow) {
513 List<List> res = new ArrayList<>();
514 char letter = 'A';
515
516 for (int i = 0; i < numSeats; i++) {
517 List<Object> p = new ArrayList<>();
518 String s = "" + (char) (letter + (i % 6));
519 int row = (i / 6) + startingRow;
520 p.add(row);
521 p.add(s);
522 res.add(p);
523 }
524
525 return res;
526 }
527
528 private class BookingInfo implements Comparable<BookingInfo>{
529 public int bID;
530 public int pID;
531 public int fID;
532 public Timestamp timestamp;
533 public int price;
534 public String seatClass;
535 public int row;
536 public String letter;
537
538 public String toString(){
539 return "("+bID+", "+pID+", "+fID+", '"+timestamp+"', "+
540 price+", '"+seatClass+"', "+row+", '"+letter+"')";
541 }
542
543 @Override
544 public int compareTo(BookingInfo b){
545 return timestamp.compareTo(b.timestamp);
546 }
547 }
548
549 /* ----------------------- Main method below ------------------------- */
550
551 public static void main(String[] args) {
552 // You can put testing code in here. It will not affect our autotester.
553 System.out.println("Running the code!");
554 try{
555 Assignment2 a2 = new Assignment2();
556 a2.connectDB("jdbc:postgresql://localhost:5432/csc343h-choisan2", "choisan2", "");
557 System.out.println("connected");
558 a2.bookSeat(1, 10, "economy");
559 a2.bookSeat(2, 10, "economy");
560 a2.bookSeat(3, 10, "economy");
561 a2.bookSeat(4, 10, "economy");
562 System.out.println("Upgraded: "+a2.upgrade(10));
563 a2.disconnectDB();
564 System.out.println("disconnected");
565 }catch(SQLException e){
566 e.printStackTrace();
567 return;
568 }
569 }
570
571}